3

I have a php file that works up to a certain point but I need a bit of help on the loop for doing a MySQl Insert.

The following preforms a SELECT and then stores the Order ID for all records that have a order_status of 'S'. This works perfectly, printing each appropriate order ID.

I then push those affected ORder IDs to an array so that I can keep them stored for various functions.

    //Query for checking all records in order_status
    $orderCheck = "
            SELECT 
                order_id,
                order_status
            FROM order_status
    ";

    $result = mysqli_query($mysqlConn, $orderCheck);
    $order_ids = array();


    //loop results to gather order IDs and store them
    while ($row = mysqli_fetch_array($result))
        {
        $order_id = $row['order_id'];
        if ($row['order_status'] == "S")
            {
            array_push($order_ids, $order_id);
            }
        }

    //print_r($order_ids);
    //This does indeed print the correct order Ids

The following portion needs some work and I'm not quite sure what to do here. I'm iterating and foreach of those iterations I have an update, but i fear the syntax is incorrect on the update and I don't know if I'd need to do another array in here.

    //This is where I'm stuck//
    //Now I need to iterate through those orderIDs and update is_placement to 1, since it's a bit column, and udpate date_updated to curdate()

    for($i=0; $i<count($order_id); $i++) {

        $sql = "UPDATE order_status SET is_placement = '1' and date_updated = curdate() WHERE order_id = '$order_id'"; //I don't believe this syntax is correct    
    }

Basically, I need to preform a mysql update for each iteration of the previously stored $order_id array.

Any ideas or suggestions/guidance are much appreciated.

UPDATE:

output of the array:

[287] => 11605809
[288] => 11605817
[289] => 11605825
[290] => 11605863
[291] => 11605869
[292] => 11605870
[293] => 11605875
[294] => 12605471
[295] => 12605643
[296] => 12605715
[297] => 12605778
[298] => 12605817
Improve this question
10
  • 3
    WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put $_POST, $_GET or any user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. Commented Feb 28, 2018 at 19:11
  • 2
    Note: The object-oriented interface to mysqli is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete mysql_query interface. Before you get too invested in the procedural style it’s worth switching over. Example: $db = new mysqli(…) and $db->prepare("…") The procedural interface is an artifact from the PHP 4 era when mysqli API was introduced and should not be used in new code. Commented Feb 28, 2018 at 19:11
  • Can you share the output of the array? Commented Feb 28, 2018 at 19:12
  • @JayBlanchard I updated with the last few elements of that array Commented Feb 28, 2018 at 19:15
  • So the values to the right are indeed the correct order IDs Commented Feb 28, 2018 at 19:15

3 Answers 3

Reset to default
5

It makes little sense to ask MySQL for all the orders, and then select manually those with status S.

Better:

// Select only orders in status S.
$orderCheck = "SELECT order_id FROM order_status WHERE order_status = 'S';"

$result = mysqli_query($mysqlConn, $orderCheck);
$order_ids = array();

while ($row = mysqli_fetch_array($result)) {
    $order_ids[] = $row['order_id'];
}

Now you want to update those records. If you do it one by one, you do:

foreach ($order_ids as $order_id) {
    $sql = "UPDATE order_status SET is_placement = 1, date_updated = DATE(NOW()) WHERE order_id = '$order_id';";
    // EXECUTE
}

A faster way would be (I'm not adding quotes, assuming that order ids are numeric, but if they aren't things get a bit more complicated) to put all order ids together and use the IN syntax:

$bunchOfOrders = implode(',', $order_ids);

$singleQuery = "UPDATE order_status SET is_placement = 1, date_updated = DATE(NOW()) WHERE order_id IN ({$bunchOfOrders})";

But you could have done all this in MySQL too:

$orderUpdate = "UPDATE order_status SET is_placement = 1, date_updated = NOW() WHERE order_status = 'S';"

$result = mysqli_query($mysqlConn, $orderUpdate);

Since the above does not change the order status from S, you can then also run $orderCheck and retrieve the order-ids involved, if necessary.

To be sure that nothing untoward happens, I would also wrap the whole operation inside a transaction.

Another twist

What if we have to run a series of different modifications depending on some condition (mutually exclusive, of course)?

We can do this with a map, as long as we use deterministic functions and no modification changes the conditions below (so, you can't change order_status in any of the "recipes", or it would influence those that come after):

$todo = array(
    "order_status='S'"    => "is_placement = 1, date_updated = DATE(NOW())",
    "order_status='K'"    => "is_placement = 2, date_updated = DATE(NOW())",
    "order_status='L'"    => "owner='NOBODY'",
    "o_s='X' AND a=12"    => "note='A random condition'",
    // We can also supply a "what to do if none of the above":
    "DEFAULT"             => "note='THIS RECORD DOES NOT MATCH'",
);
$else = array();
foreach ($todo as $when => $then) {
    if ('DEFAULT' !== $when) {
        $else[] = "({$when})";
    } else {
        // "ELSE" means when no other condition is matched.
        // i.e. NOT when1 AND NOT when2 AND NOT when3...
        // which is equivalent to NOT ( when1 OR when2 ... )
        $when = "NOT (" . implode(' OR ', $else) . ")";
    }
    $sql = "UPDATE order_status SET {$then} WHERE {$when};";
    // Execute $sql.
}
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Question: The only reason I have the select that way is to have all order Ids in the table and then have an array for all with status 's' to do this update, and then I was going to eventually have another process that does a similar thing with all other status codes, other than S. You're saying it would be better to do this, and then just have another query for all status codes <> 'S' and use another array for those?
I think I've used every single one of these methods in one reason or another. Its nice you listed many of the possible ways to do an operation such as this.
@TomN. you can run one query WHERE order_status='S' and then another query with WHERE order_status='K' (or != 'S'). If this is on a multi-client system, you definitely want to shield everything within a transaction and/or lock the table, to avoid one of those orders changing status while you're updating them. You probably do not even need the arrays if all you're doing is changing information in a closed script (you do need the arrays if you want to, say, send one confirmation email to each order's representative, or things like that).
@LSerni ah thank you, that may be another way I look into going with this. I think if I have separate queries for different ORder IDs that might keep it cleaner
@TomN. I've added something that might be useful.
|
3

What you could use is a foreach() statement:

foreach($order_ids AS $order_id) {
    $sql = "UPDATE order_status SET is_placement = '1', date_updated = curdate() WHERE order_id = $order_id";
    // execute the query here
}

While you are using information from data contained in your table you should consider learning about prepared statements MySQLi

Improve this answer

9 Comments

Hey, who downvoted this! I like foreach more than for.
Thank you for this answer, so nix the iterator and use foreach instead?
Yes @TomN, it will be cleaner.
Thank you, so the only thing I missed is that $order_is should be $order_ids. I made an oversight on that 's'. Other than that, my syntax in using that variable is correct then?
In your original for() loop @TomN.?
|
1

I would change the initial query so you don't have to loop through anything more than once.

Try this:

    //Query for checking all records in order_status
    $orderCheck = "
        SELECT 
        order_id,
        order_status
        FROM order_status
        WHERE order_status = 'S'
    ";

$result = mysqli_query($mysqlConn, $orderCheck);
$order_ids = array();

//loop results to gather order IDs and store them
while ($row = mysqli_fetch_row($result))
{
    $sql = "UPDATE order_status SET is_placement = '1' and date_updated = curdate() WHERE order_id = '$row[0]'";
}
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.