Update one table from another

[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

[derzok]

It looks like you aren't putting quotes around your sql variables. Might not be the problem, but it can't hurt. Try '{$result_row[4]}' - you might also want to consider prepared statements (it'll be a bit more efficient for the sql engine). PHP 5.something and up come with PDO.

You could also save yourself some time with "INSERT ON DUPLICATE KEY UPDATE" syntax: http://www.mysqlperformanceblog.com/2006/05/29/insert-on-duplicate-key-update-and-summary-counters/

[jemq]

Thanks,

That helps a lot with diagnosis. I have done as you suggested and enclosed my variables in '{ }' I also altered the code slightly to show the SQL statement to verify it as per another suggestion. Here is the cod.

// 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>";
}

if ($result2){
$update= "UPDATE allpet_twb SET '{costex}'='{$result_row[3]}' WHERE '{allpetcode}'='{$result_row[1]}'";
mysql_query ($update);
echo "UPDATE: $update <br>";
}
}


I now get the following output.

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

[Fumigator]

Always always always (always) check all queries for success or failure. Always. Don't assume anything. Do it every time you run a query.


$result = mysql_query($query);
//Check the query result, and display the error msg if it failed
if (!result) {
die("oopsie, there's an error! Query text: $query<br>Error: ".mysql_error());
}


And one more nag...

http://codingforums.com/showthread.php?t=68462