Implications of massive sql calls

[Mahngiel]

My database skills are limited to advanced-novice abilities.  I understand basic database management, but lack any experience on getting the best out of a table / db.  I have reluctantly decided to place queries inside a calendar for events of that day, but I'm having hesitations because of the amount of calls that will be required.

 

I have three tables with different event types and their structure is all the same.  My major concern is that for a 31 day month, I'm automatically creating 93 queries. 

 

My first approach to this was to pull monthly and build arrays with keys set to the day of the month, but arrays need to be unique and if you have multidimensional arrays, you need to perform loops - which doesn't work because you're already inside a for() to create each day of the month.

 

So, in regards to pounding the database, are there any methods you can see to better this situation?

[PFMaBiSmAd]

I have three tables with different event types and their structure is all the same.

 

^^^ You need one table with all your events in it. If you need to distinguish the type of event, you would have a `type` column.

 

My first approach to this was to pull monthly and build arrays with keys set to the day of the month, but arrays need to be unique and if you have multidimensional arrays, you need to perform loops - which doesn't work because you're already inside a for() to create each day of the month.

 

^^^ Yes. Since the primary key is the day of the month, you simply directly access the data, if any, for the day as you loop over the days of the month. If you mean you are storing multiple events on any particular day as an array for that day, you would simply loop over that sub-array for the current day -

 

<?php
$data[2][] = 'event 1 on day number 2';
$data[2][] = 'event 2 on day number 2';
$data[2][] = 'event 3 on day number 2';

//$day would loop over the days for the current month - 1, 2, 3, ...,31

//$data[$day] would access the array of events for day number 2.

if(is_array($data[$day])){
    foreach($data[$day] as $event){
        echo "$event<br />";
    }
}

[Mahngiel]

I have three tables with different event types and their structure is all the same.

 

^^^ You need one table with all your events in it. If you need to distinguish the type of event, you would have a `type` column.

 

 

I anticipated this reply, as I ran stated "all the same".  What is actually all the same are the structural & column names for the different tables.  Even if I did put my events, matches,  everything into a single table, I'm still making 31 requests.  Using a framework db driver, is this considered a crappy use of queries?

 

 

^^^ Yes. Since the primary key is the day of the month, you simply directly access the data, if any, for the day as you loop over the days of the month. If you mean you are storing multiple events on any particular day as an array for that day, you would simply loop over that sub-array for the current day -

 

<?php
$data[2][] = 'event 1 on day number 2';
$data[2][] = 'event 2 on day number 2';
$data[2][] = 'event 3 on day number 2';

//$day would loop over the days for the current month - 1, 2, 3, ...,31

//$data[$day] would access the array of events for day number 2.

if(is_array($data[$day])){
    foreach($data[$day] as $event){
        echo "$event<br />";
    }
}

 

I actually attempted this before I wrote the calendar with the queries inside and came to this board.  My code essentially looked like this:

$days_in_month = (GREGORIAN, $month, $year);

for($day =1; $day <= $days_in_month, $day++)
{
    $days[$day] = array();
}

//This gives me an array with the day number as the key.  Now i can get all the events
$events = $this->events->get_events( *query construction* );

// loop through each day
foreach($days as $day=>$key){

//loop through each event, conditionally matching
foreach($events as $event){
    if($event->event_day == $day){
        $key = $key + array('event' => $events->event_summary);
    }
}

// after passing $events to the calendar function, it can be used like this:
for($day =1; $day <= $days_in_month; $day++){
    if( $event[$day] )
    // now you've check to see if the array key matches the current $day and if so you can echo the event summary
    }
}

[Jessica]

You could get all of the entries with 1 query for any month. How are you storing the date?

[Mahngiel]

You could get all of the entries with 1 query for any month. How are you storing the date?

 

I have set up two different table structures atm. On breaks down the date into month, day, year, the other uses traditional timestamp.  Here's what i have with the queries embedded in the code for two tables.

 

for($day=1, $days_in_month=gmdate('t',$first_of_month); $day<=$days_in_month; $day++, $weekday++)
{
if($weekday == 7)
{
	// Week is full, start new tablerow
	$weekday   = 0; 
	$calendar .= "</tr><tr>";
}

$calendar .= '<td data-date="' . $day . '" ><div>';

if( !(bool)$widget )
{
	$stuffs = $this->CI->events->get_events(array('event_month' => $month, 'event_year' => $year, 'event_day' => $day));
	$matches = $this->CI->matches->get_matches_like($year . '-' . $month . '-' . $day);

	if($stuffs):
	$calendar .= '<ul class="cal-events"> <li>' . count($stuffs) . ' Events';
		foreach($stuffs as $stuff):
			$calendar .= '<ul class="cal-event"><li>' . $stuff->event_title . '</li><li>' . $stuff->event_summary .'</li></ul>';
		endforeach;
	$calendar .= '</li></ul>';
	endif;

	if($matches):
	$calendar .= '<ul class="cal-matches"> <li>' . count($matches) . ' Matches';
		foreach($matches as $match):
			$calendar .= '<ul class="cal-match"><li>' . $match->match_title . '</li><li>' . $match->match_summary .'</li></ul>';
		endforeach;
	$calendar .= '</li></ul>';
	endif;
}

 

The previous method i was using before i realized the key collisions and submitting as an array:

// Retrieve this month's events
if($events = $this->events->get_events(array('event_month' => $month, 'event_year' => $year)))
{
$event = array();
foreach($events as $entry)
{
	// Create array for calendar
	$event[$entry->event_day] = array( lcfirst(date("M-y", mktime(0, 0, 0, $entry->event_month))) . '/' . $entry->event_slug, 'calendar-event', 'Event: ' . $entry->event_title);
}
}

// which when passed to the calendar i checked for key matching before output during the for()

[Jessica]

I'm not even going to read all that. If you have a timestamp, you can get all the entries between one timestamp and another. Same for if you have month and year.

[Mahngiel]

I'm not even going to read all that. If you have a timestamp, you can get all the entries between one timestamp and another. Same for if you have month and year.

I'm well aware of how to get the dates I wish, either by month or by day.  What my concern is, is determining if making 31 (or even 93) queries to build the calendar is really the best method.

[PFMaBiSmAd]

For most simple SELECT queries, the time it takes to transmit the query statement from php to the mysql server takes longer than the query itself takes to execute, even when using prepared statements, the time it takes to transmit just the replaceable parameter values from php to the mysql server takes longer than the query itself takes to execute. So yes, it will be significantly faster to form and execute one query that gets all the data you need at one time to produce a page.

 

 

[Jessica]

I'm not even going to read all that. If you have a timestamp, you can get all the entries between one timestamp and another. Same for if you have month and year.

I'm well aware of how to get the dates I wish, either by month or by day.  What my concern is, is determining if making 31 (or even 93) queries to build the calendar is really the best method.

No, it's not, that's why we said to do ONE.

[fenway]

How about showing us your table structure and the queries you're running, instead of making us guess?