tobitimac

I have used the echo to see the sql query and its looks OK, but its not updating anything in MYSQL

Thanks am looking into my data now i think the problem might be because i have the data as 40.00- insteade of -40.00

Thanks for your reply. i used VARCHAR for quantityHand in my MySQL after using echo sprintf('%01.2f', $row['quantityHand']); and getting 40.00 instead of 40.00- because in my MySQL i have 40.00- Please help.

I have a code to sum up a field called quantityHand in MySQL, in my example below, the value always return 40 as a positive number instead of 40.00- 0.00 0.00 40.00- I have used intval() or floatval() function to convert the type of variable from string to int/float but still not working. Any help please? Here is the code $sql = mysql_query("SELECT DISTINCT itemNumber, itemDesc, quantityHand, SUM(quantityHand) AS quantityHand FROM inventory where itemNumber like '%$term%' GROUP BY `itemNumber` ORDER BY `itemNumber`"); while ($row = mysql_fetch_array($sql)){ echo "<b>"; echo "</td><td style=\"text-align: center;\"><b>"; echo $row['itemNumber']; echo "</td><td style=\"text-align: center;\"><b>"; echo $row['itemDesc']; //echo "</td><td style=\"text-align: right;\">"; echo "</td><td style=\"text-align: center;\"><b>"; echo $row ['quantityHand']; echo "</td></tr>"; echo "</b>";

Yes, i got a valid int. showing the number of rows affected.. Can i get a help on this?? the code is still not working

Something like this array(5) { [0]=> string(15) "1055-2X " [1]=> string(30) "ORANGE CLASS 2 MESH VEST " [2]=> string(4) "1329" [3]=> string(1) "Y" [4]=> string(1) "0" } array(5) { [0]=> string(15) "1055-2X " [1]=> string(30) "ORANGE CLASS 2 MESH VEST " [2]=> string(1) "0" [3]=> string(1) "Y" [4]=> string(1) "3" } array(5) { [0]=> string(15) "1055-2X " [1]=> string(30) "ORANGE CLASS 2 MESH VEST " [2]=> string(1) "0" [3]=> string(1) "Y" [4]=> string(3) "100" } array(5) { [0]=> string(15) "1055-2X " [1]=> string(30) "ORANGE CLASS 2 MESH VEST " [2]=> string(1) "0" [3]=> string(1) "Y" [4]=> string(3) "200" } array(5) { [0]=> string(15) "1055-3X " [1]=> string(30) "ORANGE CLASS 2 MESH VEST " [2]=> string(3) "362" [3]=> string(1) "Y" [4]=> string(1) "0" }

Hi , can you please help on this, all i want this script to do is the update the row if the information is editted or insert a new row when there is a new record in the excel sheet.. Right now is not inserting anything and the updating is not working as well. if (($handle = fopen('inventorylist.csv', "r")) !== FALSE) { while (($data = fgetcsv($handle, 900000, ",")) !== FALSE) { //var_dump($data); $num = count($data); $result = mysql_query("SELECT * FROM inventory WHERE itemNumber='$data[0]'"); if ($result) { $recordCount = mysql_num_rows($result); echo "$recordCount Rows\n"; if ($recordCount > 0) { // rest of your current code loop here. $sql1 = "UPDATE inventory SET itemNumber='$data[0]',itemDesc='$data[1]',quantityHand='$data[2]',category='$data[3]',Whse='$data[4]' WHERE itemNumber='$data[0]' AND itemDesc='$data[1]'AND quantityHand='$data[2]' AND category='$data[3]'AND Whse='$data[4]'"; mysql_query($sql1) or die(mysql_error()); } else { // your current code. $sql="INSERT into inventory(itemNumber,itemDesc,quantityHand,category,Whse) values ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')"; mysql_query($sql) or die(mysql_error()); } } else { echo mysql_error(); } } fclose($handle); } ?>

i have changed my select statement but still nothing is updated or inserted.. $result = mysql_query("SELECT * FROM inventory WHERE itemNumber='$data[0]'");

I want this code below to simply update the current data in MySQL if changes were made to the excel sheet, else if there is a new row added to the excel sheet i want it to add the new row into MySQL. all i want is simply update or insert. Please can someone help me out.. if (($handle = fopen('inventorylist.csv', "r")) !== FALSE) { while (($data = fgetcsv($handle, 900000, ",")) !== FALSE) { var_dump($data); $num = count($data); $result = mysql_query("SELECT * FROM inventory WHERE itemNumber='$data[0]' AND itemDesc='$data[1]'AND quantityHand='$data[2]' AND category='$data[3]' AND Whse='$data[4]'"); if ($result) { $recordCount = mysql_num_rows($result); //echo "$recordCount Rows\n"; if ($recordCount > 0) { // rest of your current code loop here. $sql1 = "UPDATE inventory SET itemNumber='$data[0]',itemDesc='$data[1]',quantityHand='$data[2]',category='$data[3]',Whse='$data[4]' WHERE itemNumber='$data[0]' AND itemDesc='$data[1]'AND quantityHand='$data[2]' AND category='$data[3]'AND Whse='$data[4]'"; mysql_query($sql1) or die(mysql_error()); } else { // your current code. $sql="INSERT into inventory(itemNumber,itemDesc,quantityHand,category,Whse) values ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')"; mysql_query($sql) or die(mysql_error()); } } else { echo mysql_error(); } } fclose($handle); } ?>

Here is what i have, and anytime the value of quantityHand changes, it will insert all the rows again into the mysql, making the mysql to have double row of the same record. if (($handle = fopen('http://mlkishigo.com/sageonline/inventorylist.csv', "r")) !== FALSE) { while (($data = fgetcsv($handle, 900000, ",")) !== FALSE) { var_dump($data); $num = count($data); $result = mysql_query("SELECT * FROM inventory WHERE itemNumber='$data[0]' AND itemDesc='$data[1]'AND quantityHand='$data[2]' AND category='$data[3]' AND Whse='$data[4]'"); if ($result) { $recordCount = mysql_num_rows($result); //echo "$recordCount Rows\n"; if ($recordCount > 0) { // rest of your current code loop here. $sql1 = "UPDATE inventory SET itemNumber='$data[0]',itemDesc='$data[1]',quantityHand='$data[2]',category='$data[3]',Whse='$data[4]' WHERE itemNumber='$data[0]' AND itemDesc='$data[1]'AND quantityHand='$data[2]' AND category='$data[3]'AND Whse='$data[4]'"; mysql_query($sql1) or die(mysql_error()); } else { // your current code. $sql="INSERT into inventory(itemNumber,itemDesc,quantityHand,category,Whse) values ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')"; mysql_query($sql) or die(mysql_error()); } } else { echo mysql_error(); } } fclose($handle); } ?>

After adding var_dump($data);. i added two two to '$data[0]'" still is not updating or inserting a new row, here is my code below. if (($handle = fopen('http://mlkishigo.com/sageonline/inventorylist.csv', "r")) !== FALSE) { while (($data = fgetcsv($handle, 900000, ",")) !== FALSE) { //var_dump($data); $num = count($data); $result = mysql_query("SELECT * FROM inventory WHERE itemNumber='$data[0]'"); if ($result) { $recordCount = mysql_num_rows($result); echo "$recordCount Rows\n"; if ($recordCount > 0) { // rest of your current code loop here. $sql1 = "UPDATE inventory SET itemNumber='$data[0]',itemDesc='$data[1]',quantityHand='$data[2]',category='$data[3]',Whse='$data[4]' WHERE itemNumber='$data[0]'"; mysql_query($sql1) or die(mysql_error()); } else { // your current code. $sql="INSERT into inventory(itemNumber,itemDesc,quantityHand,category,Whse) values ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')"; mysql_query($sql) or die(mysql_error()); } } else { echo mysql_error(); } } fclose($handle); } ?>

i used the new code but am getting this error. Unknown column '2X' in 'where clause' i believe its has to do with $result = mysql_query("SELECT * FROM inventory WHERE itemNumber=".$data[0]);

Hi Thanks for your quick response, here is the entire code, its really small code.. <? session_start(); //db connection $db = mysql_connect("xxx", "xxx", "xxx") or die("Could not connect."); if(!$db) die("no db"); if(!mysql_select_db("db70481_mlkishigo",$db)) die("No database selected."); //$row = 0; if (($handle = fopen('http://mlkishigo.com/sageonline/inventorylist.csv', "r")) !== FALSE) { while (($data = fgetcsv($handle, 900000, ",")) !== FALSE) { $num = count($data); $result = mysql_query("SELECT * FROM inventory WHERE itemNumber=".$data[0]); if ($result) { $recordCount = mysql_num_rows($result); echo "$recordCount Rows\n"; if ($recordCount > 0) { // rest of your current code loop here. $sql1 = "UPDATE inventory SET itemNumber='$data[0]',itemDesc='$data[1]',quantityHand='$data[2]',category='$data[3]',Whse='$data[4]' WHERE itemNumber='$data[0]' "; mysql_query($sql1) or die(mysql_error()); } else { // your current code. $sql="INSERT into inventory(itemNumber,itemDesc,quantityHand,category,Whse) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')"; mysql_query($sql) or die(mysql_error()); } } else { die(mysql_error()); } } fclose($handle); } ?>

Here is my new code $result = mysql_query("SELECT * FROM inventory WHERE itemNumber=".$data[0]); if ($result) { $recordCount = mysql_num_rows($result); echo "$recordCount Rows\n"; if ($recordCount > 0) { // rest of your current code loop here. $sql1 = "UPDATE inventory SET itemNumber='$data[0]',itemDesc='$data[1]',quantityHand='$data[2]',category='$data[3]',Whse='$data[4]' WHERE itemNumber='$data[0]' "; mysql_query($sql1) or die(mysql_error()); } else { // your current code. $sql="INSERT into inventory(itemNumber,itemDesc,quantityHand,category,Whse) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')"; mysql_query($sql) or die(mysql_error()); } } else { die(mysql_error()); }

Hi, I used the script but its not updating or inserting new records. What can i do now???

Am not so sure how you want to do it am really new to PHP.. Can you help with this, its really frustrating.

This is what i changed it to but still not updating, its only insert new rows. if (($handle = fopen('http://mlkishigo.com/sageonline/inventorylist.csv', "r")) !== FALSE) { while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) { $num = count($data); $recordCount = @mysql_num_rows("Select * from inventory where itemNumber=".$data[0]); if ($recordCount > 0) { $sql1 = "UPDATE inventory SET itemNumber='$data[1]',itemDesc='$data[2]',itemDesc='$data[3]',quantityHand='$data[4]',category='$data[5]',Whse='$data[6]' WHERE itemNumber='$data[1]' "; mysql_query($sql1) or die(mysql_error()); } else { $sql="INSERT into inventory(itemNumber,itemDesc,quantityHand,category,Whse) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')"; mysql_query($sql) or die(mysql_error()); } } fclose($handle); } ?>

how can i make this code to insert a single, multiple and update rows in the database. The code only insert new rows in the database. if (($handle = fopen('http://mlkishigo.com/sageonline/inventorylist.csv', "r")) !== FALSE) { while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) { $num = count($data); $recordCount = @mysql_num_rows("Select * from inventory where itemNumber=".$data[0]); if ($recordCount > 0) { $sql1 = "UPDATE inventory SET itemNumber='$data[1]' AND itemDesc='$data[2]' AND itemDesc='$data[3]' AND quantityHand='$data[4]' AND category='$data[5]' AND Whse='$data[6]' WHERE itemNumber='$data[1]' "; mysql_query($sql1) or die(mysql_error()); } else { $sql="INSERT into inventory(itemNumber,itemDesc,quantityHand,category,Whse) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')"; mysql_query($sql) or die(mysql_error()); } } fclose($handle); } ?>

thanks SO MUCH ITS WORKS..

how can i make this code to insert a single, multiple and update rows in the database. The code only insert new rows in the database. if (($handle = fopen('inventorylist.csv', "r")) !== FALSE) { while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) { $num = count($data); $sql="INSERT into inventory(itemNumber,itemDesc,quantityHand,category,Whse) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')"; mysql_query($sql) or die(mysql_error()); } fclose($handle); }

i have this small code but i will like to know add an echo that will say "No match found" if the search is not available in the database. <?php $term = $_POST['term']; $sql = mysql_query("SELECT DISTINCT itemNumber, itemDesc, quantityHand, SUM(quantityHand) AS quantityHand FROM inventory where itemNumber like '%$term%' GROUP BY `itemNumber` ORDER BY `itemNumber`"); while ($row = mysql_fetch_array($sql)){ echo "<b>"; echo "</td><td style=\"text-align: center;\"><b>"; echo $row['itemNumber']; echo "</td><td style=\"text-align: center;\"><b>"; echo $row['itemDesc']; //echo "</td><td style=\"text-align: right;\">"; echo "</td><td style=\"text-align: center;\"><b>"; echo $row['quantityHand']; echo "</td></tr>"; echo "</b>"; } if (!$term) { print "No match found"; } ?>

Thanks guy the problem is fixed. All i did was to change the select to be item number since am selecting by item number and i always used echo"$select"; to see what am selecting which help to see that i need to select to item number.

NOTHING CHANGES.. dont why its behaving this way..

Thanks Matt, still the same problem. nothing changed. the quantityHand is still not summing up..

Hi Can someone help me on this.. The SUM(quantityHand) is not summing up the quantityHand if(isset($select)&&$select!=""){ // Get records from database (table "name_list"). //$result=@mysql_query("select * FROM `inventory` WHERE id='$select' GROUP BY `itemNumber` ORDER BY `itemNumber`"); $result=@mysql_query("SELECT DISTINCT itemNumber, itemDesc, quantityHand, SUM(quantityHand) AS quantityHand FROM inventory WHERE id='$select' GROUP BY `itemNumber` ORDER BY `itemNumber`"); while ($row=@mysql_fetch_assoc($result)) { echo"<b>"; echo "<tr><td style=\"font-size: 15px;\"><b>"; echo $row['itemNumber']; echo "</td><td style=\"font-size: 15px;\"><b>"; echo $row['itemDesc']; //echo "</td><td style=\"text-align: right;\"><b>"; echo "</td><td style=\"font-size: 15px; text-align: center;\"><b>"; echo $row['quantityHand']; echo"</b>"; // End While loop } // End if statement. }