我做错了什么?我正在尝试从数据库中取出最新的行(基于日期)并将其转换为 JSON。但是这个文件只是给我[]。
$date=date("Y-m-d",strtotime($_GET['date']));
$myquery = "SELECT * FROM test WHERE week='$date'";
$query = mysql_query($myquery);
if ( ! $myquery ) {
echo mysql_error();
die;
}
$data = array();
for ($x = 0; $x < mysql_num_rows($query); $x++) {
$data[] = mysql_fetch_assoc($query);
}
echo json_encode($data);
mysql_close();
?>
我的数据如下所示:
#id | week | firstname | last name | score#
###1 |2015-03-06 | Bill | Jones | 25
###2 |2015-03-13 | Bill | Jones | 45
###3 |2015-03-06 | Dave | Smith | 32
###4 |2015-03-13 | Dave | Smith | 52
###5 |2015-03-06 | Mary | Chu | 28
###6 |2015-03-13 | Mary | Chu | 45
因此,对于 2015 年 3 月 13 日,我希望将 Bill、Dave 和 Mary 的数据转换为 JSON。我不想要上周的数据,只想要最新的数据。
我可以在你的代码中看到的错误事情是条件if(! $myquery),它应该是if(!$query),或者你可以这样做:
$query = mysql_query($myquery) or die(mysql_error());
还要尝试检查 mysql_fetch_assoc($query) 是否返回任何内容或检查$date是否有值。
对于您的循环,我只想分享一下:
你也可以这样做:
for($data=array();$x=mysql_fetch_assoc($query);$data[]=$x);
=D
不确定这正是你想要的。
而且恐怕我显然不明白您到底想从 db 收到什么,这就是为什么我按分数添加顺序的原因:
<?php
$link = mysql_connect('localhost', 'root');
$db_selected = mysql_select_db('test', $link);
$date = mysql_escape_string('2015-03-13');
$myquery = "SELECT * FROM mostCurrentRow WHERE week='$date' ORDER BY score DESC";
$query = mysql_query($myquery);
if ( ! $myquery ) {
echo mysql_error();
die;
}
$data = array();
while ($row = mysql_fetch_assoc($query)) {
$data[] = $row;
}
echo '<pre>';
var_export($data);
echo json_encode($data);
mysql_close();
/*
will return:
array (
0 =>
array (
'id' => '4',
'week' => '2015-03-13',
'firstName' => 'Dave',
'lastName' => 'Smith',
'score' => '52',
),
1 =>
array (
'id' => '2',
'week' => '2015-03-13',
'firstName' => 'Bill',
'lastName' => 'Jones',
'score' => '45',
),
2 =>
array (
'id' => '6',
'week' => '2015-03-13',
'firstName' => 'Mary',
'lastName' => 'Chu',
'score' => '45',
),
)[{"id":"4","week":"2015-03-13","firstName":"Dave","lastName":"Smith","score":"52"},{"id":"2","week":"2015-03-13","firstName":"Bill","lastName":"Jones","score":"45"},{"id":"6","week":"2015-03-13","firstName":"Mary","lastName":"Chu","score":"45"}]
*/
更改查询
$myquery = "SELECT * FROM test WHERE week = STR_TO_DATE('$date', '%Y-%m-%d %H:%i:%s')";
查询$date在查询文本中,而不是连接到查询文本中。它应该看起来更像这样:
$myquery = "SELECT * FROM test WHERE week='".$date."' ORDER BY id";