Need Help on UPDATE and UPDATE

[tobitimac]

 

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);
}
?>

 

[fugix]

what do you get when your var_dump($data) ?

[tobitimac]

what do you get when your var_dump($data) ?

 

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" }

 

[fugix]

do you get a valid int when this line is parsed

echo "$recordCount Rows\n";

[tobitimac]

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

 

[mikosiko]

Here is the $sql1 that you have... (just formated for clarity... no changes made)

    				
$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]'";

 

just to help you to debug your code... add this line below the $sql1 definition and check if the final sentence looks right or not

echo "Sql to execute : " . $sql1;

also, analyze all those AND's in the WHERE clause... are those AND's making any sense or you need to eliminate them and just match the record using the itemNumber field?... now you have something to work with.

[tobitimac]

Here is the $sql1 that you have... (just formated for clarity... no changes made)

    				
$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]'";

 

just to help you to debug your code... add this line below the $sql1 definition and check if the final sentence looks right or not

echo "Sql to execute : " . $sql1;

also, analyze all those AND's in the WHERE clause... are those AND's making any sense or you need to eliminate them and just match the record using the itemNumber field?... now you have something to work with.

 

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

[DavidAM]

See this thread concerning a similar problem.

 

Basically, you select using JUST the ID to find the record. You should use the same WHERE clause on the UPDATE. By trying to match every field in the row, against the NEW data, the update is not finding any rows to update.

 

[jcbones]

If you set the `itemNumber` as a UNIQUE key, you could then run a 'ON DUPLICATE UPDATE' query.  Which would insert a new row, unless it found a row with the itemNumber, then it would update the row.

 

 

Syntax