php mysql按日期输出,php mysql按日期排序（最近）

I have now my code is fetching data in mobile application from SQL as first added showing first, I need to set it as showing last added as first in my android application. I have api code like below, in latest its showing as per my requirement but for cid and aid I need same as latest ( must show recent as first). My code is like below

Thanks

error_reporting(0);

mysql_query("SET NAMES 'utf8'");

if(isset($_GET['cid']))

{

if(isset($_GET['cat_id']))

{

$cat_id=$_GET['cat_id'];

$query="SELECT * FROM tbl_quotes

LEFT JOIN tbl_category ON tbl_quotes.cat_id= tbl_category.cid

where tbl_quotes.cat_id='".$cat_id."'";

}

else if(isset($_GET['latest']))

{

$limit=$_GET['latest'];

$query="SELECT * FROM tbl_quotes

LEFT JOIN tbl_category ON tbl_quotes.cat_id= tbl_category.cid

ORDER BY tbl_quotes.id DESC LIMIT $limit";

}

else

{

$query="SELECT cid,category_name,category_image FROM tbl_category";

}

$resouter = mysql_query($query);

$set = array();

$total_records = mysql_num_rows($resouter);

if($total_records >= 1){

while ($link = mysql_fetch_array($resouter, MYSQL_ASSOC)){

$set['Quotes'][] = $link;

}

}

/* output in format */

//echo $val= str_replace('\\/', '/', json_encode($set));

header( 'Content-Type: application/json; charset=utf-8' );

echo str_replace('\\/', '/',json_encode($set,JSON_UNESCAPED_UNICODE));

}

else{

if(isset($_GET['aid']))

{

$author_id=$_GET['aid'];

$qry="SELECT * FROM tbl_quotes

LEFT JOIN tbl_author ON tbl_quotes.aid= tbl_author.author_id

where tbl_quotes.aid='".$author_id."'";

}

else if(isset($_GET['latest']))

{

$limit=$_GET['latest'];

$qry="SELECT * FROM tbl_quotes

LEFT JOIN tbl_author ON tbl_quotes.aid= tbl_author.author_id

LEFT JOIN tbl_category ON tbl_quotes.cat_id= tbl_category.cid

ORDER BY tbl_quotes.id DESC LIMIT $limit";

}

else

{

$qry="SELECT author_id,author_name,author_image FROM tbl_author";

}

$resultouter = mysql_query($qry);

$author_set = array();

$total_records = mysql_num_rows($resultouter);

if($total_records >= 1){

while ($link = mysql_fetch_array($resultouter, MYSQL_ASSOC)){

$author_set['Quotes'][] = $link;

}

}

// echo $value= str_replace('\\/', '/', json_encode($author_set));

/* output in format */

header( 'Content-Type: application/json; charset=utf-8' );

echo str_replace('\\/', '/',json_encode($author_set,JSON_UNESCAPED_UNICODE));

}

?>

解决方案

When you use the SELECT statement to query data from a table, the result set is not sorted in any orders. To sort the result set, you use the ORDER BY

like that :-

ORDER BY column1 [ASC|DESC]

in your code :-

$query="SELECT * FROM tbl_quotes

LEFT JOIN tbl_category ON tbl_quotes.cat_id= tbl_category.cid

ORDER BY tbl_quotes.id DESC,date DESC LIMIT $limit";

if you column data type is not date or datetime us cast() like that :-

CAST(column_name AS DATETIME);

CAST(column_name AS DATE);