Having trouble with insert and update

[tobitimac]

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

 

 

[DavidAM]

You need to determine something that makes a row unique. How do you know if a row is changed or if it was added? Your select statement is looking for a row that matches ALL of the CURRENT values (which might have changed). So, if someone changes the description for item number 4, your select will NOT find the row and will insert it.

 

If you choose something that is unique -- I would guess the itemNumber -- then select JUST on the item number, you can do the INSERT or UPDATE based on whether you find it or not.

[tobitimac]

You need to determine something that makes a row unique. How do you know if a row is changed or if it was added? Your select statement is looking for a row that matches ALL of the CURRENT values (which might have changed). So, if someone changes the description for item number 4, your select will NOT find the row and will insert it.

 

If you choose something that is unique -- I would guess the itemNumber -- then select JUST on the item number, you can do the INSERT or UPDATE based on whether you find it or not.

 

i have changed my select statement but still nothing is updated or inserted..

	$result = mysql_query("SELECT * FROM inventory WHERE itemNumber='$data[0]'");

 

[DavidAM]

The same logic applies to your UPDATE statement. The WHERE clause of the UPDATE should basically be the same as the SELECT.