Updating mysql table in php, and storing ID for each affected row

Question

I'm trying to use the results of a MySQL select in a php script and make some updates in the same table.

For the following query, I need to look at each record and say "if order_status = 'S', set is_placement to 1 (this is a bit) and set date_updated to curdate()"

I can do that with an update, but the trick is I need to store the order_id of those with the status of 'S' in an array to use in another query.

I'm not sure how I would loop and store those in an array for that.

Here's the select:

$orderCheck = "
    SELECT 
        order_id,
        order_status
    FROM order_status
 ";

EDIT (based on answers below): So I can make an array of order IDs like so:

$result = mysqli_query($connection,$orderCheck);
while ($row = mysqli_fetch_array($result))
{
    $array = $row['order_id'}
    if($row['order_status'] == 'S'){
        Store ORder IDs here
    }
}

But with that array and stored order IDs, I need to update the records for each ID.

How can I preform that update and store the necessary IDs as well?

We are always glad to help and support new coders but ***you need to help yourself first. :-)*** After [**doing more research**](https://meta.stackoverflow.com/q/261592/1011527) if you have a problem **post what you've tried** with a **clear explanation of what isn't working** and provide [a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). Read [How to Ask](http://stackoverflow.com/help/how-to-ask) a good question. Be sure to [take the tour](http://stackoverflow.com/tour) and read [this](https://meta.stackoverflow.com/q/347937/1011527). — Jay Blanchard, Feb 28 '18 at 17:04
I can see how the array portion would be a duplicate, but I'm mostly wondering how I would update based on that — Geoff_S, Feb 28 '18 at 17:07
Update based on the array? That is another question entirely. — Jay Blanchard, Feb 28 '18 at 17:09
My last line addresses "Preform the update AND store the necessary IDs as well". The main issue is preforming the table update based on the IDs, and secondarily storing the IDs in an array for use elsewhere. — Geoff_S, Feb 28 '18 at 17:10
"For the following query, I need to look at each record and say "if order_status = 'S', set is_placement to 1 (this is a bit) and set date_updated to curdate()" I can do that with an update, but the trick is I need to store the order_id of those with the status of 'S' in an array to use in another query." — Geoff_S, Feb 28 '18 at 17:11
I'm not trying to argue at all, I just thought I had made it clear that I need to preform an update on the table — Geoff_S, Feb 28 '18 at 17:11
OK - I'll give you that. But you show no effort in solving it yourself, which makes the question quite broad. Too broad, in fact. — Jay Blanchard, Feb 28 '18 at 17:12
Fair enough, I updated my question slightly. I realize I should have included an array or at least pseudo code of it with a potential mysql example. I guess I should have been more clear in what I was really looking for — Geoff_S, Feb 28 '18 at 17:25
`if($row[order_status']` there's a syntax error here. It's missing a quote. — Funk Forty Niner, Feb 28 '18 at 17:25
thanks @FunkFortyNiner, I fixed it but that was more of a 'pseudo code' approach so I overlooked it originally — Geoff_S, Feb 28 '18 at 17:26
Now that you have an array, loop through the array and run your update during each loop iteration. — Jay Blanchard, Feb 28 '18 at 17:27
Gotcha. So create an iterator at 0, loop on that iterator and foreach, preform an update — Geoff_S, Feb 28 '18 at 17:28
welcome @TomN. sometimes people think it's part of the actual code. — Funk Forty Niner, Feb 28 '18 at 17:29

score 0 · Answer 1 · answered Feb 28 '18 at 16:56

0

$orderCheck = "SELECT 
        order_id,order_status FROM jfi_sales.order_status ";
   $result = mysql_query($orderCheck );
   $ids= array();

  while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
     if($row['order_status'] == 'S'){
        $ids[]= $row['order_id'];
     }
  }
print_r($ids);

Now $ids is your array of order id whose order status is 'S'.Try this it will work.

answered Feb 28 '18 at 16:56

Ms.KV

214
2
10

Turn the tide against teaching/propagating sloppy and dangerous coding practices. If you post an answer without prepared statements [you may want to consider this before posting](http://meta.stackoverflow.com/q/344703/). Additionally [a more valuable answer comes from showing the OP the right method](https://meta.stackoverflow.com/a/290789/1011527). – Jay Blanchard Feb 28 '18 at 17:03
***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Feb 28 '18 at 17:03
What? That makes no sense....use what code? – Jay Blanchard Feb 28 '18 at 17:10
If your php version is >=7 then use this code- `$orderCheck = "SELECT order_id,order_status FROM jfi_sales.order_status "; $result = mysqli_query($con,$orderCheck); $ids= array(); while ($row = mysqli_fetch_array($result, MYSQL_NUM)) { if($row['order_status'] == 'S'){ $ids[]= $row['order_id']; } } print_r($ids); ` – Ms.KV Feb 28 '18 at 17:13
Please do not dump code in comments. It is unreadable and leads to mistakes. Edit your original post to add any updates. BTW - version has nothing to do with using the `mysql_*` API. It is an insecure API and should no longer even be mentioned. – Jay Blanchard Feb 28 '18 at 17:14

Zoltán Király · Accepted Answer · 2018-02-28T17:11:52.433

0

The following will store the data the way you needed:

$result = mysql_query($orderCheck);
$order_ids = array();

while ($row = mysql_fetch_array($result))
    {
    $order_id = $row['order_id'];
    if ($row['order_status'] == "S")
        {
        array_push($order_ids, $order_id);
        }
    }

edited Feb 28 '18 at 17:11

answered Feb 28 '18 at 16:59

Zoltán Király

259
1
12

So this stores my ID for those with a status of 'S' but how exactly can I use that to update the is_placement bit and the date_updated column to curdate() – Geoff_S Feb 28 '18 at 17:00
1

***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Feb 28 '18 at 17:02
You need to iterate through the $order_ids array and call an update for each row using the order_id stored in that row. – Zoltán Király Feb 28 '18 at 17:03
1

Turn the tide against teaching/propagating sloppy and dangerous coding practices. If you post an answer without prepared statements [you may want to consider this before posting](http://meta.stackoverflow.com/q/344703/). Additionally [a more valuable answer comes from showing the OP the right method](https://meta.stackoverflow.com/a/290789/1011527). – Jay Blanchard Feb 28 '18 at 17:03
1

`$mysql_fetch_array` - there shouldn't be a `$` sign here. – Funk Forty Niner Feb 28 '18 at 17:11

Updating mysql table in php, and storing ID for each affected row

2 Answers2