CODEWITHSUNDEEP
phpmysql
qadenza
qadenza

Reputation: 9293

get statistic data from two tables

two tables - posts - and users, common column - posts.user/users.id

I need to output some statistic data, i.e. how much posts each user has for today, last 7-30-365 days and total count.

The code below works, but I'm interesting to reduce number of queries, if possible. 

$stmt0 = $db->query("select * from users where role = 'mod' order by name asc");
    $count0 = $stmt0->rowCount();
    while($row0 = $stmt0->fetch()){
        $stmt1 = $db->query("select id from posts where user = " . $row0['id'] . " and date(date) = date(now())");
        $stmt2 = $db->query("select id from posts where user = " . $row0['id'] . " and date(date) > curdate() - interval 7 day");
        $stmt3 = $db->query("select id from posts where user = " . $row0['id'] . " and date(date) > curdate() - interval 30 day");  
        $stmt4 = $db->query("select id from posts where user = " . $row0['id'] . " and date(date) > curdate() - interval 365 day");
        $stmt5 = $db->query("select id from posts where user = " . $row0['id']);
   }

$count1 = $stmt1->rowCount();
$count2 = $stmt2->rowCount();
... etc.

Upvotes: 0

Views: 38

Answers (1)

StrongLucky
StrongLucky

Reputation: 588

The MySQL Query:

SELECT  
    user,
    COUNT(DISTINCT CASE WHEN date = CURDATE() THEN id END)  as countToday,
    COUNT(DISTINCT CASE WHEN date >= CURDATE() - INTERVAL 7 DAY THEN id END)  as    count7,
    COUNT(DISTINCT CASE WHEN date >= CURDATE() - INTERVAL 14 DAY THEN id END) as    count14,
    COUNT(DISTINCT CASE WHEN date >= CURDATE() - INTERVAL 30 DAY THEN id END) as count30, 
    COUNT(DISTINCT CASE WHEN date >= CURDATE() - INTERVAL 365 DAY THEN id END) as count365,
    COUNT(*) as countAll
FROM posts
GROUP BY posts.user

Access with PHP:

$res = $db->query($query); // set $query to my query
while($row = $res>fetch()){ // loop the result
    // access your row here
    echo $row["user"] // $row["countToday"] ...
}

Upvotes: 1

Related Questions