Jump to content

shashib

Members
  • Content Count

    45
  • Joined

  • Last visited

Everything posted by shashib

  1. 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>"; } } }
  2. 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 ....
  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. shashib

    Group by value ( remarks ) below tr

    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.....
  5. shashib

    Group by value ( remarks ) below tr

    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
  6. shashib

    Group by value ( remarks ) below tr

    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
  7. shashib

    Group by value ( remarks ) below tr

    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
  8. shashib

    Group by value ( remarks ) below tr

    i have modified my question ..please see
  9. shashib

    Group by value ( remarks ) below tr

    any one here ..please help me ...any other solution with other table tr format will help me tooo
  10. shashib

    Excel to PHP - MYQL

    ------------------------------|------------------ 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***
  11. shashib

    Excel to PHP - MYQL

    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 ....
  12. shashib

    Excel to PHP - MYQL

    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 ...
  13. shashib

    Excel to PHP - MYQL

    Any with solution .....
  14. shashib

    Excel to PHP - MYQL

    any one here ?????
  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
  16. Cat and sub category Table (cat_tbl) : id | cat_n | parent_id1 | cat | 02 | dog | 03 | tiger | 24 | lion | 05 | abc | 06 | bcd | 3 Now i have a product table as below (prod_tbl) : id | pwght | cid | cpid10 | 1.2 | 1 | 011 | 2.4 | 2 | 012 | 3.4 | 2 | 013 | 4.5 | 6 | 3 and user final weight product table is below (userprod_tbl) id | pwght | cid | cpid | prod_id ( is above prod_tbl primary id )1 | 1.1 | 1 | 0 | 102 | 2.3 | 2 | 0 | 113 | 3.1 | 3 | 2 | 124 | 4.0 | 6 | 3 | 13 **RESULT : ( OUTPUT WHICH I WANT ) IS comparison of prod_tbl with userprod_tbl as below :** Prod tbl Userprod tblcat 1.2 cat 1.1dog 2.4 dog -- -- 2.3dog 3.4 dog tiger -- 3.1dog 4.5 dog tiger bcd 4.0 Hence in above result 2.4,3.4,4.5 are belong to Parent 2**But i am getting as below** Prod tbl Userprod tblcat 1.2 cat 1.1dog 2.4 dog -- -- 2.3dog 3.4 dog tiger -- 3.1 here i am not getting 4.5 value as 4.5 has 6,3 relation from above prod table but its parent of 2**Below is my query which i have return:** SELECT pt.pwght , upt.pwght ,ct.cat_n,uct.cat_n,umct.cat_nFROM prod_tbl AS ptLEFT JOIN userprod_tbl AS upt ON (pt.id = upt.prod_id)LEFT JOIN cat_tbl AS ct ON pt.packet_id = ct.idLEFT JOIN cat_tbl AS uct ON upt.packet_id = uct.idLEFT JOIN cat_tbl AS umct ON upt.parent_packet_id = umct.id Please let me know what is missingThanks
  17. NO SIR not used that ... actauly i am storing that main cat id and its parent id cat in product table ..becuase i have to manage that record with some inbuild functionality i know its bit complicated ...but to get fast result as per 6,3 ... but geting all result as per 3 is making more complex ..i know i need to go with recurisve function ..but yes i havent used that before ...can you please guide me on same .. thanks
  18. @ justsomeguy sir, can you tell me with example with above as a example . i need to store cat id in product and user product table as i need to manitain the forigen relation with cat and prod,userprod table .... hence i have put cat id in both tables .... please guide me so that in my next phase of my s/w i can improve this better to get result ... as now if above query i am get only parent and its single child ..... not n level childs pls help me
  19. Cat and sub category Table (cat_tbl) : id| cat_n | parent_id1 | cat | 02 | dog | 03 | tiger | 24 | lion | 05 | abc | 06 | bcd | 3 Now i have a product table as below (prod_tbl) : id | pwght | cid | cpid10 | 1.2 | 1 | 011 | 2.4 | 2 | 012 | 3.4 | 2 | 013 | 4.5 | 6 | 3 and user final weight product table is below (userprod_tbl) id | pwght | cid | cpid | prod_id ( is above prod_tbl primary id )1 | 1.1 | 1 | 0 | 102 | 2.3 | 2 | 0 | 113 | 3.1 | 3 | 2 | 124 | 4.0 | 6 | 3 | 13 **RESULT : ( OUTPUT WHICH I WANT ) IS comparison of prod_tbl with userprod_tbl as below :** Prod tbl Userprod tblcat 1.2 cat 1.1dog 2.4 dog -- -- 2.3dog 3.4 dog tiger -- 3.1dog 4.5 dog tiger bcd 4.0 Hence in above result 2.4,3.4,4.5 are belong to Parent 2**But i am getting as below** Prod tbl Userprod tblcat 1.2 cat 1.1dog 2.4 dog -- -- 2.3dog 3.4 dog tiger -- 3.1here i am not getting 4.5 value as 4.5 has 6,3 relation from above prod table but its parent of 2 **Below is my query which i have return:** SELECT pt.pwght , upt.pwght ,ct.cat_n,uct.cat_n,umct.cat_nFROM prod_tbl AS ptLEFT JOIN userprod_tbl AS upt ON (pt.id = upt.prod_id)LEFT JOIN cat_tbl AS ct ON pt.packet_id = ct.idLEFT JOIN cat_tbl AS uct ON upt.packet_id = uct.idLEFT JOIN cat_tbl AS umct ON upt.parent_packet_id = umct.id Please let me know what is missingThanks
  20. any one ..please help me... actually its combine of this : http://w3schools.invisionzone.com/index.php?showtopic=52843&hl= i got solution on above link, thanks to davej ...but above question parent id wise result is not matching here ....
  21. shashib

    How to get record in while loop

    producttable : id | mid | pid | owgh | nwgh | 1 3 12 1.5 0.6 2 3 12 1.5 0.3 3 3 14 0.6 0.4 4 3 15 1.2 1.1 5 4 16 1.5 1.0 6 4 17 2.4 1.2 7 3 19 3.0 1.4 8 3 19 3.0 1.2 I need result in while loop as **below** : as per mysql query $sql = "SELECT pid,owgh,nwgh from produttable WHERE mid = 3 "; **RESULT I WANT AS BELOW :** Product Id | Old weight | New weight --------------------------------------- 12 1.5 0.6 12 1.5 0.3 Tot: 3.0 0.9 -------------------------------- 14 0.6 0.4 Tot: 0.6 0.4 -------------------------------- 15 1.2 1.1 Tot: 1.2 1.1 -------------------------------- 19 3.0 1.4 19 3.0 1.2 Tot: 6.0 2.6 $query = mysql_query($sql); echo"<table> <tr> <td> Product Id </td> <td> Old Weight </td> <td> New Wight </td> </tr> "; while($row = mysql_fetch_array($query )){ echo "<tr>"; echo "<td>".$row['pid']."</td>"; echo "<td>".$row['owgh']."</td>"; echo "<td>".$row['nwgh']."</td>"; echo "</tr>"; echo "<tr><td>-----</td> <td>Tot : </td> <td> </td></tr>"; } echo " </table>"; But result i am getting is as below Product Id | Old weight | New weight --------------------------------------- 12 1.5 0.6 -------------- Tot 12 1.5 0.3 ---------------Tot 14 0.6 0.4 ---------------Tot 15 1.2 1.1 ---------------Tot 19 3.0 1.4 ---------------Tot 19 3.0 1.2which i dont want , **i want as below** : Product Id | Old weight | New weight --------------------------------------- 12 1.5 0.6 12 1.5 0.3 Tot: 3.0 0.9 -------------------------------- 14 0.6 0.4 Tot: 0.6 0.4 -------------------------------- 15 1.2 1.1 Tot: 1.2 1.1 -------------------------------- 19 3.0 1.4 19 3.0 1.2 Tot: 6.0 2.6 **Update : 27/3/2015**Can i get below result, with sum of each Product Id | Old weight | New weight --------------------------------------- 12 1.5 0.6 12 1.5 0.3 Tot: 3.0 0.9 -------------------------------- 14 0.6 0.4 Tot: 0.6 0.4 -------------------------------- 15 1.2 1.1 Tot: 1.2 1.1 -------------------------------- 19 3.0 1.4 19 3.0 1.2 Tot: 6.0 2.6------------------------------------------Grand Total : 6.3 5 Note in above Old weight : repeat value is not added thats 1.5 , 3.0 is coming twice hence while addition takensingle of it**OR** Product Id | Old weight | New weight --------------------------------------- 12 1.5 0.6 1.5 0.3 Tot: 3.0 0.9 -------------------------------- 14 0.6 0.4 Tot: 0.6 0.4 -------------------------------- 15 1.2 1.1 Tot: 1.2 1.1 -------------------------------- 19 3.0 1.4 3.0 1.2 Tot: 6.0 2.6------------------------------------------ Grand Total : 6.3 5Note in above Old weight : repeat value is not added thats 1.5 , 3.0 is coming twice hence while addition takensingle of it as product id 12, 19 are coming twice hence shown once with its resp valuePlease help me, if i am wrong in HTML tr td format please correct me , it not necessary to get in same table tr,td ..i just want my result in above format
  22. shashib

    How to get record in while loop

    Thanks u so much
  23. shashib

    How to get record in while loop

    Hi davej , Thanks for reply ... Sir Old Weight is coming wrong .. its taking SUM of all ... but if you check above i need only SUM of ( 1.5 + 0.6 + 1.2 + 3.0 ) = 6.3 but its doing actually ( (1.5 + 1.5)+ 0.6 + 1.2 + (3.0 +3.0 )) ii dont want that repeated value sum of same group but if sum of this should work ( 1.5 + 0.6 + 1.5 + (3.0 +3.0 )) in this 1.5 are of other group id hence its should added Hope you got me .... and sir New weight is working perfect ... if i have another column suppose as name = another_weight which values are ( 1+2+4+5+6+7) = 25 .. which belong to each above resp. id ..then can i get sum of it Thanks
  24. shashib

    How to get record in while loop

    Hi , Can i get Grand Total : at last ? of each column Please note : in old weight column weight are repeated twice , trice but i want its single value to be added as below : Product Id | Old weight | New weight --------------------------------------- 12 1.5 0.6 12 1.5 0.3 Tot: 3.0 0.9 -------------------------------- 14 0.6 0.4 Tot: 0.6 0.4 -------------------------------- 15 1.2 1.1 Tot: 1.2 1.1 -------------------------------- 19 3.0 1.4 19 3.0 1.2 Tot: 6.0 2.6------------------------------------------ Grand Total : 6.3 5 Note in above Old weight : repeat value is not added thats 1.5 , 3.0 is coming twice hence while addition takensingle of it**OR** Product Id | Old weight | New weight --------------------------------------- 12 1.5 0.6 1.5 0.3 Tot: 3.0 0.9 -------------------------------- 14 0.6 0.4 Tot: 0.6 0.4 -------------------------------- 15 1.2 1.1 Tot: 1.2 1.1 -------------------------------- 19 3.0 1.4 3.0 1.2 Tot: 6.0 2.6------------------------------------------ Grand Total : 6.3 5Note in above Old weight : repeat value is not added thats 1.5 , 3.0 is coming twice hence while addition takensingle of it echo "<table><tr><td> Product Id </td><td> Old Weight </td><td> New Weight </td></tr>";$pid = null;$sumold = 0;$sumnew = 0;while($row = mysql_fetch_array($query)){if ($row['pid'] == $pid || $pid === null){echo "<tr>";echo "<td>".$row['pid']."</td>";echo "<td>".$row['owgh']."</td>";echo "<td>".$row['nwgh']."</td>";echo "</tr>";$pid = $row['pid'];$sumold += $row['owgh'];$sumnew += $row['nwgh'];}else{echo "<tr><td></td><td></td><td></td></tr><tr><td></td><td>Tot :".$sumold."</td><td>".$sumnew."</td></tr><tr><td>------</td><td>------</td><td>------</td></tr>";echo "<tr>";echo "<td>".$row['pid']."</td>";echo "<td>".$row['owgh']."</td>";echo "<td>".$row['nwgh']."</td>";echo "</tr>";$sumold = $row['owgh'];$sumnew = $row['nwgh'];$pid = $row['pid'];}}echo "<tr><td></td><td></td><td></td></tr><tr><td></td><td>Tot :".$sumold."</td><td>".$sumnew."</td></tr><tr><td>------</td><td>------</td><td>------</td></tr><tr><td>Grand Tol </td><td>Total OLD WGT</td><td>Total NEW WGT</td></tr></table>"; Please help me
×