Jump to content

mySQL query: try for one minute.


ChenXiu
Go to solution Solved by Barand,

Recommended Posts

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 by ChenXiu
Link to comment
Share on other sites

  • ChenXiu changed the title to mySQL query: try for one minute.
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 by ChenXiu
Link to comment
Share on other sites

  • Solution

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 by Barand
  • Thanks 1
Link to comment
Share on other sites

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 by ChenXiu
Link to comment
Share on other sites

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
}

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.