Hi Guys,
I have recently come across the need to extract data from a database and generate a report (in the form of graphs, but that's not really the problem) to see the increase/decrease of registered users between Dates X and Y.
I can get the information from the database, but I'm not sure how to sort all the users which registered per day.
For example:
I want to get all the data from 20-07-2011 to 25-07-2011 and then I need to find the users which registered on the 20th, 21st, 22nd, 23rd, 24th and 25th and put them into appropriate places (I'd assume arrays).
Please bare in mind that all dates are stored in unixtimestamps and I'm not very good at timestamps.
Any help would be great, my script is below, however, I know it's really badly written, it's only a test-run at the moment.
Thanks again!
<?php
/**
* @author DarkMantis
* @copyright 2011
*/
class GenerateReports{
/**
* The constructor of the class
*/
public function __construct(){
}
/**
* Gets the RAW data from the database ready to be parsed.
*
* @param int $startDate
* @param int $endDate
* @param int $reportType
*
* @return array $arrOut
*/
public function getData( $startDate=null, $endDate=null, $reportType=0 ){
$this->ConnectToDb(); //Not a real function
//Perform a few little checks to ensure data is passed to the function correctly
if( ($startDate == NULL) && ($endDate == NULL) ){
$startDate = strtotime('2000-01-01');
$endDate = strtotime('now');
}else{
$startDate = strtotime($startDate);
$endDate = strtotime($endDate);
}
//Get different report types and data
switch($reportType){
case '1':
//Get all results between given dates
//for($i = 1; $i < 31; $i++){
unset($endDate);
$endDate = ($startDate+($i*86400));
$sqlQuery = mysql_query(sprintf(
'SELECT `id`,`date_registration` FROM `crowdcube_users` WHERE `date_registration` > %d AND `date_registration` < %d',
$startDate,
$endDate
));
// }
while($arrResult = mysql_fetch_assoc($sqlQuery)){
print_r('<pre>' . var_dump($arrResult) . '</pre>');
if($startDate){
}
}
break;
case 'default':
break;
}
}
}
//See if the script works
$new = new GenerateReports();
$new->getData('2011-07-24', 'now', 1);
?>
