PHP and Graphs

[DarkMantis]

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);
?>

[requinix]

Once you're at the point where you have/can get an array of everything, you can use date to get the date. So like

$dates = array();
while ($row = mysql_fetch_array($resultset)) {
$date = date("d-m-Y", $row["date_registration"]);
if (isset($dates[$date])) {
	$dates[$date]++;
} else {
	$dates[$date] = 1;
}
}

 

 

But you can make the query do more work. Try running:

SELECT DATE_FORMAT(FROM_UNIXTIME(`date_registration`), '%d-%m-%Y') AS `date`, COUNT(1) AS `count`
FROM `crowdcube_users`
WHERE `date_registration` BETWEEN %d AND %d
GROUP BY `date`

Then you have a set of results already counted. (Note how it won't give you zeroes for the days nobody registered - the dates will be missing from the results.)

[DarkMantis]

Omg, thank you. That was amazingly useful!

 

Thanks for all your help, you have just taught me a lot.

 

Thanks again!