Sorry for vague question.
We need to run a stock update on a remote server (not ours). After about 300 updates, it times out.
So I have set it to run per category. You select the category, hit the button, and it does it every time as they all have under 300 products.
However, we would like to be able to have it churn them all over - so it finds all products assigned to catid 1, and runs the query. Waits 30 seconds. Then it runs the query on the next found catid, such as catid 23.
I know how to run a query to find the catids, and the only way I know how to make a page refresh is via the HTML refresh code. But it might refresh too soon, or it might run the query on 5 products, and the refresh is set to 30 seconds, so you are waiting for about 20 seconds before it finally refreshes.
The second problem with this option is to what URL do you make it refresh. In theory it must do it to the next CatID, but how do you know what is the next CATID, and how do you not make it refresh from the start.
In theory, it's a query on catid and catname, group by catid, then query the stockid against the remote server for as long as there are stockid's to query.
Once done..... then you run the refresh perhaps. The only way I can see how to do it like that, is to do a count at the start - once the count as reached the number, then run the refresh. But how do I then know what CATid to refresh the page to?
Any help or thoughts please.
How can I query one Catid at a time, with a pause betwn...?
Moderator: General Moderators
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
How can I query one Catid at a time, with a pause betwn...?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How can I query one Catid at a time, with a pause betwn.
Should this work?
It does do things, but it never pauses.
It does do things, but it never pauses.
Code: Select all
<?php
$now = time();
$resultcat = mysql_query ("SELECT catid, catname FROM products WHERE pause = 'off' GROUP BY catname");
while ($rowcat = mysql_fetch_object($resultcat))
{
$result = mysql_query ("SELECT id, codeFROM products WHERE pause = 'off' AND catid = '$rowcat->catid'");
$num_rows = mysql_num_rows($result);
while ($row = mysql_fetch_object($result))
{
$count = $count + 1;
if ($count <= $num_rows)
{
{ mysql_query ("UPDATE products SET rcstock = 'out of stock' WHERE id = '$row->id'"); }
else
{ mysql_query ("UPDATE products SET rcstock = 'in stock' WHERE id = '$row->id'"); }
if ($count == $num_rows)
{
while ($now + 10 > time()) {
echo "waiting a moment<br/>";
}
}
}
} mysql_free_result($result);
} mysql_free_result($resultcat);
?>Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How can I query one Catid at a time, with a pause betwn.
Look into the sleep() function rather than your while() loop.
Does this remote server have a query limit - such as a maximum of 300 queries per hour?
You could also try changing up your queries so you query multiple items at a time, rather than individually. For example, rather than running one UPDATE query per product ID, use your while loop to generate an array of which IDs are in stock, and which are out of stock. Then build 1 query for all in-stock ids, and one for all out-of-stock ids.
Your select queries are both querying the `products` table as well - there's a good chance you can combine those into 1 query.
Does this remote server have a query limit - such as a maximum of 300 queries per hour?
You could also try changing up your queries so you query multiple items at a time, rather than individually. For example, rather than running one UPDATE query per product ID, use your while loop to generate an array of which IDs are in stock, and which are out of stock. Then build 1 query for all in-stock ids, and one for all out-of-stock ids.
Your select queries are both querying the `products` table as well - there's a good chance you can combine those into 1 query.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How can I query one Catid at a time, with a pause betwn.
How would i combine all 'in stock' products into an array - never done that before.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How can I query one Catid at a time, with a pause betwn.
Well you're currently looping through all your results and have a condition which decides which UPDATE query to run. Rather than running a query, just store the id in an array - one array for "in stock" products, one for "out of stock" products.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.