How to run multiple sql queries using php without giving load on mysql server?
Question by aslamdoctor
I have a script that reads an excel sheet containing list of products. These are almost 10000 products. The script reads these products & compares them with the products inside mysql database, & checks
-
if the product is not available, then ADD IT (so I have put insert query for that)
-
if the product is already available, then UPDATE IT (so I have put update query for that)
Now the problem is, it creates a very heavy load on mysql server & it shows a message as “mysql server gone away..”.
I want to know is there a better method to do this excel sheet work without making load on mysql server?
Answer by Starx
Ok, here is quick thought
Instead of running the query, after every check, where its present or not, add on to your sql as long as you reach the end and then finally execute it.
Example
$query = ""; //creat a query container
if($present) {
$query .= "UPDATE ....;"; //Remember the delimeter ";" symbol
} else {
$query .= "INSERT ....;";
}
//Now, finally run it
$result = mysql_query($query);
Now, you make one query at the last part.
Update: Approach this the another way
Use the query to handle it.
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
UPDATE table SET c=c+1 WHERE a=1;
