is there a faster way to do this?

[njdubois]

This is the code I have :  

	$start_month=5;
	$this_year=2013;
	$cur_day=10;
	$total_days=10;

	for ($i = 1; $i <= $total_days; $i++) {

		$current_date=$start_month.'/'.$cur_day.'/'.$this_year;

		$SELECT_SQL="SELECT * FROM Calls WHERE Dealership='".$_SESSION['dealership']."' AND Session_Date='".$current_date."'";
		$data_results=mysql_query($SELECT_SQL);

		$sales_calls=0;
		if($data_results) {
			$sales_calls=mysql_num_rows($data_results);
		}


		$SELECT_SQL="SELECT * FROM Calls WHERE Dealership='".$_SESSION['dealership']."' AND Session_Date='".$current_date."' AND Appointment!='No'";
		$data_results=mysql_query($SELECT_SQL);

		$days_appt=0;
		if($data_results) {
			$days_appt=mysql_num_rows($data_results);
		}



		$SELECT_SQL="SELECT * FROM Calls WHERE Dealership='".$_SESSION['dealership']."' AND Session_Date='".$current_date."' AND After_Hour='Yes'";
		$data_results=mysql_query($SELECT_SQL);

		$days_after_hour=0;
		if($data_results) {
			$days_after_hour=mysql_num_rows($data_results);
		}



		$SELECT_SQL="SELECT * FROM Stats WHERE Stat_Type='Total_Dealer_Calls' AND Stat_Name='".$_SESSION['dealership']."' AND Stat_Date='".$current_date."'";
		$data_results=mysql_query($SELECT_SQL);

		$days_dealer_calls=0;
		if($data_results) {
			$temp_row=mysql_fetch_assoc($data_results);
			$days_dealer_calls=$temp_row['Stat_Data'];
		}

		echo $sales_calls . ' - ' . $days_appt . ' - ' . $days_after_hour . ' - ' . $days_dealer_calls . '<br />';

	}

Is this the fastest way to do this?  I have a feeling there is a faster mysql statement to pull all the data I want instead of the 3 selects from Calls. 

 

thanks for any of your thoughts, ideas and opinions!

 

Nick

[kicken]

Your first three queries could be turned into this:

SELECT
	Sesson_date
	, SUM(CASE 
		WHEN After_hour='Yes' THEN 1
		ELSE 0
	END) as numAfterHours
	, SUM(CASE 
		WHEN Appointment != 'No' THEN 1
		ELSE 0
	END) as numAppointments
	, SUM(1) as numSalesCalls
FROM Calls
WHERE
	Dealership=$_SESSION['dealership']
	AND Session_date BETWEEN $current_date AND ($current_date+INTERVAL $total_days)
GROUP BY
	Session_date

You'll need to make sure you handle all the variables properly, I just stuck them there so you can see where they belong. What that will do is return several rows, one for each day. Each row will contain columns for each of your counts.

 

The last query, since it uses a different table and fields will need to be separate, however you can do similar to the above to return all the data in one go rather than a separate query for each day:

SELECT
	Stat_date
	, COUNT(*) as numDealerCalls
FROM Stats
WHERE
	Stat_type='Total_Dealer_Calls'
	AND Stat_name=$_SESSION['dealership']
	AND Stat_date BETWEEN $current_date AND ($current_date+INTERVAL $total_days)
GROUP BY
	Stat_date

The one potential issue here is if there are no matching rows for a particular day, that day will not be included in the output. So if you ran this today and there was no future data, then only one row for 8/20/2013 would be returned. 8/21/2013-8/30/2013 would not show in the result set.

 

If you want to make sure that all 10 days appear on your page you'll need to generate a list of the days still and merge the results with that list. A simple way to do this would be to generate the days and store them in an array as keys, then assign the query results as values to those entries. For example:

 

This would generate the array of days

$start = new DateTime('2013-8-20');
$allDays = array();
for ($i=0; $i<10; $i++){
   $allDays[$start->format('Y-m-d')] = array();
   $start->modify('+1 day');
}

Then when you loop the results:

while ($row=$result->fetch()){
   $allDays[$row['date']]['numAfterHours'] = $row['numAfterHours'];
   //...
}

$row['date'] would have to match the same format you use for they key in the array. You can use mysql's DATE_FORMAT function to create a matching format.

[njdubois]

Thank you so much for your reply!  Your help looks awesome and exactly what I was looking for!  I'll be playing around with it later tonight!

 

Thanks!!!!!