0

I have a search result page with a simple form to filter order of results, I want the form to filter:

ORDER BY - ASC or DESC - and PER PAGE LIMIT in the MySQL query

Here is the code to filter the order by I am trying to use

$order_by = mysqli_real_escape_string($database,$_GET['order_method']);
$query = mysqli_query($database,"SELECT * FROM `products` 
order by `<?php if(empty($order_by)){echo "id";}else{echo "$order_by"; ?>` ASC");

It is not working ... I get errors in >php line 22 and this line is the line of code above

The idea is that if the user comes to the default page I obviously get no $order_by so in this case the order by will be the default

echo "id"

But if the customer uses the html form to filter the results and I get the "order_by" the mysql query order by changes to the value the customer sends using the html form in this case

echo "$order_by"

I am trying many ways to do this but no one seems to work, any ideas would help a lot

Kamikaza
  • 1
  • 1
  • 18
  • Why Complicating Your Query? – Nana Partykar Jul 20 '16 at 15:36
  • You can't use php tags inside of a php section. Take them out. . – aynber Jul 20 '16 at 15:38
  • 1
    Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared statement and parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) – RiggsFolly Jul 20 '16 at 15:38
  • So if I delete the and ?> tags it should work ? – Kamikaza Jul 20 '16 at 15:44

1 Answers1

1

Your code:

$order_by = mysqli_real_escape_string($database,$_GET['order_method']);
$query = mysqli_query($database,"SELECT * FROM `products` 
order by `<?php if(empty($order_by)){echo "id";}else{echo "$order_by"; ?>` ASC");

The problem: 

<?php if(empty($order_by)){echo "id";}else{echo "$order_by"; ?>

You are already in a PHP code block. You need to build up the query string using concatenation:

Try:

$order_by = mysqli_real_escape_string($database,$_GET['order_method']);

$sql = "SELECT * FROM `products` order by `";

if(empty($order_by)){
  $sql .= "id";
} else {
  $sql .= $order_by;
}

$sql .="` ASC";

// Now you can execute the query
$query = mysqli_query($database,$query);
RichardAtHome
  • 4,293
  • 4
  • 20
  • 29
  • does I need to obligated use the // Now you can execute the query $query = mysqli_query($database,$query); too ? – Kamikaza Jul 20 '16 at 17:02
  • It's to indicate that I'm building the query first, *then* executing the query. The original example attempts to build the query and execute it in the same step. – RichardAtHome Jul 21 '16 at 12:48