使用PHP Order By对查询结果进行排序

The below code displays data from a table and then filters it depending on the results of two combo boxes. I am able to order the results by ID once the form is submitted, but not on initial load (where all are listed). I have tried $sql = "SELECT * FROM Places ORDER BY ID"; which works when the list loads but returns an error when the form is submitted. Hope that makes sense. Any ideas? Thanks!

// Default query
$sql = "SELECT * FROM Places";
// check if form was submitted
if (isset($_POST['area'])) {
    $connector = 'where';
    if ($_POST['area'] != 'All') { 
        $sql .= " where Area = '".$_POST['area']."' ORDER BY ID";
        $connector = 'and';
    }
    if ($_POST['theme'] != 'All') { 
        $sql .= " $connector Theme = '".$_POST['theme']."' OR Theme2 = '".$_POST['theme']."' 
        ORDER BY ID";
    }
}

Your ORDER BY ID clause must appear at the very end of your statement. If both $_POST['area'] and $_POST['theme'] are filled, you end up with a query like this:

SELECT ... WHERE Area = 'some area' ORDER BY ID AND Theme = 'some theme'

Add the ORDER BY bit as the last part of your query.

I think you are missing a default behavior statement. I.e. Your IF statement doesn't have an else clause. So you are checking for isset and if it is change the select query, but there is nothing to say IF ! isset SELECT query should be .... ORDER BY ID.

Also I would try echoing your SQL queries out each time you set / change a portion of it to understand exactly what is being sent to the DB.

Lastly I always check the mysql.general_log table for the last run queries to see what is actually happening at the DB end.

It looks like it is possible for $_POST['area'] != 'All' and $_POST['theme'] != 'All'. In that case you will be putting the ORDER BY clause in twice. That probably your problem.

So try this.

// Default query
$sql = "SELECT * FROM Places";
// check if form was submitted
if (isset($_POST['area'])) {
    $connector = 'where';
    if ($_POST['area'] != 'All') { 
        $sql .= " where Area = '".$_POST['area']."'";
        $connector = 'and';
    }
    if ($_POST['theme'] != 'All') { 
        $sql .= " $connector Theme = '".$_POST['theme']."' OR Theme2 = '".$_POST['theme'] . "'";
    }

    if ( $_POST['area'] != 'All' || $_POST['theme'] != 'All' ) {
        $sql .= ' ORDER BY ID';
    }
}

Thanks for all your help, I have solved the problem at the server end anyway so no need for code. Thanks for bringing attention to the security issues, I had these in the back of my mind but wasn't sure how bad it was! If I change the code to PDO would it help greatly? I have already reduced the privileges of the user to minimal. Thanks again.