[RESOLVED] Unreliable updates

[jemq]

I am running a PHP update code to update prices in a catalogue.

The import table has 5862 records. The catalogue table is empty.

When I execute the following code (with the mysql_query($insert) commented out)

My $new variable reads 5862 at the end of the code completion.

However, if I un-comment and execute the code again I get a random number of new records in my catalogue table and my last 2 lines of code never execute.

I suspect that something in the import table is possibly corrupting the code and it is crashing but I cannot find any suspect items.

Any suggestions please

Thanks

JemQ

$new = 0;
$updated = 0;

// assign the query

$query = "SELECT * FROM import_allpet LIMIT 9999";

// Execute the query
$result = mysql_query( $query );
if (!$result){
die ("Could not query the database: <br />". mysql_error( ));
}

// Setup the Table
echo "<table width='0' border='1' cellpadding='1'>";
echo "<tr>";
echo "<th scope='col'>TWB Code</th>";
echo "<th scope='col'>Allpet Code</th>";
echo "<th scope='col'>Description</th>";
echo "<th scope='col'>Cost Ex $</th>";
echo "<th scope='col'>Updated</th>";
echo "<th scope='col'>Updated/New Count</th>";
echo "</tr>";

// Fetch and display the results
while ($result_row = mysql_fetch_row(($result))){

		$query2 = "SELECT * FROM allpet_twb WHERE allpetcode='{$result_row[1]}'";
		$result2= mysql_query( $query2 );
		$numrows = mysql_num_rows($result2);
		if ( $numrows > 0) {
			//if (costex == '{$result_row[3]}'){
				$update= "UPDATE allpet_twb SET '{costex}'='{$result_row[3]}, status = 
				'Updated' WHERE	'{allpetcode}'='{$result_row[1]}'";
				//mysql_query($update);
				$updated = $updated + 1;
				echo"<tr>";
			    echo"<td> $result_row[4]</td>";
		    	echo"<td> $result_row[1]</td>";
		    	echo"<td> $result_row[2]</td>";
		    	echo"<td align='right'>$ $result_row[3]</td>";
				echo"<td>UPDATE</td>";
				echo"<td>$updated / $new</td>";
				echo"</tr>";
			//}


			}

		else{
			$insert="INSERT INTO allpet_twb (allpetcode,description,costex,status) VALUES 
						('{$result_row[1]}','{$result_row[2]}','{$result_row[3]}','NEW') ";
			[COLOR="Red"]//mysql_query($insert);[/COLOR]
			$new = $new + 1;
			echo"<tr>";
		    echo"<td> $result_row[4]</td>";
		    echo"<td> $result_row[1]</td>";
	    	echo"<td> $result_row[2]</td>";
	    	echo"<td align='right'>$ $result_row[3]</td>";
			echo"<td>NEW</td>";
			echo"<td>$updated / $new</td>";
			echo"</tr>";

			}


}

echo "Records Updated = $updated <br>";
echo "New Records = $new <br>";

[mikell]

I suspect that something in the import table is possibly corrupting the code and it is crashing but I cannot find any suspect items.

That would be my guess too...look at the data in the import table and make sure the
data types match up to the table you are importing them into.

[NogDog]

Don't know if it will fix the problem you're seeing, but for that insert query, any values which are not numeric should be filtered via mysql_real_escape_string() in case they contain any characters that would cause an SQL syntax error. You can also help yourself debug by checking the result of mysql_query() and log/display an error if it's false, e.g.:

$result = mysql_query($query);
if($result == false)
{
   user_error(mysql_error()."<br />\n$query");
}

[jemq]

Thanks for the suggestions. I have tried both as well as using simple test data.
I get no error message. I have a test import file of 6000 records. If I run the code with simple test data it will still only load about 967 records (between 800-999) and the number it loads each time I run the query changes with no alteration in code or data. The code still does not complete as it does not echo my last two variables. I appreciate your efforts but this is driving me crazy.

OK, further information. I have enable the General Query Log on my SQL server and it processes along fine and then th only message after the last INSERT statement is

6 Quit

[NogDog]

Sounds like your script is timing out. I'd suggest starting the script with:

<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);
set_time_limit(600); // timeout in seconds, set to 0 for no timeout
//...rest of script...
?>

[jemq]

That has fixed the problem. Thank you so much for your input.
I can now continue on with my project. Much appreciated.

[bradgrafelman]

Don't forget to mark this thread resolved.