Help with my code/structure

[jemq]

Hi, I am a newbie to php and sql.

I have a table called allpet_twb this contains products and pricing.
I have another table called import_allpet that contains products and updated pricing as well as new products.

What I am trying to do is go through each record in import_allpet and see if it is in allpet_twb (which is currently empty so my code shoudl insert new records). If so update the costex field otherwise insert a new record and assign the data from import_allpet to allpet_twb.

Here is my code.

// assign the query

$query = "SELECT * FROM import_allpet";

// Execute the query

// 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 "</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 );
		if (!$result2) {
			mysql_query( "INSERT INTO allpet_twb (allpetcode,description,costex) VALUES 
							$result_row[1],$result_row[2],$result_row[3] " );
			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"</tr>";
			}

		if ($result2){
			mysql_query( "UPDATE allpet_twb SET costex=$result_row[3] WHERE allpetcode=$result_row[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> 'UPDATED'</td>";
			echo"</tr>";

			}


}

When I run the code it always shows "UPDATED" as per the if ($result2) test but the table is not updated.

I also want to add some extra values into the allpet_twb table to show if the price has increased, decreased, the product is NEW or the product has been discontinued (i.i it is in the old pricelist but not the new one.

Thanks in adavnce

Jemq

[mikell]

if the insert and update queries are being run, which it appears they are if the rows in the table are being output, then echo the insert and update statements before or after they are run...maybe you will see something obvious as to why they are not working. if not, then run a few "manually" on the command line or using whatever mysql gui client you might be using and see what the response/error message is. that should give you some clues.

ex:

// Assign the query
$query = "SELECT * FROM import_allpet";

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

// 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 );
	if (!$result2) 
	{
		$insert = "INSERT INTO allpet_twb (allpetcode,description,costex) VALUES ($result_row[1],$result_row[2],$result_row[3])";
		mysql_query($insert);
		echo "INSERT: $insert <br>";
	}
	else
	{
		$update = "UPDATE allpet_twb SET costex = $result_row[3] WHERE allpetcode = $result_row[1]";
		mysql_query($update);
		echo "UPDATE: $update <br>";
	}
}

[jemq]

Thanks,

That helps a lot with diagnosis. I have done as you suggested and also enclosed my variables in '{ }' as per another suggestion. I now get th following output as per your suggested code.

INSERT: INSERT INTO allpet_twb (allpetcode,description,costex) VALUES '14.113','AQUARIUM 12X6X9" 8 LTR','10.68'

This to me seems a correct SQL statement, however, my table is allpet_twb still empty despite processing 5861 INSERT statements. I am unable to determine why this code is not inserting new records into the Table.

Thanks

Jemq

[Weedpacket]

A common debugging technique is to write "mysql_query($query) or die(mysql_error())" during development so that SQL syntax errors can be reported on (such as not having parentheses around the list of values being inserted).

[mikell]

In the version of mysql that I use, you have to have () around the values you are inserting...
I assume thats the case with the version you are using.

WRONG:
NSERT INTO allpet_twb (allpetcode,description,costex) VALUES '14.113','AQUARIUM 12X6X9" 8 LTR','10.68'

RIGHT:
NSERT INTO allpet_twb (allpetcode,description,costex) VALUES ('14.113','AQUARIUM 12X6X9" 8 LTR','10.68')

Thats why I said to try running the query using mysql command line or some gui yourself, so you could see if there
was an error message. When I ran it, this is what I got:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'null, 'test', 'test', 'test', 'test', 'Realtor', 'password', 0,'Active', 0, 1215' at line 1

My bad Weedpacket, didn't realize you already answered this.