PHP: fetch MySQL records one at a time

I have made a PHP script that creates logo's from names, that come from a MySQL database with thousands of records.

The creation of a logo can take up to a few seconds per record, so the whole process takes a couple of days. The script I have made now, gets all data beforehand, and then starts making the logo's. But while the script is running, the data in records that aren't processed yet, could CHANGE!

How can I iterate through a resultset, while still getting the latest data when the next record is processed?

This is what I use now:

        $sql = "SELECT * FROM names";
        $mysqli = new mysqli($server, $user, $password, $database);
        if ($mysqli->multi_query($sql)) {
          do {
            if ($result = $mysqli->store_result()) {
              while ($record = mysqli_fetch_assoc($result)) {
                $records[] = $record
              }
              $result->close();
            } 
          } while ($this->DB->next_result());
        }
    开发者_高级运维    foreach($records as $record) {
          //create the logo from $record['name']
        }

Assuming that you have unique ID's for each entry in the database and you know for sure that this script can run "forever", something like this should work:

$result = mysql_query('SELECT `id` FROM `logos`');
$ids = array();

while($id = mysql_fetch_row($result)){
    $ids[] = $id[0];
}

// we now have all the ids stored in an array which we'll loop through.

foreach($ids as $id){
    $result = mysql_query('SELECT * FROM `logos` WHERE `id`=' . $id);
    $row = mysql_fetch_assoc();

    // process
}

What does this do then? Well, we start by fetching all the ID's that currently reside in the database. We store them in a variable, loop through it and get the most recent data for each of the rows.

As others have said, you can't iterate over a result set while having the most recent data at the same time. This is the alternative.

I would probably fetch the rows one by one starting at the lowest id and then just increasing that id by one until reaching the maximum id. If there isnt a record with that id, just jump to the next one.

So what happens when the data has been converted into an image? The data row stays in the table?

not that it matter, you can just add LIMIT 1 to the end of your SELECT and you will just get that the first entry that matches your request.

You could compromise by getting, say, 30 rows at a time. You could implement some sort of mechanism that adds any updated rows to a redo list.

Edit:

How often does the table get updated? Half of the resultset changing is different to 30ish changing. Is this a one-off operation? If this is done often, then you'd be going through the entire resultset over and over, looking for changes. Also, what happens if you come to row 987, create a logo, and when you've moved on to row 1032, someone changes row 987?

If the update activity is minimal, I'd try getting the whole resultset, creating a update trigger event to log changes, doing all the logos, then going back and updating logos based on the logging. (This might be overkill.)

You can't. You'll need to manually step trhough your rows one at a time, querying for a single row every time. When you issue a SELECT query, MySQL buffers the results. So when data changes between issuing the query and fetching the rows, you get the old buffered data.

If you don't want to run a query for every row, you could simply do it in batches. E.g, query 100 records and process them. Query the next 100, etcetera.