ORDER BY before second request

January 26, 2022

I have two tables. One is a list of the dishes a user wants to cook associated with their level of priority. The second one associate a dish with its recipe.

I would like to do the following : Print all the dishes the user wants to cook with their recipe ordered by the priority level of each dish.

I tried doing the following :

        $req = $pdo->prepare('SELECT dish, priority FROM dish_task WHERE (username = :username) ORDER BY priority');
    $req->execute(['username' => $username]);
    $dish = $req->fetchAll();

    if ($dish != null) {

        //get right format -> (dish_1, ..., dish_n)
        $listDish = ""; 

        foreach ($dish as $d) {

            $listDish = $listDish . "'". strval($f['dish']) . "'" . ",";

        }

        $listDish = substr($listDish, 0, -1);

        $req = $pdo->prepare('SELECT dish, recipe FROM dish_data WHERE dish IN (' . $listDish . ')');
        $req->execute();

        $taskList = $req->fetchAll();

        echo json_encode($taskList);

The problem is that doing the second request seems to cancel the ORDER BY I did previously… So I don’t know how I can manage to get what I want well ordered ?

>Solution :

Use a single query that joins the two tables.

$req = $pdo->prepare('
    SELECT dt.dish, dd.recipe
    FROM dish_task AS dt
    JOIN dish_data AS dd ON dt.dish = dd.dish
    WHERE (dt.username = :username) 
    ORDER BY dt.priority');
$req->execute(['username' => $username]);
$taskList = $req->fetchAll();
echo json_encode($taskList);