How do I create an array of data for 30 days from database table where some dates are not entered?

Let's say you have a simple table with:

ID  IP                  CREATED_DATE
-------------------------------------------------------
1   111.111.111.111     2011-03-28 14:12:31
2   111.111.111.111     2011-03-29 03:38:12
3   222.222.222.222     2011-04-02 12:04:45
4   111.111.111.111     2011-04-02 22:13:23
5   333.333.333.333     2011-04-03 05:53:15
6   222.222.222.222     2011-04-05 02:13:51
7   111.111.111.111     2011-04-07 11:45:34

I need to query the last 30 days and get a count of unique ips and total rows per day even if there are no entries for that day. So days that are not entered would obviously jus开发者_运维知识库t be 0 as the array value for both unique and total rows.

If you have an integers table, you can generate all the days in your desired timespan and join those with the tallies of IPs for each day appearing in your table:

   SELECT timespan."day",
          COALESCE(num_uniq_ips, 0) AS num_uniq_ips,
          COALESCE(num_records, 0) AS num_records
     FROM (SELECT DATE_SUB(CURRENT_DATE, INTERVAL i DAY) AS "day"
             FROM integers
            WHERE i < 30) timespan
LEFT JOIN (SELECT DATE(created_date) AS "day",
                  COUNT(DISTINCT ip) AS num_uniq_ips,
                  COUNT(1) AS num_records
             FROM so6025149
         GROUP BY 1) tallies
          ON timespan."day" = tallies."day"
 ORDER BY timespan."day" ASC;

You can create a temporary table, populate it with the past 30 days and use that in your query. From PHP code:

// create table with past 30 days
$buffer = "CREATE TEMPORARY TABLE IF NOT EXIST past_month (val DATETIME NOT NULL );";
$now = new DateTime(now);
for($i=30;$i>0;$i++)
{
    $buffer += "INSERT INTO past_month VALUES(" . $now . ");";  
    $now->sub(new DateInterval('P1D'));
}
mysql_query($buffer);

// the query    
$sql= "SELECT
           val,
           (SELECT COUNT(IP) 
            FROM table 
            WHERE DATE_FORMAT(CREATED_DATE,'%Y-%m-%d) = DATE_FORMAT(val,'%Y-%m-%d') As                UNIQUE_IPS   
       FROM 
           past_month";
$result = mysql_query($sql);

SELECT COUNT(DISTINCT column_name)
FROM table_name
WHERE ( DateTime > (GetDate()-30) )

something along those lines