通过PHP从SQL表到JSON - From SQL table to JSON through PHP

自从几天以来，我一直在努力解决这个问题，但我找不到正确的解决方案。

我想通过PHP:从这个数据库表中检索数据

数据库表

并且，再次使用php，具有类似于以下内容的JSON输出：

[{
"label": "2013-01-07",
"value": "4"
}, {
    "label": "2013-01-06",
    "value": "65"
}, {
    "label": "2013-01-05",
    "value": "96"
}]

我写了一个从表中检索信息的函数，但我无法将它们按正确的顺序排列，而且可能有更好的方法可以做到这一点。

function getUsersCountOnDate()
{
    $result = mysql_query("Select FROM_UNIXTIME(regtime, '%Y-%m-%d') as date, count(FROM_UNIXTIME(regtime, '%Y-%m-%d')) as count from users group by FROM_UNIXTIME(regtime, '%Y-%m-%d') order by FROM_UNIXTIME(regtime, '%Y-%m-%d') DESC");
    while($row = mysql_fetch_array($result)){
    $date[] = $row['date'];
    }
    $result = mysql_query("Select FROM_UNIXTIME(regtime, '%Y-%m-%d') as date, count(FROM_UNIXTIME(regtime, '%Y-%m-%d')) as count from users group by FROM_UNIXTIME(regtime, '%Y-%m-%d') order by FROM_UNIXTIME(regtime, '%Y-%m-%d') DESC");
    while($row = mysql_fetch_array($result)){
    $count[] = $row['count'];
    }
    $merged = array_merge($date, $count);
    return json_encode($merged);
}

我检索到的内容如下：["2016-03-18"，"2016-03-13"，"201603-11"，"2016年03月06日"，"2016.03-04"，"6"，"1"，"一"，"

有人能帮我吗？

首先，使用PDO或mysqli函数进行数据库查询

CCD_ 1将第一个阵列置于第二个阵列之前。例如：$array1 = array('red', 'yellow'); $array2 = array('blue', 'green'); $arrayMerged = array_merge($array1, $array2); $arrayMerged is now array(red,yellow,blue,green);

由于您正在执行的查询是相同的，您可以这样做：

$result = mysql_query("Select FROM_UNIXTIME(regtime, '%Y-%m-%d') as date, count(FROM_UNIXTIME(regtime, '%Y-%m-%d')) as count from users group by FROM_UNIXTIME(regtime, '%Y-%m-%d') order by FROM_UNIXTIME(regtime, '%Y-%m-%d') DESC");
    $i = 0;
    while($row = mysql_fetch_array($result)){
        $date[$i][label] = $row['date'];
        $date[$i][value] = $row['count'];
        $i++;
    }
$newArray = json_encode($date);

尝试

echo json_encode($merged);

有时光靠自己回来是不够的。例如，如果您想在ajax调用中接收json，则必须对其进行响应。

另外，您应该关注PDO，而不是使用简单的旧mysql函数。

请这样使用：

function getUsersCountOnDate()
{
    $result = mysql_query("Select FROM_UNIXTIME(regtime, '%Y-%m-%d') as date, count(FROM_UNIXTIME(regtime, '%Y-%m-%d')) as count from users group by FROM_UNIXTIME(regtime, '%Y-%m-%d') order by FROM_UNIXTIME(regtime, '%Y-%m-%d') DESC");
    while($row = mysql_fetch_array($result)){
    $dates[] = $row['date'];
    }
    $result = mysql_query("Select FROM_UNIXTIME(regtime, '%Y-%m-%d') as date, count(FROM_UNIXTIME(regtime, '%Y-%m-%d')) as count from users group by FROM_UNIXTIME(regtime, '%Y-%m-%d') order by FROM_UNIXTIME(regtime, '%Y-%m-%d') DESC");
    while($row = mysql_fetch_array($result)){
    $count[] = $row['count'];
    }
    $dates = array("2013-01-07", "2013-01-06", "2013-01-05");
   $count = array("4", "65", "96");
  $i = 0;
  foreach ($dates as $date)
 {
     $newArray[$i]['label'] = $date;
     $newArray[$i]['value'] = $count[$i];
     $i++;
 }
 ///print_r($newArray);
    return json_encode($newArray);
}