php - Timeout Error when doing Large select from loop ← (PHP, MySQL)

I have a simple php code below

$sql_items = "SELECT id FROM item_masterfile"; /* this query has 7000 rows */

$result_items = mysqli_query($con,$sql_items);
    while ($row_items  = mysqli_fetch_row($result_items)) {
        $sql_qty = "SELECT qty FROM inventory WHERE id = ".$row_items[0];
        /* rest of the code here */
    }
}

this is working but due to a lot data my server cannot handle it and other queries took long to respond. how can I fix this? My target here is like batch select? to prevent clogged?

What I see in the process is a lot of select waiting to initiate.

How can I fix this?

Solution:

try with batches using limit and offset like below

$offset = $i * 5000; // $i is run batch number .. 1, 2, 3 etc in the foreach() loop.

if ($i == 0) {
  $offset = "";
}
$result_items="SELECT id FROM item_masterfile LIMIT 5000 OFFSET $offset;";

Solution:

The code in your question shows that there is one_to_one or one_to_many relation between tables, so using pagination and join statement would resolve the problem. Check below code hope to be helpful

$sql = "
     SELECT im.id, i.qty 
     FROM item_masterfile AS im 
     JOIN inventory AS i ON 
     im.id = i.item_masterfile_id 
     LIMIT $offset, $limit
";
$result_items = mysqli_query($con,$sql);

you can set $offset and $limit dynamically in your code and go on ...

Solution:

Instead of using the loop and providing id in there, you should collect all the ids in an array and then pass all of them to the query in one go using the IN operator.

Example: SELECT qty FROM inventory WHERE id IN (1,2,3,4,5). By doing this you can avoid loop and you code will not exit with timeout error.

OR

You can achieve the same using a subquery with your main query

Example: SELECT qty FROM inventory WHERE id IN (SELECT id FROM item_masterfile)

Solution:

Try with follow step:

1. get result of first query and get id values like (1,2,3,4,...)..
2. and out side where clause execute second query with WHERE condition IN clause

Like

$sql_items = "SELECT id FROM item_masterfile"; /* this query has 7000 rows */

$result_items = mysqli_query($con,$sql_items);
    while ($row_items  = mysqli_fetch_row($result_items)) {
        $ids[] = $row_items['id'];
    }

    $sql_qty = "SELECT qty FROM inventory WHERE id IN  ".$ids;
      /* rest of the code here */
}