Update one database from another.

jemq

I have 2 databases. 1 is a pricelist (allpet). 2 is an updated pricelist (import).
What I want to do is get all items from IMPORT and check each one against ALLPET. If a product code exists in ALLPET, update the price. If a product code does not exist INSERT a new record in ALLPET.

I have the following code but am stuck with regards the If then else statement and the correct coding for the SQL Queries $insert and $update.

<?php

$db = mysql_connect("localhost", "root", "jemq1312");
mysql_select_db("allpet",$db) or die ('Unable to connect to database');

$updated=0;
$inserted=0;

$q="SELECT * FROM import";

$update = "UPDATE allpet SET allpetcode= (SELECT import.allpetcode FROM import WHERE import.allpetcode=allpet.allpetcode) WHERE EXISTS (SELECT import.allpetcode FROM import WHERE import.allpetcode=allpet.allpetcode)"

$insert = "INSERT INTO allpet (allpetcode,description,costex) VALUES (import.allpetcode, import.description, import.costex)"

$result = mysql_query( $q, $db )or die(" - Failed query import : <pre>$q</pre> Error: " . mysql_error());

$num_rows = mysql_num_rows($result);

if ($myrow = mysql_fetch_array($result)) {

do {
// REQUIRES IF STATEMENT here to select either

mysql_query( $update, $db )or die(" - Failed UPDATE RECORD <pre>$q</pre> Error: " . mysql_error());
$updated++;

//ELSE

{ mysql_query( $insert, $db ) or die(" - Failed INSERT NEW RECORD <pre>$q</pre> Error: " . mysql_error());
$inserted++;

} while ($myrow = mysql_fetch_array($result));

}
mysql_free_result($result);
mysql_close($db);

echo "Upates = : " $updated;
echo "Insert = : " $inserted;

Thanks for your help

Weedpacket

Get the dbms to do the work. See
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

jemq

Thanks excellent answer. However,

Now Getting the following

"Error: 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 '12X6X9" 8 LTR, 10.68) ON DUPLICATE KEY UPDATE costex=10.68' at line 2"

from this code

mysql_query( "INSERT INTO allpet (allpetcode,description,costex)
VALUES ($myrow[allpetcode], $myrow[description], $myrow[costex])
ON DUPLICATE KEY UPDATE costex=$myrow[costex]", $db )
or die(" - Failed UPDATE RECORD <pre>$q</pre> Error: " . mysql_error());