Home PHP returns SQL error when appending "AND" expression after "ORDER BY" expression in SQL query

Questions

PHP returns SQL error when appending "AND" expression after "ORDER BY" expression in SQL query

May 5, 2023

In documentation I can see ORDER BY expression is usually put at the end of the query, however, in my case, I want to order products by price first and then choose brands, as a result, I would add " AND product_brand IN" after ORDER BY product_price. Then PHP throw an error, i’m pretty sure it is because I put AND express after ORDER BY expression, how can I get over this?

if(isset($_POST['action'])) {
    $keyword =  $_POST['keyword'];
    $output = "";
    $query = "SELECT * FROM $keyword WHERE product_rating = '4.5'";
    if (isset($_POST['general'])) {
        $general_filter = implode("','", $_POST['general']);
        switch ($general_filter) {
            case 'all':
                $query .= "";
                break;
            case 'low_to_high':
                $query .= "ORDER BY product_new_price ASC";
                break;
            case 'high_to_low':
                $query .= "ORDER BY product_new_price DESC";
                break;
            case 'name_ascending':
                $query .= "ORDER BY product_title ASC";
                break;
            case 'name_descending':
                $query .= "ORDER BY product_title DESC";
                break;
            case 'newest':
                $query .= "ORDER BY product_date DESC";
                    break;
            case 'top_seller':
                $query .= "ORDER BY product_sold ASC";
                break;
            case 'available':
                $query .= "AND product_status = '1'";
                break;
        }
    }
    if (isset($_POST['brand'])) {
        $brand_filter = implode(",", $_POST['brand']);
        if ($brand_filter == 'all') {
            $query .= "";
        }
        else {
            $query .= " AND product_brand IN ('$brand_filter')";
        }
    }

I search for hours on internet and still stuck.

>Solution :

SQL has a specific order of commands. It is not allowed to have the orderBy before a WHERE clause.
You can solve this by moving the second if-statement up:

    $query = "SELECT * FROM $keyword WHERE product_rating = '4.5'";
        if (isset($_POST['brand'])) {
                $brand_filter = implode(",", $_POST['brand']);
                if ($brand_filter == 'all') {
                    $query .= "";
                }
                else {
                    $query .= " AND product_brand IN ('$brand_filter')";
                }
        }
        if (isset($_POST['general'])) {
            $general_filter = implode("','", $_POST['general']);
            switch ($general_filter) {
                case 'all':
                    $query .= "";
                    break;
                case 'low_to_high':
                    $query .= "ORDER BY product_new_price ASC";
                    break;
                case 'high_to_low':
                    $query .= "ORDER BY product_new_price DESC";
                    break;
                case 'name_ascending':
                    $query .= "ORDER BY product_title ASC";
                    break;
                case 'name_descending':
                    $query .= "ORDER BY product_title DESC";
                    break;
                case 'newest':
                    $query .= "ORDER BY product_date DESC";
                        break;
                case 'top_seller':
                    $query .= "ORDER BY product_sold ASC";
                    break;
                case 'available':
                    $query .= "AND product_status = '1'";
                    break;
            }
        }