Clever Geek Handbook

SQL Query for 30 unique variables

I am pulling out data that counts the number of user registrations for each of the last 30 days. This data is converted to Json for googlechart.

I'm wondering if there is a more efficient way to get the data I'm looking for rather than 30 separate queries.

Here's my data flow and working code.

SQL Query Per # registrations from day (today, yesterday, total up to 30 days ago) → PHP Variable → Json → GoogleChart

SQL QUERY

$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);

Fix - Updated working SQL query

$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)
?>
+3


source to share


2 answers


I think you need one request, something like this should work:



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)
+4


source


You can use GROUP BY to help in this case.

Consider:



select count () from wp_users where dateiff (NOW (), user_registered) <30 group with date (NOW (), user_registered);

This will return you all the wp_users columns you need, plus the number of days ago stamped, all in one query.

+2


source






More articles:


All Articles