MYSQL order by how to use other name for column?

Question

This is a segment of my PHP file, the database is MySQLi:

$order = $_GET["order"];

mysqli_query($connect,"SELECT * FROM table WHERE activ = '1' ORDER BY $order LIMIT $start, $end);

Now if I want to sort by newest, my url would look like this:

mysite.com/index.php?order=date DESC

But this doesn't look good.

How can I use another name for date DESC, for example 'newest'?

mysite.com/index.php?order=newest

Thank you in advance!!

Best regards.

Answer 1

$order = $_GET["order"];
if ( $order == "newest" )
{
    $order = "DESC";
}
else
{
    $order = "ASC";
}

This would reassign the value of order depending on its initial value.

This would also give you a default value of ASC for any sort order that does not match newest exactly.

Note that if you end up doing something else, your posted code is very prone to SQL injection; you should be sure to sanitize all your strings.

Answer 2

i think there's two ways of getting your database information here, but I had to clarify some of your arguments:

1). the value that you're getting from $_GET['order'] must be asc or desc.

2). is the your query is wrong, there must be an column name after your order. it should be look like this order by column_name $order.

3). is you might be mispelled your activ as active. Anyway, i'm following your column name in my query below.

4). I don't understand what's the purpose of $end.

5). Anyway, here's an easy way of getting the information in your DB.

1. if every time the user has to insert/add some data into the table, you could simple order it by IDs.

   $query = mysqli_query($connnect, "select * from table where activ = '1' order by id $order limit by $start");

2. As your get information above. I assume that you are getting the order date. So, if the conditions really lies in the date, your query should be exactly look like this. Note: I write the date lower case so pay attention in your date column name if it is DATE or Date or date or what.

   $query = mysqli_query($connect, "select * from table where activ = '1' order by date $order limit $start");

I just mean to help. Sorry don't flame me.

Answer 3

The query string needs to be enclosed in "".

Does the segment of code you provided work? Some things to check if it does not:

1) Make sure that your $connect information is correct.

2) Make sure that table is the name of your table.

3) Make sure that activ is a column in your table. And the values stored there are of Boolean data type.

4) Make sure that date is a column in your table and the date type you are using is correctly stored in the table.

5) Note that LIMIT is expecting an argument of number of records to return from some offset, based on the variable names you are using $start and $end, I am assuming you are using LIMIT incorrectly. More appropriately $number_of_records and $offset.

6) Check the error logs to see if there is an error being logged either by the database server or the domain server itself.

Now if that query works...

Matt Clark handled the rest of the question about ASC and DESC below, see here. But obviously you can't just stick ASC or DESC without specifying a key to sort by, such as the column date. So the query string would look like this:

"SELECT * FROM table WHERE activ = '1' ORDER BY date $order LIMIT $start, $end"

Another option if your queries get more complex and are not only related to sorting by date, then you might resort to providing multiple variables with your URL.

mysite.com/index.php?sortby=date&order=newest

"SELECT * FROM table WHERE activ = '1' ORDER BY $sortby $order LIMIT $start, $end"

You can learn more about multiple variables in a URL from this post: Passing multiple variables to another page in url and others like it.