CODEWITHSUNDEEP
phpmysqlloopsforeach
odd_duck
odd_duck

Reputation: 4111

Loop through MySql Table and output in Particular order with PHP

The below loops through a table in my MySql database and shows all the car records in a table cell that match. I know i can use the ORDER BY command in the MySql query to order by a particular column but how can i place a particular record on top and then list all the remaining matches?

For example i have a simple form before this where the user selects their favourite car ($_POST['user_car']) and the required spec ($_POST['user_spec']) with the $_POST method. So my query shows all records that match the spec selected, but i want the selected car to be at the very top of the list with the remaining to follow.

        $query_params = array( 
            ':spec' => $_POST['user_spec']
        );

        $query = " 
            SELECT 
                *
            FROM 
                table
            WHERE
                spec=:spec
        "; 

        try { 
            $stmt = DB::get()->prepare($query); 
            $stmt->execute($query_params); 
            $rows = $stmt->fetchAll(); 
        }  
        catch(PDOException $ex) {}

        foreach($rows as $row):

            $cell .= '<td>
                          <input type="checkbox" '.$check.' name="spec[]" value="'.$row['id'].'"/><span> '.$row['spec'].'
                      </td>';
        endforeach;

Upvotes: 0

Views: 292

Answers (1)

Waygood
Waygood

Reputation: 2693

The ORDER BY does not limit you to just column(S) you can put evaluated statements in there too.

        SELECT 
            *
        FROM 
            table
        WHERE
            spec=:spec
        ORDER BY car_name=:user_car DESC, car_name ASC

if users favourite car is 'mini', then the record with car_name of 'mini' will evaluate to 1, and the rest to 0, so it will appear first. The rest of the cars will then be ordered by their name.

Upvotes: 4

Related Questions