mySQL query: try for one minute.

[ChenXiu]

Data is supposed to be instantly inserted into mySQL table from a 3rd party.
However, sometimes it takes up to a minute or so....
Is this "keep checking for up to a minute" loop okay? If not, how can I make it better?

$count = 0;
$query = $db->query("select data from mytable where sku = 'XYZ123' limit 1")->fetch_assoc();

// if no instant result, keep trying for up to one minute:
while (!$query) {
$query = $db->query("select data from mytable where sku = 'XYZ123' limit 1")->fetch_assoc();
sleep(1); // wait for one second before trying again
$count++;
if($count > 60) { break; } // Give up after one minute.
}

if (!$query) {
echo 'Forget it, no data will be provided!'; // Give up.
} else {
echo 'Success! Data is ' . $query["data"]; // Success!
}

Thank you.

Edited September 8, 2021 by ChenXiu

[requinix]

57 minutes ago, ChenXiu said:

However, sometimes it takes up to a minute or so....

That is a problem. Do not ignore the problem. Find out why it is taking so long and fix it.

[ChenXiu]

52 minutes ago, requinix said:

That is a problem. Do not ignore the problem. Find out why it is taking so long and fix it.

No, you misunderstood. The 3rd party data provider can take up to one minute depending on how many customers are simultaneously using their service.
The 3rd party provider produces results in about 1/4 second if 10 people are using their service. If 200 people are using their service, you're in a queue.
When they provide the data is when they provide the data.

Is my mySQL code okay?

Edited September 8, 2021 by ChenXiu

[Barand]

Looks OK. I'd use a "do" loop i this case and count the records found.

    $start = time();
    $query = $db->prepare("select count(*) from mytable where sku = ?");
    do {
        sleep(1);
        $query->execute(['XYZ123']);
        $recs = $query->fetchColumn();
    } while ($recs == 0 && time() < $start + 60);

    echo $recs == 0 ? "Try again later" : "$recs found";

 

Edited September 8, 2021 by Barand

[ChenXiu]

1 hour ago, Barand said:

I'd use a "do" loop

Thank you! I've never used a "do" loop before; this will be my first one.
Tomorrow morning I'm setting aside time to learn about them, pros and cons, and when to use.
It looks elegant!

[Barand]

A "while()" loop executes code only if the condition is true.

A "do...while()" loop executes at least once before testing the condition. That is what you want in this case.

[ChenXiu]

Oooooh! That is fantastic! Like I've said lots before, I wish I had learned PHP from the ground up. This sounds like something every brand new PHP person already knows. So, yes, a "do loop" is exactly what I want.

Tangentially, when I was "borrowing code" from an example I read somewhere for something else, they had a "try/catch" thing going on. I immediately got rid of the "try" and "catch" thinking "I don't need those stupid things whatever they are..."
So, after I get comfortable using "do loops" I should brush up on what try/catch means and does 😃
As well as what 'break' 'continue' and 'next' actually mean (in my code above, using "break" was just a lucky guess 😊 )

p.s. I should have mentioned before... your use of time() in the code is fantastic -- that never would have occured to me. I thought a $counter + sleep() was the only way to do this sort of thing. Right now I can already think of a dozen places where I can use time() in a loop.

Edited September 9, 2021 by ChenXiu

[Barand]

6 hours ago, ChenXiu said:

I should brush up on what try/catch means and does

Typically I only use try/catch when using database functions (as they throw execeptions and not just set error codes). I turn on exceptions when I make the db connection. Having done that, I mostly leave the automatic reporting of the exceptions to PHP so no further checking required - if there's an error it gets reported. This done by

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);           // PDO

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);                  // MYSQLI

Two situations where I resort to try/catch are

• transactions
• duplcate keys

Transactions

Say I were to write an order record then several order item records to another table. I need to be sure that all were successfully completed. If the last order item fails, I want to undo all of the inserts

try {
    begin transaction
    write order record
    while there are order items
        write order item record
    end while
    commit                             // all ok so save the data
}
catch (exception) {
    rollback                           // oops! undo all transaction inserts
    throw exception                    // rethrow the exception so PHP reports an error occured
}

Duplcates

I don't want two users to have the same username so I attempt the insert anyway and only worry about it if it turned out to be a duplcate (requires username column to be defined as unique)

try {
   insert user record
}
catch (exception) {
   if exception error is duplicate key
      inform user
   else
      throw exception                           // nothing I can do so php reports error
   end if
}