Jump to content

shashib

Members
  • Posts

    45
  • Joined

  • Last visited

Profile Information

  • Location
    India

shashib's Achievements

Newbie

Newbie (1/7)

0

Reputation

  1. any one here ....only my UPDATE QUERY IS NOT WORKING ... SELECT QUERY IS PERFECT ...IT TAKES ME IN IF CONDITION IF == else it take me in else loop update producttab set prdname='prdnew' WHERE prdid='00A' update producttab set prdname='prd2new' WHERE prdid='00B' this is what echo/print my update query shows....but in database value is not update ....
  2. I need to check if value inserting in database while **import excel file** , if it has already value in database then it should get update. Below is producttab table value in database prdid | prdname 00A | prd1 00B | prd2 00C | prd3 00D | prd4 Below is EXCEL FILE data prdid | prdname 00A | prdnew 00B | prd2new 00E | prd8 00H | prd9 So if i upload above excel file then , 00A , 00B should get UPDATE IN producttab table as they are already present there... but 00E,00H should get insert below is what i have tried, value is getting insert properly only UPDATE IS NOT HAPPENING if(isset($_POST["Upload"])) { $fileinfo = pathinfo($_FILES["uploadFile"]["name"]); $filetype = $_FILES["uploadFile"]["type"]; $remark = NULL; //Validate File Type if(strtolower(trim($fileinfo["extension"])) != "csv") { $_SESSION['msg_r'] = "Please select CSV file"; header("location:importfile.php"); exit; } else { $file_path = $_FILES["uploadFile"]["tmp_name"]; } $row = 0; $tempFileName = time().".csv"; if ( is_uploaded_file( $file_path ) ) { $fileCopied = copy( $file_path , $tempFileName); if (($handle = fopen($tempFileName, "r")) !== FALSE) { fgetcsv($handle); while (($data = fgetcsv($handle, 6000, ",")) !== FALSE) { $num = count($data); for ($c=0; $c < $num; $c++) { $col[$c] = $data[$c]; } $col1 = $col[0]; // prdid $col2 = $col[1]; // prdname $sql = "SELECT prdid FROM producttab WHERE prdid = '".$col1."' "; $query = db_query($sql); $pfetech = db_fetch($query); if($col1 == $pfetech['prdid']){ $sqlup = "UPDATE producttab SET prdid = ".$pfetech['prdid'].", prdname = ".$col2." "; $sqlup .= " WHERE prdid = ".$pfetech['prdid']." "; $resultsqlupdate = mysql_query($sqlup); }else{ $query = "INSERT INTO producttab(prdid,prdname) VALUES('".$col1."','".$col2.")"; $s = mysql_query($query); } } fclose($handle); } echo "<center>File data imported to database!!</center>"; } } }
  3. Its PHP MYSQL : I have a table prodt , in which i first INSERT a value and with its LAST INSERT ID i do update for MAX + 1 as below , BUT I AM GETTING ERROR You can't specify target table 'prodt' for update in FROM clause $a = db_insert_id(); $sqllast = "UPDATE prodt SET pdname= ((SELECT pdname FROM ( SELECT MAX( pdname ) AS pdname FROM prodt WHERE oid = ".db_escape($oid)." ) AS pdname ) + 1 ), pcyn = ".db_escape(0)." WHERE id = ".db_escape($a)." AND oid= ".db_escape($oid)." "; $resultsqllast = db_query($sqllast); if((!$resultsqllast) || (db_mysql_affected_rows($db) <= 0)) { throw new Exception('Wrong SQL UPDATE' . $sqllast . ' Error: '.db_error_msg($db) . db_error_no()); } After research i tried below : $sqllast = "UPDATE prodt SET pdname= ((SELECT pdname FROM ( SELECT MAX( pdname ) AS pdname FROM ( SELECT * FROM prodt WHERE oid = ".db_escape($oid)." )AS pdname ) AS pdname ) + 1 ), pcyn = ".db_escape(0)." WHERE id = ".db_escape($a)." AND oid= ".db_escape($oid)." "; $resultsqllast = db_query($sqllast); if((!$resultsqllast) || (db_mysql_affected_rows($db) <= 0)) { throw new Exception('Wrong SQL UPDATE' . $sqllast . ' Error: '.db_error_msg($db) . db_error_no()); } But still its not working, getting same error message ... Thanks
  4. hey dude, And what if i need as below, as i tried to build in your above query ...but i failed ..... id | mid | wgh | remark| remkok | name | 1 3 1.5 r3ok 1 name1 2 2 1.5 0 name3 3 2 0.6 nice 0 name3 4 1 1.2 okh 0 name4 5 4 1.5 bye 0 name2 6 4 2.4 okby 0 name2 7 3 3.0 oknice 1 name1 mid wgh 3 1.5 3.0 remarks : NAME1 - r3ok, oknice 4 1.5 2.4 remarks : NAME2 - bye, okby 2 1.5 0.6 remarks : NAME3 - , nice 1 1.2 remarks : NAME4 - okh
  5. hey @dsonesuk, thanks you very much , have tried your above code ....and its working ... will implement it fully with my all conditions .... thanks once again
  6. hey @dsonesuk , thanks for reply ... please help me out what you have in mind ...might it can help me ...basically that remarks row will be seen only when my Column remkok = 1 ... but to explain each point was making my question confusing ..hence i wrote it simple.... please help me with your answer Thanks
  7. i have modified my question ..please see
  8. any one here ..please help me ...any other solution with other table tr format will help me tooo
  9. Below is products table : id | mid | wgh | remark| remkok | 1 3 1.5 r3ok 1 2 2 1.5 0 3 2 0.6 nice 0 4 1 1.2 okh 0 5 4 1.5 bye 0 6 4 2.4 okby 0 7 3 3.0 oknice 1 I want to display remark below tr of group by mid ....like below mid wgh 3 1.5 3.0 remarks : r3ok, oknice 4 1.5 2.4 remarks : bye, okby 2 1.5 0.6 remarks : , nice 1 1.2 remarks : okh **What i have tried as below :** $pid= null; while($row = mysql_fetch_array($result)) { $rowpkts = $row['mid']; echo "<tr class=\"undercl\">"; if($rowpkts != $pid){ echo'<td align="center" valign="top">'.$row["mid"].'</td>'; }else{ echo'<td align="center" valign="top"></td>'; } echo'<td align="center" valign="top">'.$row["wgh"].'</td>'; echo "</tr>"; // what i tried to build for remarks as below $remsql = "SELECT mid as onu , GROUP_CONCAT(`remark` ORDER BY `id` ASC SEPARATOR ', ') AS plrmks FROM products WHERE remkok= 1 GROUP BY `mid`"; $fetchremk = mysql_query($remsql); $rowresults = mysql_fetch_array($fetchremk); if($rowresults['onu'] == $pid ){ echo"<tr style='border-style:underline;'>"; echo'<td align="center" align="top">'.$rowresults["plrmks"].'</td>'; echo"</tr>"; } } $pid = $rowpkts; } But remarks is not coming proper ...i means its not display below mid=3 or mid=1.....
  10. hey thanks for concepts ... NO range is also in system ...please SEE UPDATE QUESTION : Here is main excel file : but the thing is that value 26,25.30 is in excel file .... let say i make tree node for AB and XE and others as you said ....but what about value ...how i will call that from excel file ...how i can differentiate that 26 belong to which tree node from excel file. As said early that excel file has many boxes ( as per your logic : many others node and its value ) combinations , above is just sample .... i think node logic is nice ... but after that which value belongs to which node ...how i can get that from excel file I have to read each row ,column of excel file as per node ????... how i can do that ... As main node AB is can be done ...but XE node ??...and after XE ...i have another BH....main node ...then how i can differentiate that in excel ... if its was in database it would be easy ...but it impossible for me to insert each combination in database ... Thanks once again ....
  11. Above Code box is data which is in excel in same way as i have shown ... in PHP page i have below drop-down value 1 } 0.45-0.90 ( range ) : thats 0.45,0.55,0.69,..0.90 2 } AB , XE 3 } A,B,C,D 4 } b1,b2,b3,X1,X2,X3 When any one select 0.45 - AB - A - b1 :: then its value should come from excel or any other matter(mysql etc) : 23 i cant make each combination and store in database, as there are thousands of combination , so is there any easy way that excel format get store in mysql or any coding etc... i haven't tried anything ..as i am not getting how to start an what to start ...as this value is in excel and its like martix ...
  12. Any with solution .....
  13. any one here ?????
  14. ------------------------------|------------------ AB | XE ------------------------------|------------------ | b1 | b2 | b3 | X1 | X2 | X3 ------------------------------|------------------ | A | 23 | 34 | 45 | 20 | 19 | 80 0.45 - 0.90| B | 20 | 31 | 85 | 23 | 23 | 67 | C | 23 | 32 | 23 | 10 | 12 | 23 | D | 10 | 12 | 23 | 56 | 45 | 23 -----------------------------|------------------ AB | XE ------------------------------|------------------ | b1 | b2 | b3 | X1 | X2 | X3 ------------------------------|------------------ | A | 33 | 34 | 65 | 40 | 19 | 90 1.00 - 2.90| B | 80 | 91 | 85 | 23 | 23 | 47 | C | 63 | 82 | 63 | 90 | 82 | 83 | D | 20 | 82 | 23 | 56 | 65 | 26 Above date is in excel Excel File Link note : 0.45-0.90 is range from 0.45 to 0.90 IN PHP page I have value in drop down for below : 1 } 0.45-0.90 ( range ) : thats 0.45,0.55,0.69,..0.90 2 } AB , XE 3 } A,B,C,D 4 } b1,b2,b3,X1,X2,X3 that numbers : 23,34,45,..etc is output, when any user select above drop-down data : Like : example as below : when any one select FIRST RANGE ( 0.45 - 0.90 ) : 0.45 - AB - A - b1 : then he will get output in textbox as ***23*** 0.45 - AB - A - b2 : then he will get output in textbox as ***34*** 0.55 - AB - A - b2 : then he will get output in textbox as ***34*** 0.69 - AB - B - b2 : then he will get output in textbox as ***31*** 0.69 - XE - B - X2 : then he will get output in textbox as ***23*** 0.90 - XE - C - X3 : then he will get output in textbox as ***23*** 0.90 - XE - D - X1 : then he will get output in textbox as ***56*** Like : example as below : when any one select SECOND RANGE ( 1.00 - 2.90 ) : 1.01 - AB - A - b1 : then he will get output in textbox as ***33*** 1.11 - AB - A - b2 : then he will get output in textbox as ***34***
  15. id | mid | pid | owgh | nwgh |1 3 12 1.5 0.62 3 12 1.5 0.33 3 14 0.6 0.44 3 15 1.2 1.15 4 16 1.5 1.06 4 17 2.4 1.27 3 19 3.0 1.4 Select mid , COUNT(distinct pid) as cpid , SUM(nwgh) as totalnwgh from test GROUP BY mid sqlfiddle : link of below result with above query mid cpid totalnwgh3 4 3.84 2 2.2 But above i need one more column that's as below : **totowgh** mid cpid totalnwgh totowgh3 4 3.8 6.3 (DISTINCT value as per pid column)4 2 2.2 3.9 where totowgh = 6.3 come by DISTINCT value as per pid columnthat's mid = 3 has count 5 but distinct pid = 4 for mid=3 same way "distinct" owgh = 6.3 for mid=3 and distinct pid.As pid=12 is count 1 time hence,1.5 + 0.6 + 1.2 + 3 = 6.3 ( please not this is as per DISTINCT value of pid )Please note : i need owgh value as per distinct pid or group by pid .. because if i replace value of owgh 0.6 with 1.5 then it will be 5.7 instead of 7.2 but value of owgh 0.6 belong to pid = 14 and not pid = 12 hence totalcount of owgh change ...but i need is 7.2SEE WHAT I MEANS : sqlfiddle.com/#!9/2a53c/6
×
×
  • Create New...