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]