Sky Sky - 7 months ago 11
PHP Question

php + mysql two table sorting

+------------------------------------+
| uid | user_name | food |
+-----------------------------------+
| 12345 | Joe | 3,1,2 |
| 12346 | John | 2,1 |
| 12347 | David | 30,2,1 |
| 12355 | Peter | 50,1 |
+-----------------------------------+


+-------------------------+
| food_id | food_name |
+------------------------+
| 1 | apple |
| 2 | orange |
| 3 | banana |
| 50 | pear |
+------------------------+

$user_sql = "SELECT `uid`,`user_name`,`food` FROM `user`";
$stmt = $pdo->prepare($user_sql);
$stmt->execute();
$user_data = $stmt->fetchAll(PDO::FETCH_ASSOC);

$food_id = $user_data["food"]; // will be get eg: 50,1,3
$food_sql = "SELECT * FROM `food` WHERE `food_id` in ($food_id)";
$stmt = $pdo->prepare($food_sql);
$stmt->execute();
$food_data = $stmt->fetchAll(PDO::FETCH_ASSOC);


I will be foreach $food_data.

That's OK, can show it.

But this array($food_data) is sorting order by
food
.
food_id
, I need sort same to
user
.
food


EG show:

Joe - apple,orange,banana (default sort by food_id 1,2,3)

John - apple,orange (default sort by food_id 1,2)

David - apple,orange,pear (default sort by food_id 1,2,50)

Peter - apple,pear (default sort by food_id 1,50)

but I need change it.

How to change to

Joe - banana,apple,orange (sort same to
user
.
food
field value 3,1,2)

John - orange,apple (sort same to
user
.
food
field value 2,1)

David - pear,orange,apple (sort same to
user
.
food
field value 30,2,1)

Peter - pear,apple (sort same to
user
.
food
field value 50,1)

I had try one by one select sql through the smarty template plugin.

But that's so wasted resources. I can do something in php or mysql?



Answer

You can use code like

SELECT * FROM `food` WHERE `food_id` in (3,1,2) ORDER BY FIELD(food_id,3,1,2) 

to achieve "Joe - banana,apple,orange (sort same to user.food field value 3,1,2)" same for others... you can use ORDER BY FIELD to order whatever you want

Comments