How Do I: Recycle $db->fetch_array($qry) ?

[DigitalCrowd]

Okay... I'm drawing a blank.

I do a query... example:

$qry=$db->query_read("SELECT field1,field2,field3 from vb_xyz WHERE field4='myvalue' and field8='Not_Processed'");

// SET FIELD8 TO PENDING TO STOP OVERLAPPING CRONS

while ($result=$db->fetch_array($qry)) {

    $db->query_write("UPDATE vb_xyz SET field8='Pending' WHERE field1='$result[field1]'");

}

// BEGIN PERFORMING ACTIONS, UPDATE DB FOR EACH FIELD ONCE COMPLETE

while ($result2=$db->fetch_array($qry)) {

    // action code here
    // this is what is not working, "$qry" seems to be "empty" or "finished"

}

So, my hang up (and maybe someone can suggest a better way to do this) is with this second "while" loop. I want to keep overlapping cron runs (example, running every minute, but one cron launch script lasts longer than 1 minute) from duplicating efforts.

In the first loop, I update a given field to "Pending" status and in the second loop, I actually take action. I can't do this with one loop because I want to make sure that the Pending status is applied to the entire selected $qry before running a process, so no future spawned scripts duplicate what a previously spawned script is already attempting to complete.

I know in PHP you can take an array and set the counter back to 0. But, how do I do this with the mysql query and the while clause using fetch_array?

If you know of a better way to do what I want to do, Im open for suggestions.

Thanks!

PS - Above code is for example purposes only and not copied from my actual working code.

[Paul M]

In the first loop add a line to store each row in an array, then loop round that array for your second set of processing.

[DigitalCrowd]

Ah Ha! Why did I not think of that. Guess when your looking in one direction, sometimes other directions are blind.

I'd still be open to reseting that counter for the $qry if anyone knows how to do that. But, in the mean time... Paul's idea would solve my issue.

[Code Monkey]

Or, better yet, you can use mysql_data_seek();

PHP Code:

$qry=$db->query_read("SELECT field1,field2,field3 from vb_xyz WHERE field4='myvalue' and field8='Not_Processed'");

// SET FIELD8 TO PENDING TO STOP OVERLAPPING CRONS

while ($result=$db->fetch_array($qry)) {

    $db->query_write("UPDATE vb_xyz SET field8='Pending' WHERE field1='$result[field1]'");

}



mysql_data_seek($qry, 0);




// BEGIN PERFORMING ACTIONS, UPDATE DB FOR EACH FIELD ONCE COMPLETE

while ($result2=$db->fetch_array($qry)) {

    // action code here
    // this is what is not working, "$qry" seems to be "empty" or "finished"

} 


[DigitalCrowd]

Thank you. That worked perfectly. I think I need a vacation, I should have known that answer.

[mihai11]

Originally Posted by Code Monkey

Or, better yet, you can use mysql_data_seek();

I also had trouble with this and your answer helped. Thank you.

[mihai11]

Looking thru the code, I found this function:

PHP Code:

function data_seek($queryresult, $index) 


in the file class_code.php.

This seems to be doing what the OP requested using the VB coding standards.

[Dismounted]

Yes, you should use the $db->data_seek() alias for the function, as this keeps the database layer in there, which is needed when vBulletin includes different layers to cater for different database types.