After rewind mysql query results, not working to re-use query

Question

I'm relatively new to mysql/php rewind. I am execuring a query, and after I mark the current data set and re-wind it, I need to run the same set to run shell scripts that take a really long time. I'm going to run the same script as a cron in a few minutes, so I can mark another set and know I'm fetching a different data set to run the slow shell scripts on. For some reason, the rewind isn't working, so it's not using the data set twice:

if(!($stmt = $mysqli->prepare("SELECT  node, model FROM Table WHERE vendor = 'Calix' AND model in ('C7','E7') AND  ((update_status NOT in ('u') OR (update_time IS NULL) OR ((DATEDIFF(NOW(),SW_ver_update_time)>14)) )) LIMIT 100"))) //AND ping_reply IS NULL AND software_version IS NULL 
{
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if(!$stmt->bind_result($ip, $model))
{
    echo "Binding results failed: (" . $stmt->errno . ") " . $stmt->error;
}

if(!$stmt->execute())
{
    $tempErr = "Error select node, model c7,e7 status: " . $stmt->error;
    printf($tempErr . "\n");    //show mysql execute error if exists  
    $err->logThis($tempErr);
}

$stmt1 = $mysqli1->prepare("UPDATE Table SET update_status = 'u' , update_time = UTC_TIMESTAMP() WHERE node = ?");

while($stmt->fetch()) {
    print "current ip: " . $ip . "\n";
    $stmt1->bind_param("s", $ip);
    $stmt1->execute(); //write time stamp and 'u' on ones 'in process of Updating'
}
//rewind db pointer
mysql_data_seek($stmt, 0);

//Circulate through 100 dslams fetched that we marked as in process.  
//This takes a long time to execuate and will be running this script concurrently in 5 minutes 
//so we need to know what we're working on so we don't fetch them again.
while($stmt->fetch()) {
  print "hello current ip: " . $ip . "\n";
  //will execute shell script here 
  //I never see hello print statement
}

I looked at mysql_data_seek but I don't see an example that uses fetch(). Can I not use fetch() after a rewind? What's the issue here? Thanks!

*Update: I tried

$stmt->data_seek(0);

But it's still not letting me re-use that query. If anyone has a suggestion of how to get rewind to work, or a way to get around it, like storing the query results so I can re-use them without re-running the query later, that's ok too.

Bill Karwin · Answer 1 · 2018-04-06T17:29:01.740

You can't use mysql_data_seek() with mysqli functions. Each PHP extension for MySQL is separate, and you can't use the functions from one extension with query results from another extension.

You'd want to use the equivalent function in the mysqli extension: mysqli_stmt::data_seek().

Re your comments:

You can use get_result() and then call fetch_all() on the result. This will return an array of rows, in which each row is an array of columns returned by the MySQL query.

if(!($stmt = $mysqli->prepare("SELECT  node, model FROM Table WHERE vendor = 'Calix' AND model in ('C7','E7') AND  ((update_status NOT in ('u') OR (update_time IS NULL) OR ((DATEDIFF(NOW(),SW_ver_update_time)>14)) )) LIMIT 100"))) //AND ping_reply IS NULL AND software_version IS NULL 
{
    error_log("Prepare failed: ({$mysqli->errno}) {$mysqli->error}");
    die();
}

if(!$stmt->execute())
{
    error_log("Error select node, model c7,e7 status: {$stmt->error}");
    die();
}

if (!($result = $stmt->get_result()))
{
    error_log("Error get result of select node, model c7,e7: {$stmt->error}");
    die();
}

$rows = $result->fetch_all(MYSQLI_ASSOC);

I also show use of error_log() which automatically logs to your http error log. If there's an error, I call die() so the code doesn't attempt to go on to the next step. In your script, you might structure it differently, like use return instead, if there's other code to run.

Or else you can fully embrace exceptions.

I never heard of that! Thanks! I tried $stmt->data_seek(0); before my second while stmt->fetch and still don't see the print statement inside that second loop. — Michele, Apr 05 '18 at 18:48
Frankly, I've never used data_seek(). I would just put the rows of the result set into a PHP array, then you can access any one you want. MySQL doesn't actually support cursors anyway, so if this works at all, it would require mysqli storing all the rows in memory anyway. — Bill Karwin, Apr 05 '18 at 18:51
I've never put the results into an array before. Wouldn't that be putting them in the array one row at a time? That wouldn't help me to get all the current results in a re-usable way for the current/full query. Do you have an example? — Michele, Apr 06 '18 at 12:48

After rewind mysql query results, not working to re-use query

1 Answers1