Im having trouble with selecting data from database using like and order by-CodePudding

Im making a filter where you can select colors and select newest. It would filter them, but the order by doesn't work for some reason.

I tried it this way. It outputs the colors that match the database table, but it doesn't sort them by date.

$color_arr = ["red", "blue", "white"];

foreach($color_arr as $color) {
    $data = $conn->query("SELECT * FROM `prod_items` WHERE item_color LIKE '%$color%' ORDER BY `item_date` DESC");
    while ($row = $data->fetch()) {
        print_r($row);
    }
}

CodePudding user response：

Change the query.. Don't run sql query inside loop and also use IN instead of LIKE

$data = $conn->query("SELECT * FROM `prod_items` WHERE item_color IN ($color_arr) 
                        ORDER BY `item_date` DESC");
while ($row = $data->fetch()) {
    print_r($row);
}

CodePudding user response：

You can order by multiple fields, below is the MYSQL query:

SELECT * FROM `prod_items` WHERE item_color LIKE '%$color%' ORDER BY `item_date` DESC, `price` DESC"

This will first sort by item_date and then by price.