I don't know why it's not working... I'm trying to "order by" a created variable like this:
mysql_query("SELECT *,DATE_FORMAT(FROM_UNIXTIME(banfrom), '%d/%m/%Y') as time FROM ab_list WHERE time = '24/08/2013' ORDER BY banfrom DESC LIMIT 0,50");
Or,
$hr = date('d/m/Y');
mysql_query("SELECT *,DATE_FORMAT(FROM_UNIXTIME(banfrom), '%d/%m/%Y') as time FROM ab_list WHERE time = '$hr' ORDER BY banfrom DESC LIMIT 0,50");
Assuming the variable $hr is in the format %d/%m/%Y, you can use having instead of where to filter the records.
mysql_query("SELECT *,DATE_FORMAT(FROM_UNIXTIME(banfrom), '%d/%m/%Y') as time
FROM ab_list
HAVING time = '$hr'
ORDER BY banfrom DESC
LIMIT 0,50");
The reason to use HAVING and not WHERE in this case is because time is computed column.
It's because you are using an ALIAS in the WHERE clause that was created on the same level of the SELECT clause. There are two ways to make it working,
One, using the calculated column directly in the WHERE clause
WHERE DATE_FORMAT(FROM_UNIXTIME(banfrom), '%d/%m/%Y') = '24/08/2013'
Second, using a subquery
SELECT a.*
FROM (put your whole query here) a
WHERE a.time = '24/08/2013'
WHERE cannot use ALIAS because in the SQL Order of Operation the WHERE clause is executed before the SELECT clause. Here's the list of order of operations: