[SOLVED] Multiple where commands on a select.

[cooldude832]

I want to query my User contributions tables and return results like

Post in last day: 15

Post in last week: 20

Post in last month: 100

Post in last year: 10000

 

Can I do this via one query if my table is like

 

Posting

PostID

UserID

PostDate

Post

etc.

 

I don't know how to do the multiple limiting "wheres" on the selects.

 

[fenway]

I guess you could have a SUM with an IF () condition for each one.

[cooldude832]

can you show me an example

would I do something

<?php
$userid = "15";
$oneweek = date("Y-m-d G:i:s", strtotime("-7 Days"));
$q = "Select SUM(if(Post_Date >= '".$oneweek."')Posting_Table.Post_date) as `One_week` From `Posting_Table` Where Userid = '".$userid."'";
?>

 

I never done a mysql if

[Barand]

<?php
$q = "SELECT 
        SUM(IF(Post_date > NOW() - INTERVAL 1 DAY, 1, 0)) AS lastday ,
        SUM(IF(Post_date > NOW() - INTERVAL 7 DAY, 1, 0)) AS lastweek ,
        SUM(IF(Post_date > NOW() - INTERVAL 1 MONTH, 1, 0)) AS lastmonth ,
        SUM(IF(Post_date > NOW() - INTERVAL 1 YEAR, 1, 0)) AS lastyear
      FROM Posting_Table
      WHERE Userid = '$userid' ";  

[cooldude832]

Thanks, I just let php do the work on this one, but I'll try this one later probably a way better choice than php cause I am drawing the whole date out and comparing it.