Please look at this small section of code and get me on the right track.....

I'm having a real problem grasping how "mysql_error" works. What I am attempting to do is to update a products data base with only the "in stock" items.
The code is supposed to do the following:

The first sql query sets all stock levels in the db to 0, i.e. out of stock. I then input a csv file which contains stock numbers of items that are in stock. The second sql query sets the stock levels to 5 by product number as contained in the csv file. Since there are stock numbers in the csv that are not in the db, I want to output a list of these numbers to the screen so that I can add these products at a later date.

I am under the impression that mysql_error returns an empty text string when there is no error, and an error msg if there is. Running the below code, I get absolutely no messages except the completion, but the database is updated properly.

<?
include './config.php';
#------------------------------------------------|
# INIT THE INVENTORY AND USE INVENTORY COLUMNS |
#------------------------------------------------|
mysql_query("UPDATE CubeCart_inventory SET stock_level=0, useStockLevel=1");

#-----------------------------------------------|
# READ THE CSV FILE |
#-----------------------------------------------|
$fcontents = file ('./Discontinued.csv');

for($i=0; $i<sizeof($fcontents); $i++) {
$line = trim($fcontents[$i]);
$arr = explode(";", $line);
$record = $arr[0];
$icode = 5;
#-------------------------------------------------------|
# Update record if it is in the db |
#-------------------------------------------------------|
mysql_query ("UPDATE CubeCart_inventory SET stock_level='$icode' WHERE productCode='$record'");
$error = mysql_error();
if ($error != "") {
echo $record ."is not in DB.<br>\n";
}

}
echo "<font color=green> Database update complete!!!!</font><br>";
?>

Thank you for any help,
Klaus Cook
Houston, TX

 

I would code it like this:

<?

include './config.php';
$query = mysql_query("UPDATE CubeCart_inventory SET stock_level=0, useStockLevel=1");

$lines = file('./Discontinued.csv');
foreach ($lines as $key => $value)
{
	$line = trim($value);
	if ($line!='') {
		$arr = explode(";", $line);
		$record = $arr[0];
		$icode = 5;
		// Delete LIMIT 1 at the end if there can be several records with that record number/code.
		$query = mysql_query('UPDATE CubeCart_inventory SET stock_level='.$icode.' WHERE productCode='.mysql_real_escape_string($record).' LIMIT 1');
		if (mysql_affected_rows($query)<1) {
			echo $record ."is not in DB.<br> - error number=".mysql_errno()." desc=".mysql_error()."\n";
		}
	}
}
echo "<font color=green> Database update complete!!!!</font><br>";
?>

PHP:

Haven't tested it but it should be working fine.
Basically I would use mysql_affected_rows to see how many rows has the update query changed. If no records were changed, it's obvious there is no row to change or there was an error querying the database.

 

mariush's code looks good, but I'd move the setting of $icode to outside the loop if it's always going to be the same value - no sense in setting the same thing over and over again for no reason.

 

Hey, thanks guys for the input. I used that code, but I had to make one change to it. PHP didn't like this:

if (mysql_affected_rows($query)<1)
It gave me an error of "Invalid argument to mysql_affected_rows".

So I changed it to this:

mysql_query("UPDATE CubeCart_inventory SET stock_level=".$icode." WHERE productCode=".mysql_real_escape_string($record)." LIMIT 1");
$numrow = mysql_affected_rows();
if ($numrow<1) {

Just testing affected_rows in the if statement didn't work either, I had to get the value into a variable before everything fell into place.

Again, thanks a bunch.........
Klaus Cook
Houston, Texas

BTW: Mariush, I love your country. I spent a month in Bucharest in the 1990s doing some consulting work at the Ministry of Geology. Didn't get out of the city too much, but there was enough to do in town to keep me busy.