php - 30 个唯一变量的 SQL 查询

Question

我正在提取统计过去 30 天每天的用户注册数量的数据。此数据正在转换为 Json 以用于 googlechart。

我想知道是否有更有效的方法来提取我正在寻找的数据，而不是 30 个单独的查询。

这是我的数据流和工作代码。

SQL 查询从一天(今天，昨天，一直到 30 天前)的# of registrations -> PHP Variable -> Json -> GoogleChart

SQL查询

$mysqli=mysqli_connect("localhost","root","","db");
if (mysqli_connect_errno()){echo "Failed to connect to MySQL: " . mysqli_connect_error();}

$query_a = "SELECT * FROM wp_users WHERE user_registered BETWEEN DATE_SUB(NOW(), INTERVAL 1 day) AND NOW()";
$result_a = mysqli_query($mysqli,$query_a);
$row_a = mysqli_num_rows($result_a);

$query_b = "SELECT * FROM wp_users WHERE user_registered BETWEEN DATE_SUB(NOW(), INTERVAL 2 day) AND DATE_SUB(NOW(), INTERVAL 1 day)";
$result_b = mysqli_query($mysqli,$query_a);
$row_b = mysqli_num_rows($result_b);
mysqli_close($mysqli);  

JSON

$col1=array();
$col1["id"]="";
$col1["label"]="";
$col1["pattern"]="";
$col1["type"]="string";
//print_r($col1);
$col2=array();
$col2["id"]="";
$col2["label"]="Registrations";
$col2["pattern"]="";
$col2["type"]="number";
//print_r($col2);
$cols = array($col1,$col2);
//print_r($cols);  
  
// Today
$cell0["v"]=date('M-d');
$cell1["v"]=$row_a;
$row0["c"]=array($cell0,$cell1);

//Yesterday
$cell0["v"]=date('M-d', strtotime('-1 day'));
$cell1["v"]=1;
$row1["c"]=array($cell0,$cell1);  
  
$rows=array();
array_push($rows,$row4);
array_push($rows,$row3);  
  
$data=array("cols"=>$cols,"rows"=>$rows);
//print_r($data);
echo json_encode($data);  

已修复 - 更新了工作 SQL 查询

$query = "SELECT DISTINCT date(user_registered) as date, count(*) as cnt 
FROM wp_users 
WHERE date(user_registered) BETWEEN DATE_SUB(date(NOW()), INTERVAL 2 month) AND date(NOW())
GROUP BY date(user_registered)
";

$result = mysqli_query($mysqli,$query) or die;
$data = array();
while($row = mysqli_fetch_assoc($result))
{ $data[] = $row; }

print_r($data)
?>

Answer 1

我想你想要一个单一的查询，像这样的东西应该可以工作:

SELECT date(user_registered) as date, count(*) as cnt
FROM wp_users
WHERE date(user_registered) BETWEEN DATE_SUB(date(NOW()), INTERVAL 30 day) AND date(NOW())
GROUP BY date(user_registered)