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???