Mysql Table Synchronization

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
karl_281
Forum Newbie
Posts: 3
Joined: Thu Nov 23, 2006 5:38 pm

Mysql Table Synchronization

Post by karl_281 »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi,

I have written a php code for a current project that allows the user to upload a csv file to the database, the csv contains 50,000+ records. I have the csv uploading to a table called upload that is working fine. I have then created a table called parts, this is the table that is assecced by the application to show the parts details, price, qty, etc... The csv is uploaded nightly to update any changes made during the day. What I want it to do is have the upload table synchronize with the parts table checking if any columns have changed, if they have update them, if the item doesn't exist then add it and then the parts table check the upload table if any items don't exist and remove it from the parts table.

Now I have written a piece of code that is doing this but the problem I am having is that when there is data in the parts table the synchronization starts and works for about 2-3 mins then comes up with an error MYSQL client has ran out of memory. When there is no data in the parts table the copy of the data has no problem. But the whole point is that it synchronizes with the parts table so that there is no down time.

Does anyone know a better way of doing this?

here is my current code (still a work in progress):

Code: Select all

$limit = 8000;
if (!get_cfg_var('safe_mode')) {
      set_time_limit($limit);
    }
	
$handle = fopen ("mtq_test.csv","r");

while ($data = fgetcsv ($handle, 1000, ",")) {
$query = "INSERT INTO upload (prc_dis, manufacturer, item_number, description, uom, price, flag, qty, item_one, item_two, item_three, item_four, item_five, item_six, item_seven, item_eight, item_nine) VALUES('".db_input($data[0])."', '".db_input($data[1])."', '".db_input($data[2])."', '".db_input($data[3])."', '".db_input($data[4])."', '".db_input($data[5])."', '".db_input($data[6])."', '".db_input($data[7])."', '".db_input($data[8])."', '".db_input($data[9])."', '".db_input($data[10])."', '".db_input($data[11])."', '".db_input($data[12])."', '".db_input($data[13])."', '".db_input($data[14])."', '".db_input($data[15])."', '".db_input($data[16])."')";
   $result = mysql_query($query) or die("Invalid query: " . mysql_error().__LINE__.__FILE__);
   $row++;
}
fclose ($handle);

$query="SELECT p.prc_dis, p.manufacturer, p.item_number, p.description, p.uom, p.price, p.flag, p.qty, p.item_one, p.item_two, p.item_three, p.item_four, p.item_five, p.item_six, p.item_seven, p.item_eight, p.item_nine, n.prc_dis, n.manufacturer, n.item_number, n.description, n.uom, n.price, n.flag, n.qty, n.item_one, n.item_two, n.item_three, n.item_four, n.item_five, n.item_six, n.item_seven, n.item_eight, n.item_nine FROM upload p, parts n WHERE p.item_number = n.item_number AND p.prc_dis != n.prc_dis OR p.manufacturer != n.manufacturer OR p.description != n.description OR  p.price != n.price OR p.uom != n.uom OR p.flag != n.flag OR p.qty != n.qty OR p.item_one != n.item_one OR p.item_two != n.item_two OR p.item_three != n.item_three OR p.item_four != n.item_four OR p.item_five != n.item_five OR p.item_six != n.item_six OR p.item_seven != n.item_seven OR p.item_eight != n.item_eight OR p.item_nine != n.item_nine";
$query=mysql_query($query) or die(mysql_error());

if(mysql_num_rows($query) >0) {

while($row=mysql_fetch_array($query)){

$prc_dis=$row['p.prc_dis'];
$manufacturer=$row['p.manufacturer'];
$id=$row['p.item_number'];
$desc=$row['p.description'];
$uom=$row['p.uom'];
$price=$row['p.price'];
$flag=$row['p.flag'];
$qty=$row['p.qty'];
$one=$row['p.item_one'];
$two=$row['p.item_two'];
$three=$row['p.item_three'];
$four=$row['p.item_four'];
$five=$row['p.item_five'];
$six=$row['p.item_six'];
$seven=$row['p.item_seven'];
$eight=$row['p.item_eight'];
$nine=$row['p.item_nine'];

$query="UPDATE parts SET prc_dis = '$prc_dis', manufacturer = '$manufacturer', item_number = '$id', description = '$desc', uom = '$uom', price = '$price', flag = '$flag', qty = '$qty', item_one = '$one', item_two = '$two', item_three = '$three', item_four = '$four', item_five = '$five', item_six = '$six', item_seven = '$seven', item_eight = '$eight', item_nine = '$nine'";

}
}
$query="SELECT item_number FROM upload";
$query=mysql_query($query) or die(mysql_error());

if(mysql_num_rows($query)>0) {

while($row=mysql_fetch_assoc($query)){

$id=$row['item_number'];

$find="SELECT item_number FROM parts WHERE item_number = '$id' LIMIT 1";
$find=mysql_query($find) or die(mysql_error());

if(mysql_num_rows($find)==0) {

$upload="SELECT * FROM upload WHERE item_number = '$id' LIMIT 1";
$upload=mysql_query($upload) or die(mysql_error());

$row=mysql_fetch_assoc($upload);

$prc_dis=db_input($row['prc_dis']);
$manufacturer=db_input($row['manufacturer']);
$item_number=db_input($row['item_number']);
$desc=db_input($row['description']);
$uom=db_input($row['uom']);
$price=db_input($row['price']);
$flag=db_input($row['flag']);
$qty=db_input($row['qty']);
$one=db_input($row['item_one']);
$two=db_input($row['item_two']);
$three=db_input($row['item_three']);
$four=db_input($row['item_four']);
$five=db_input($row['item_five']);
$six=db_input($row['item_six']);
$seven=db_input($row['item_seven']);
$eight=db_input($row['item_eight']);
$nine=db_input($row['item_nine']);

$insert="INSERT INTO parts (prc_dis, manufacturer, item_number, description, uom, price, flag, qty, item_one, item_two, item_three, item_four, item_five, item_six, item_seven, item_eight, item_nine) VALUES ('$prc_dis', '$manufacturer', '$item_number', '$desc', '$uom', '$price', '$flag', '$qty', '$one', '$two', '$three', '$four', '$five', '$six', '$seven', '$eight', '$nine')";
$insert=mysql_query($insert) or die(mysql_error());
}
}
}
echo'Operation Complete';

Thanks


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
iluxa
Forum Newbie
Posts: 15
Joined: Wed Nov 29, 2006 3:29 am

Re: Mysql Table Synchronization

Post by iluxa »

karl_281 wrote:

Code: Select all

$query="UPDATE parts SET prc_dis = '$prc_dis', manufacturer = '$manufacturer', item_number = '$id', description = '$desc', uom = '$uom', price = '$price', flag = '$flag', qty = '$qty', item_one = '$one', item_two = '$two', item_three = '$three', item_four = '$four', item_five = '$five', item_six = '$six', item_seven = '$seven', item_eight = '$eight', item_nine = '$nine'";
Are you sure you don't want any where clause here?
karl_281 wrote:

Code: Select all

$query="SELECT item_number FROM upload";
$query=mysql_query($query) or die(mysql_error());

if(mysql_num_rows($query)>0) {

while($row=mysql_fetch_assoc($query)){

$id=$row['item_number'];

$find="SELECT item_number FROM parts WHERE item_number = '$id' LIMIT 1";
$find=mysql_query($find) or die(mysql_error());

if(mysql_num_rows($find)==0) {

$upload="SELECT * FROM upload WHERE item_number = '$id' LIMIT 1";
$upload=mysql_query($upload) or die(mysql_error());

$row=mysql_fetch_assoc($upload);

$prc_dis=db_input($row['prc_dis']);
$manufacturer=db_input($row['manufacturer']);
$item_number=db_input($row['item_number']);
$desc=db_input($row['description']);
$uom=db_input($row['uom']);
$price=db_input($row['price']);
$flag=db_input($row['flag']);
$qty=db_input($row['qty']);
$one=db_input($row['item_one']);
$two=db_input($row['item_two']);
$three=db_input($row['item_three']);
$four=db_input($row['item_four']);
$five=db_input($row['item_five']);
$six=db_input($row['item_six']);
$seven=db_input($row['item_seven']);
$eight=db_input($row['item_eight']);
$nine=db_input($row['item_nine']);

$insert="INSERT INTO parts (prc_dis, manufacturer, item_number, description, uom, price, flag, qty, item_one, item_two, item_three, item_four, item_five, item_six, item_seven, item_eight, item_nine) VALUES ('$prc_dis', '$manufacturer', '$item_number', '$desc', '$uom', '$price', '$flag', '$qty', '$one', '$two', '$three', '$four', '$five', '$six', '$seven', '$eight', '$nine')";
$insert=mysql_query($insert) or die(mysql_error());
}
}
}
echo'Operation Complete';
The insertion part could probably be achieved with single insert statement.
something along the lines of

Code: Select all

INSERT INTO parts (...) AS SELECT ... FROM UPLOADS WHERE item_number NOT IN (SELECT item_number FROM parts)
Of course, this is an ugly query, and may actually need to be done in two steps (I'm not sure you can insert into the table that is part of select in subquery).
In that case you'd need to create a temp table.

Also in your code I noticed that you use "LIMIT 1" when getting data from upload table. I think it would
be better to create unique key on item_number in that table, and just not insert duplicates in there
(or clean it up in some other way - point being - do it before you have to retrieve data)
karl_281
Forum Newbie
Posts: 3
Joined: Thu Nov 23, 2006 5:38 pm

Post by karl_281 »

I have revised my code as the way I was doing it was very messy and I kept on coming up with memory errors. The problem I am now having is that I get an error in the mysql syntax in the update query. I have spent a number of hours re-writing but I am constantly getting the syntax 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 'WHERE item_number = '11039720' LIMIT 1' at line 1.

Could anyone please help.

This is the code:

Code: Select all

$update=array();
if($prc_dis1!=$prc_dis) 
{
$update[]="prc_dis='$prc_dis'";
}
if($manufacturer1!=$manufacturer)
{
$update[]="manufacturer='$manufacturer'";
}
if($desc1!=$desc)
{
$update[]="description='$desc'";
}
if($uom1!=$uom)
{
$update[]="uom='$uom'";
}
if($price1!=$price)
{
$update[]="price='$price'";
}
if($flag1!=$flag)
{
$update[]="flag='$flag'";
}
if($qty1!=$qty)
{
$update[]="qty='$qty'";
}
if($one1!=$one)
{
$update[]="item_one='$one'";
}
if($two1!=$two)
{
$update[]="item_two='$two'";
}
if($three1!=$three)
{
$update[]="item_three='$three'";
}
if($four1!=$four)
{
$update[]="item_four='$four'";
}
if($five1!=$five)
{
$update[]="item_five='$five'";
}
if($six1!=$six)
{
$update[]="item_six='$six'";
}
if($seven1!=$seven)
{
$update[]="item_seven='$seven'";
}
if($eight1!=$eight)
{
$update[]="item_eight='$eight'";
}
if($nine1!=$nine)
{
$update[]="item_nine='$nine'";
}

$query="UPDATE parts SET";
		foreach($update as $load){
		$load = quote_smart($load);
		$query = $query . "$load,";
		}
		$query = rtrim($query, ',') . " WHERE item_number = '".$id."' LIMIT 1";
		mysql_query($query) or die(mysql_error());
iluxa
Forum Newbie
Posts: 15
Joined: Wed Nov 29, 2006 3:29 am

Post by iluxa »

I don't think you can do LIMIT 1 in update statement itself.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

you can ;)


Please let us see the complete query via

Code: Select all

mysql_query($query) or die(mysql_error() .': '.$query);
iluxa
Forum Newbie
Posts: 15
Joined: Wed Nov 29, 2006 3:29 am

Post by iluxa »

In that case it'll be far easier to figure out what's wrong if karl_281 would echo whole statement
that he forms, and paste it here.
iluxa
Forum Newbie
Posts: 15
Joined: Wed Nov 29, 2006 3:29 am

Post by iluxa »

Although, after looking closer, I suspect what's missing is spaces between col='val' statements
karl_281
Forum Newbie
Posts: 3
Joined: Thu Nov 23, 2006 5:38 pm

Post by karl_281 »

Thanks for you help I got the script working, it wasn't putting in any values because it was checking if there was a change in values if there wasn't it was trying to execute the query without the col='$val' so to fix it I put in

Code: Select all

if(count($update) > 0)      
{
and it is working finally.

The problem now is that when I execute the whole script including the csv upload the browser will set to done after 30 seconds, the upload continues but nothing under that query is executed. I have put in some code at the top to stop this, it works on my machine but not on the remote server.

This is my full code:

Code: Select all

//Set query time limit
$limit = 32000;
if (!get_cfg_var('safe_mode')) {
      set_time_limit($limit);
    }

//Start CSV data upload to db
	
$handle = fopen ("mtq.csv","r");

while ($data = fgetcsv ($handle, 400, ",")) {
$query = "INSERT INTO upload (prc_dis, manufacturer, item_number, description, uom, price, flag, qty, item_one, item_two, item_three, item_four, item_five, item_six, item_seven, item_eight, item_nine) VALUES('".db_input($data[0])."', '".db_input($data[1])."', '".db_input($data[2])."', '".db_input($data[3])."', '".db_input($data[4])."', '".db_input($data[5])."', '".db_input($data[6])."', '".db_input($data[7])."', '".db_input($data[8])."', '".db_input($data[9])."', '".db_input($data[10])."', '".db_input($data[11])."', '".db_input($data[12])."', '".db_input($data[13])."', '".db_input($data[14])."', '".db_input($data[15])."', '".db_input($data[16])."')";
   $result = mysql_query($query) or die("Invalid query: " . mysql_error().__LINE__.__FILE__);
   $row++;
}
fclose ($handle);

//Start part table and upload table synchronization

$query="SELECT DISTINCT prc_dis, manufacturer, item_number, description, uom, price, flag, qty, item_one, item_two, item_three, item_four, item_five, item_six, item_seven, item_eight, item_nine FROM upload";
$query=mysql_query($query) or die(mysql_error());

if(mysql_num_rows($query) >0) {

while($row=mysql_fetch_array($query)){

$prc_dis=db_input($row['prc_dis']);
$manufacturer=db_input($row['manufacturer']);
$id=db_input($row['item_number']);
$desc=db_input($row['description']);
$uom=db_input($row['uom']);
$price=db_input($row['price']);
$flag=db_input($row['flag']);
$qty=db_input($row['qty']);
$one=db_input($row['item_one']);
$two=db_input($row['item_two']);
$three=db_input($row['item_three']);
$four=db_input($row['item_four']);
$five=db_input($row['item_five']);
$six=db_input($row['item_six']);
$seven=db_input($row['item_seven']);
$eight=db_input($row['item_eight']);
$nine=db_input($row['item_nine']);

$query="SELECT DISTINCT prc_dis, manufacturer, item_number, description, uom, price, flag, qty, item_one, item_two, item_three, item_four, item_five, item_six, item_seven, item_eight, item_nine FROM parts WHERE item_number = '$id' LIMIT 1";
$query=mysql_query($query) or die(mysql_error());

//if item number exist check if has changed
if(mysql_num_rows($query)>0) {

$part=mysql_fetch_array($query);

$prc_dis1=db_input($part['prc_dis']);
$manufacturer1=db_input($part['manufacturer']);
$id1=db_input($part['item_number']);
$desc1=db_input($part['description']);
$uom1=db_input($part['uom']);
$price1=db_input($part['price']);
$flag1=db_input($part['flag']);
$qty1=db_input($part['qty']);
$one1=db_input($part['item_one']);
$two1=db_input($part['item_two']);
$three1=db_input($part['item_three']);
$four1=db_input($part['item_four']);
$five1=db_input($part['item_five']);
$six1=db_input($part['item_six']);
$seven1=db_input($part['item_seven']);
$eight1=db_input($part['item_eight']);
$nine1=db_input($part['item_nine']);

//Check if data has changed
$update=array();
if($prc_dis1!=$prc_dis) 
{
$update[]="prc_dis='$prc_dis'";
}
if($manufacturer1!=$manufacturer)
{
$update[]="manufacturer='$manufacturer'";
}
if($desc1!=$desc)
{
$update[]="description='$desc'";
}
if($uom1!=$uom)
{
$update[]="uom='$uom'";
}
if($price1!=$price)
{
$update[]="price='$price'";
}
if($flag1!=$flag)
{
$update[]="flag='$flag'";
}
if($qty1!=$qty)
{
$update[]="qty='$qty'";
}
if($one1!=$one)
{
$update[]="item_one='$one'";
}
if($two1!=$two)
{
$update[]="item_two='$two'";
}
if($three1!=$three)
{
$update[]="item_three='$three'";
}
if($four1!=$four)
{
$update[]="item_four='$four'";
}
if($five1!=$five)
{
$update[]="item_five='$five'";
}
if($six1!=$six)
{
$update[]="item_six='$six'";
}
if($seven1!=$seven)
{
$update[]="item_seven='$seven'";
}
if($eight1!=$eight)
{
$update[]="item_eight='$eight'";
}
if($nine1!=$nine)
{
$update[]="item_nine='$nine'";
}
//if data has changed update
if(count($update) > 0)      
{
$query="UPDATE parts SET";
		foreach($update as $load){
		$load = quote_smart($load);
		$query = $query . "$load, ";
		}
		$query = rtrim($query, ',') . " WHERE item_number = '".$id."' LIMIT 1";
		mysql_query($query) or die(mysql_error() .': '.$query);
}
}
//if item number doesn't exist insert into database
else
{
$query="INSERT INTO parts (prc_dis, manufacturer, item_number, description, uom, price, flag, qty, item_one, item_two, item_three, item_four, item_five, item_six, item_seven, item_eight, item_nine) VALUES('$prc_dis', '$manufacturer', '$item_number', '$desc', '$uom', '$price', '$flag', '$qty', '$item_one', '$item_two', '$item_three', '$item_four', '$item_five', '$item_six', '$item_seven', '$item_eight', '$item_nine')";
mysql_query($query) or die(mysql_error());
} 
}
}
//delete all data from table
$query = "TRUNCATE TABLE upload";
$query = mysql_query($query) or die(mysql_error());

//Optimize table
$query = "OPTIMIZE TABLE upload";
$query = mysql_query($query) or die(mysql_error());

echo "Operations Completed Successfully...";
Post Reply