MySQL and procedural PHP: Update only previously selected records

PHP
1

I’m pretty rusty with MySQL these days. I need to select some rows from a table, send it to a third-party API via curl, and if that’s successful, then I need to update the selected rows.

It’s got to use procedural PHP.

Are MySQL transactions the way to go? Can someone recommend a really basic skeleton example of how this would be done? I’ve checked out a couple of tutorials but there’s something missing that I can’t quite get my mind around.

2

only if the failing of one query should void the whole update. judging from your description I wouldn’t say that transactions are desirable in your case.

3

It doesn’t matter if either of the queries fails. The first is a select, and if it fails then the whole script run will fail. The second is an update and needs to occur if I get the expected response from the third-party API. But it needs to only update the records that were originally selected, and nothing that may be inserted in the meantime.

Basically I just need to select rows based on a “Status” field, then update that field after the API has received the data. I’ve never had to do anything like this before. How would it normally be done?

4

Transactions are not going to help as the select will not fail and the update is the only step changing anything. A transaction only rolls back if a database update fails and a single update that fails doesn’t get applied anyway.

When you run the select you need to capture information about the records that are selected so that those and only those will be updated later. You don’t want that stored in the database as that will lock up the tables preventing other updates while this process is running.

If you use the captured information with the info retrieved from the API to run multiple updates then you might make these a transaction so that all or none get updated - if you can use a single update call then you will not need a transaction.

5

How would it normally be done?

Pretty much the same way as you wrote. Just select, do an API call, then update. It can be done using either procedural or object syntax. SQL queries are pretty basic too.

Can someone recommend a really basic skeleton example of how this would be done?

There is no basic example for the every task in the world. Every task is assembled from basic actions.

  1. Start from selecting data. There are plenty of tutorials.
  2. Go for API querying. Check the API documentation for this.
  3. Proceed to updating. There are plenty of tutorials as well.
  4. Assemble all the above code snippets into your skeleton app and feel the pride for the job.

You are welcome here to ask any particular questions you’ve got in the process.

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.