CODEWITHSUNDEEP
phpmysql
Dr_movi
Dr_movi

Reputation: 45

Best strategy to update mysql rows with PHP?

Say I've 5 rows with id, catname and order fields:

+----+----------+-----+  
| id | catname  |order|
+----+----------+-----+  
|  1 | cat1     |   2 |
|  2 | cat2     |   1 |
|  3 | cat3     |   3 | 
|  4 | cat4     |   5 |
|  5 | cat5     |   4 |
+----+----------+-----+

and I want to update the order of these 5 categories from an array, e.g.:

array(1 => 3, 2 => 4, 3 => 5, 4 => 1, 5 => 2)

What is the best practice? To select each row and update order fields with its corresponding order in array? Or to create a new table containing order array of all these categories and use it for ordering and linking the two tables with a join statement using for example userid?

Upvotes: 3

Views: 181

Answers (3)

cmbuckley
cmbuckley

Reputation: 42468

If you want to do it in one query, you can generate your SQL query as follows (assuming the keys are the existing order values):

$orders = array(
    1 => 3,
    2 => 4,
    3 => 5,
    4 => 1,
    5 => 2,
);

$sql = 'UPDATE category SET order = CASE order ';

foreach ($orders as $old => $new) {
    $sql .= "WHEN $old THEN $new ";
}

$sql .= 'END';

If the keys are the id entries, use CASE id instead of CASE order.

Upvotes: 2

mistapink
mistapink

Reputation: 1956

You may could assign them negative values with the new order and then multiply the negative values with -1.

array(1->-3,2->-4,3->-3,4->-1,5->-2)

But you will have to update the rows each themselves by looping through your array:

update yourTable set order=-3 where order=1;

And later use

update yourTable set order=-1*order where order < 0;

Upvotes: 1

Rijk
Rijk

Reputation: 11301

Putting the order data in the rows you're going to be selecting/fetching anyway is going to be faster than a join with a different table for sure. In terms of updating, it doesn't really matter - provided you have good indexes in place. So, I would go with the one table approach.

Upvotes: 0

Related Questions