insert and update row in mysql

[tobitimac]

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

        }
?>

[gizmola]

Your update syntax is wrong. 

 

It should be UPDATE inventory SET itemNumber='$data[1]', itemDesc='$data[2]', .... WHERE...

[tobitimac]

 

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

        }
?>

[gizmola]

Well yes I guess I didn't notice that this line is invalid:

 

$recordCount = @mysql_num_rows("Select * from inventory where itemNumber=".$data[0]);

 

What you need to do is do a query and assign that to a variable that will be the result.  Then you can use mysql_num_rows() on the result to see if there is one.  Just in terms of advice, don't use the @ until you are sure you know what you're doing as it hides errors.

[tobitimac]

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

[gizmola]

You do something like:

 

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

if ($result) {
  $recordCount = mysql_num_rows($result);
  if ($recordCount > 0) { // rest of your current code loop here.
  } else {
    // your current code.
  }
} else {
  die(mysql_error());
}

[tobitimac]

Hi,

 

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

[gizmola]

Let's see the current version of your script.

[tobitimac]

 

 

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

[gizmola]

Well, your first query can not succeed based on what you have right now, because I don't see anyplace that $data would be getting values from.  You used to have:

 

 

if (($handle = fopen('http://mlkishigo.com/sageonline/inventorylist.csv', "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) {
        $num = count($data);

 

Did you leave that out?  I'd actually like to see your entire script at present rather than a snippet.

[tobitimac]

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


        }
?>

[gizmola]

I editted your post to take out your pw's, and here I reformatted your code a little bit, and changed the mysql_error() call in the loop to echo rather than die in the outer loop.  I don't see anything overtly incorrect here.  Are you getting any errors?  What is the output?

 

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 {
		echo mysql_error();
	}
    }
    fclose($handle);
}
?>

[tobitimac]

 

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]);

[gizmola]

Right at the top under the line:

 

while (($data = fgetcsv($handle, 900000, ",")) !== FALSE) {

 

Add

 

var_dump($data);

 

So you can see if the $data array elements match up to what you expect.  I'm assuming that if the problem you pointed out above is related to data, it's that when you use itemNumber=, if itemNumber is a numeric data type as defined in your database, then it expects a number.  '2X' would not be a number.  If it's a character type like a varchar, then you need the quotes around it for the sql to be valid.

 

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

 

[tobitimac]

Right at the top under the line:

 

while (($data = fgetcsv($handle, 900000, ",")) !== FALSE) {

 

Add

 

var_dump($data);

 

So you can see if the $data array elements match up to what you expect.  I'm assuming that if the problem you pointed out above is related to data, it's that when you use itemNumber=, if itemNumber is a numeric data type as defined in your database, then it expects a number.  '2X' would not be a number.  If it's a character type like a varchar, then you need the quotes around it for the sql to be valid.

 

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

 

 

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

[gizmola]

I actually wanted you to run the var_dump and paste the results here so we can see what the data array contains.

[tobitimac]

 

 

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

[gizmola]

In your WHERE clause, if there is some combination of columns that can be used to uniquely identify one row from another, then you should use that, omitting the 'AND quantityHand='$data[2]' from the SELECT since it seems that is not what you want.  With that said, it seems like this question is morphing, and that we've solved your initial issues.  If you're now onto some new problem, please make a new thread with a new title that better reflects what your struggling with, and mark this one solved.