I want to to fetch 100,000 records by an SQL query using MySQL XAMPP and save in an array, but it doesn’t even fetch 16,000 and halt or shows

Fatal error: Maximum execution time of 30 seconds exceeded in C:xampphtdocsMapRoutingRouting.php

Yes, I can set the maximum execution time, but I want the result in less time, like in MySQL when a person clicks on a table, it gives a result, e.g. showing rows 0 – 24 (16037 total, Query took 0.0032 seconds.)

$link = mysqli_connect("localhost", "root", "", "example");

if (mysqli_connect_error()) {
    echo "error";
}

$rows = array();
$count = 0;

for ($J=0; $J <= 3; $J++) {
    $count = $count + 4000;

    for ($i = 1; $i <= $count; $i++) {
        $offset_var = $i - 1;
        $query = "select id from shipment Limit 1 OFFSET " . $offset_var;
        $result = mysqli_query($link, $query);

        while ($row = mysqli_fetch_assoc($result)) {
            $rows[] = $row;
        }
    }
}

mysqli_close($connection);
echo "<pre>";
print_r($rows);
echo "</pre>";

Answer

I am not sure what your exact requirement of this code is, but your query efficiency seems very low.

You’re running too many queries that fetch only one row per query.

    select id from shipment Limit 1 OFFSET 0
    select id from shipment Limit 1 OFFSET 1
    select id from shipment Limit 1 OFFSET 2
    select id from shipment Limit 1 OFFSET 3
    select id from shipment Limit 1 OFFSET 4
    select id from shipment Limit 1 OFFSET 5
    ...

Try this and see for yourself:

#$link = mysqli_connect("localhost", "root", "", "example");
#if (mysqli_connect_error()) {
#   echo "error";
#}
$rows = array();
$count = 0;
for($J=0; $J<=3; $J++) {
    $count = $count + 10;
    for($i=1; $i<=$count; $i++) {
         $offset_var = $i-1;
         $query = "select id from shipment Limit 1 OFFSET " . $offset_var;
         #$result = mysqli_query($link, $query);
         #while ($row = mysqli_fetch_assoc($result)) {
         #    $rows[] = $row;
         #}
         echo $query . "n";
    }
}
#mysqli_close($connection);
#echo "<pre>";print_r($rows);echo "</pre>";

If you want to run a query to get 4000 results per page/query?

Then following code would do that in 26 queries

$link = mysqli_connect("localhost", "root", "", "example");
if (mysqli_connect_error()) {
    echo "error";
}
$rows = array();
$page_size = 4000;
$total_records = 100000;

$count = ceil($total_records/$page_size);
for($i=0; $i<=$count; $i++) {
   $offset_var = $i * $page_size;
   $query = "select id from shipment Limit " . $page_size . " OFFSET " . $offset_var;
   $result = mysqli_query($link, $query);
   while ($row = mysqli_fetch_assoc($result)) {
       $rows[] = $row;
   }
}
mysqli_close($connection);
echo "<pre>";
print_r($rows);
echo "</pre>";