syncronise two tables...

v3nt

i have created a file that copies entries accross to another table. But now i need to syncronize them, otherwise i will just be copying replica entries to the table and it will mess everything up.

pcode is the unique value, so if that does not exist the php file can then enter it into the map_hg table.

do i need to create some basic loop before $query can work?

Many thanks, dan.

$connection_l = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
mysql_select_db($db) or die ("Unable to select database!");

$query_PD = "SELECT * FROM property WHERE pavailable ='1'";
$result_PD = mysql_query($query_PD) or die ("Error in query: $query. " . mysql_error());

while($myrow_PD = mysql_fetch_array($result_PD)){
$pcode = $myrow_PD["pcode"]; 

$query = "INSERT INTO map_hg (id,  px, py, pcode, pavailable, pstatus, poption_a, poption_b) VALUES ('', '0','0', '$pcode','','','','')";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

}

$query_b = "SELECT * FROM map_hg";
$result_b = mysql_query($query_b) or die ("Error in query: $query. " . mysql_error());
while($myrow_b = mysql_fetch_array($result_b)){
$pcodeb = $myrow_PD["pcode"]; 
$ass .="$pcodeb<BR>";
$i++;
}

print "$ass<BR> $i";

herve

After the very first synchonizing of the two tables, you need
to have to check for new entries only in the first table.

A timestamp for instance.

That way your first query will be something like :

SELECT * FROM property WHERE (pavailable ='1') AND (date > '$lastCheck')

where "date" is a new column storing
the date of insertion in that table for that
record and "$lastcheck" is the timestamp
of the last synchronisation between
the two tables.

That SELECT will give you only the new entries in the first table.

So when you want to inject that in the second table, there won't be duplicate records (except if you can inject
multiple times the same record in the first table, I don't know your application specifications)

v3nt

i can't add any date fields! I can only use what i have. so pcode is the only thing i have to work with.

what about something like this-

if($pcode !== $pcode [in any table entry?] ){
$query = "INSERT INTO map_hg (id, px, py, pcode, pavailable, pstatus, poption_a, poption_b) VALUES ('', '0','0', '$pcode','','','','')";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
}else{

continue loop?
}