Geek asks and answers

Best mysql string update strategy using PHP?

Let's say I have 5 lines with fields id, catnameand order:

+----+----------+-----+  
| 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 the array, for example:

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

What is the best practice? To select each row and update the ordinal fields with the corresponding order in the array? Or create a new table containing an array of orders of all these categories, and use it to organize and link the two tables with the join operator, for example, using userid?

+3
source share
3 answers

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

$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';

id, CASE id CASE order.

+2

, -1.

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

, :

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


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

Entering order data in the rows you are about to select / select will in any case be faster than joining another table. As for the update, it doesn't really matter if you have good indexes. So, I would go with a single table approach.

0
source

More articles:


All Articles