Building a mysqli query with order by and per page

问题:

The page display all results, now I want to filter results and how many results per page. To do this the visitor use a simple html GET form to select the filter.

Now I get the GET form and try to filter the results

<?php
$order_by = mysqli_real_escape_string($database,$_GET['order_by']);
$order = if(empty($order_by)){echo 'manufacturer';}else{echo '$order_by';
?>

OK now we get the filter and try to get results from MySQL like this

$set_order=mysqli_query($database,"SELECT * FROM `products` order by `$order` ASC");}

But I get error in the line:

   $order = if(empty($order_by)){echo 'manufacturer';}else{echo '$order_by';

Cannot find a way to do this ... Any idea?

回答1:

First, set a default value for order by.

$order = 'manufacturer';

Next, if the user has provided something else, replace the default value with that.

if (!empty($_GET['order_by'])) {
    $order = mysqli_real_escape_string($database, $_GET['order_by']);
}

Then you can use whatever it ends up being in your query.

$set_order = mysqli_query($database, "SELECT * FROM `products` order by `$order` ASC");

It is definitely good that you're using mysqli_real_escape_string here, but I would recommend checking the user input against a list of acceptable column names to mitigate the SQL injection risk.

回答2:

using single quotes around a variable will not work - but why not assign the variable and then echo it back?

$order = empty($order_by) ? 'manufacturer' : $order_by;
echo $order;

if however this is to be used in the sql query you do not need to echo it.

After a short 8r break for sleep you have an answer but I'll post this here too.

$order = empty($order_by) ? 'manufacturer' : $order_by;
$sql="select * from `products` order by `{$order}` asc;";

$set_order=mysqli_query( $database, $sql );
if( $set_order ){
    /* process recordset */
}

回答3:

try this:

if(empty($order_by)){$order = 'manufacturer';}else{$order = $order_by;}