Link to home
Start Free TrialLog in
Avatar of sabecs
sabecs

asked on

MySQL - display dates where no match found.

Hi, I have a table as below which tracks users stats.  

CREATE TABLE IF NOT EXISTS `track_users` (
  `id` int(12) NOT NULL auto_increment,
  `user_id` int(5) NOT NULL,
  `action` enum('Login','Logout') default NULL,
  `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `track_users`
--

INSERT INTO `track_users` (`id`, `user_id`, `action`, `time`) VALUES
(1, 31, 'Login', '2010-12-29 10:38:24'),
(2, 31, 'Logout', '2010-12-29 10:38:44'),
(3, 407, 'Login', '2010-12-30 11:24:29'),
(4, 407, 'Login', '2010-12-30 11:29:40'),
(5, 395, 'Login', '2010-12-30 12:09:30'),
(6, 367, 'Login', '2010-12-30 15:14:05'),
(7, 31, 'Login', '2010-12-15 21:33:10'),
(8, 31, 'Logout', '2010-12-15 22:33:10'),
(9, 367, 'Login', '2010-12-31 12:58:19'),
(10, 393, 'Login', '2010-12-31 13:47:34');


I then extract the results and work out how many users logged in per day for the date range requested.
                  
$no_of_days = ((strtotime($_POST['to_date']) - strtotime($_POST['from_date'])) / (60 * 60 * 24));                   
$from_date = $_POST['from_date'];
$to_date = $_POST['to_date']." 23:59:59";

//get number of users that logged in per day.
$query_num_users_rs = "SELECT *,COUNT(action) FROM `track_users` WHERE action = 'Login' && track_users.time >= '$from_date' && track_users.time <= '$to_date' GROUP BY DAY(time)";
example: "SELECT *,COUNT(action) FROM `track_users` WHERE action = 'Login' && track_users.time >= '2010-12-01' && track_users.time <= '2010-12-31 23:59:59' GROUP BY DAY(time)"

$num_users_rs = mysql_query($query_num_users_rs, $conn_data) or die(mysql_error());
$row_num_users_rs = mysql_fetch_assoc($num_users_rs);
$totalRows_num_users_rs = mysql_num_rows($num_users_rs);


Finally, I loop through results as below.

do {

$display_date = date("j M y",strtotime($row_num_users_rs['time']));
$times = $row_num_users_rs['COUNT(action)'];
$data[$display_date] = $times;      

}while ($row_num_users_rs = mysql_fetch_assoc($num_users_rs));      

All works fine but I would also like to include a zero count for the days when no one logs in.
I know the number of days in the range ($no_of_days) so how would I loop through the above to include these zero dates as well?
In above example only dates 15 Dec 10, 29 Dec 10 & 30 Dec 10 are displayed.

I hope this makes sense.
Thanks in advance for your comments and feedback.
Avatar of atanu_mib
atanu_mib

sorry ... i dont khow
SOLUTION
Avatar of marklogan
marklogan
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
$ dropped.
$loopDate = date('Y-m-d', mktime(0,0,0,date('m',$tempString),date('d',$tempString)+$i,date('Y',$tempString)));

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Beverley Portlock
Beverley Portlock
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Brian:  Happy New Year, and here is the extra closing parenthesis for line 15  

    )


;-)

All the best, ~Ray
Avatar of sabecs

ASKER

Thanks for your help, Happy New Year.
Thanks Ray - I wondered where I had left that bracket

;-)

and a happy and prosperous new year to all at EE