Help with multiple queries

sledgeweb

Hello-

I'm building a timeline website that stores historical events in a databse by date.

When the main page is accessed, it displays a timeline from 3000 BC to 2000 AD, in increments of decades.

As the page is built, the mysql database is queried for each decade to see if any events are stored for that decade using a for loop:

for ($i=-3000; $i<2000; $i+=10)

Which results in 500 queries being run on the page. However, the events are split into two categories, in two tables, and so, actually 1000 queries are run on the page.

Further, you can zoom in to a 1 hundred year span. On those pages, a timeline is displayed for the century you clicked on, with increments in months. Again, as the page is built, the database is queried for events in the designated month/year.

12 months * 100 years = 1200 queries. Again, this is doubled, so there are 2400 mysql queries run on these pages.

The pages actually load pretty fast: http://bibletimeline.cubit.net

But, it seems like this isn't the best way to get the desired result. Does anyone have any ideas on how I could improve this? Or do you think the method I'm using is OK?

Thanks,
Sledgeweb

barand

I'd do a single query ordered by date.

sledgeweb

If I do that, then I get a large array containing all the data. So when I building the timeline, and going through the decades, how do I pull out the events in the array that correspond to that decade? Seems bad to do a foreach loop on the entire array for each decade. Any ideas on how to implement this?

Thanks,
Sledge

sledgeweb

Hmm.. actually, I suppose it isn't an array. So I can't use array search. It is a result set. So... how can I extract the info from the result set as I'm cycling through the decades?

Thanks,
Sledge

shakah

I don't know if this is quite what you're looking for, but as far as the MySQL query goes perhaps you could use GROUP BY to cut down your number of queries when presenting your original timeline. Assuming your table is something like:

create table events (
event_name varchar(50) not null
,event_date datetime not null
) ;

insert into events values ('World War I begins', '1914-06-28') ;
insert into events values ('World War I ends', '1918-11-11') ;
insert into events values ('World War II begins', '1939-09-01') ;
insert into events values ('9/11 Attack', '2001-09-11') ;

you could use a query similar to the following to identify decades that have events:

select 10FLOOR(year(event_date)/10) AS 'decade'
,count() AS 'number of events'
from events
group by FLOOR(year(event_date)/10)
order by 1 ;

to get something like the following result:

+--------+------------------+
| decade | number of events |
+--------+------------------+
| 1910 | 2 |
| 1930 | 1 |
| 2000 | 1 |
+--------+------------------+

sledgeweb

Hmm... I'll have to think about that. I'm not sure it actually helps because I need the details for all of the events, not just how many are in a decade.

I've now stored my query results into an array. And I can use array_search to find a specific year. Unfortuantely, array_search won't let me search for a value between two constants, and I can't get two results from array_search. So if there are two events in the array with year=1914, it only returns the key to one of the events.

Any other ideas? Also - can someone confirm that it is BAD to run so many queries on a page? Otherwise, I will just leave it as is.

-Sledge

ultraslacker

The pages actually load pretty fast

Well, yeah, under no load. Try with simulated users.

You haven't posted any code, so I cant see what is stopping you from using one query for the initial page, and detail queries on the drilldown pages.

barand

Having got your single query in chronological order, why don't you create the timeline as you read through the query results.

Its a lot more efficient than building the timeline and then searching for individual decades as you go.

sledgeweb

That sounds reasonable, but how? If I just cycle through the results I will not output any timeline for years which there are no events. Not what I want to do.

Here's what I've come up with so far:

$startDate = -3000; //set start year
$endDate = 2000; //set end year

//Select events that appear on main page, order by year
$select = "SELECT title, eid, year FROM events_sec WHERE isMajor = 1 ORDER BY year";

//Get query results
$eventResults = mysql_query($select, $db);

//Store result in multidimensional array,
//with the key of the first array being the year
for ($i=0; $i < mysql_num_rows($eventResults); $i++)
{
$eventArray[mysql_result($eventResults, $i, "year")]=mysql_fetch_row($eventResults);
}

If I could check the key, then I could cycle through the decades and output the events if the key falls within the current decade.

Something like this: (This is just thought code, not actual syntax)

for ($i=$startDate; $i<#endDate; $i+=100) //cnt each century
{
print some formating stuff
for ($cnt = $i; $cnt <= $i+90; $cnt+=10) //cnt each decade in century
{
//is the current key of eventArray within this decade
check if current eventArray key <= #cnt+9 AND >= $cnt
if TRUE { print event, set array pointer to next }
ELSE { leave pointer alone }
}
}

Does this sound like a good approach? If so, how can I check the key? Sometimes there will be duplicate keys, is this a problem? If there are 5 events in 1944, then all 5 would have a key of 1944.

Is there a better way to do this? Maybe not using an array? If there is, can you please show some pseudo code, at least?

Thanks,
Sledge

sledgeweb

Well... storying the query results into an array, I've run into a problem:

for ($i=0; $i < mysql_num_rows($eventResults); $i++)
{
$eventArray[mysql_result($eventResults, $i, "year")]=mysql_result($eventResults, $i, 'eid') . ", " . mysql_result($eventResults, $i, 'title');
}

If two events have the same year, the code above overwrites the key, and the data. I suppose the key could be the event ID, which is unique, and the year be an element in the array?

Still open to other thoughts on how to do this the best way.

-Sledge

sledgeweb

OK, after a lot of tests, I've now reached this point. I put the results from my query into an array, using the Event Id, a unique indentifier, as the key:

$startDate = -3000;  //set start year
$endDate = 2000; // set end year

//select all events that appear on front page
$select = "SELECT title, eid, year FROM events_sec WHERE isMajor = 1 ORDER BY year";

//run the query
$eventResults = mysql_query($select, $db);

//cycle through results and store in array
//use event id # as key, store year and title as array within the array
for ($i=0; $i < mysql_num_rows($eventResults); $i++)
	{
	$eventArray[mysql_result($eventResults, $i, "eid")]=array(year => mysql_result($eventResults, $i, 'year'), 'title' => mysql_result($eventResults, $i, 'title'));
	}

Now, when I loop through the decades as shown in code above (in a previous post) I can check the year of the current element in the array:

$element = current($eventArray)
if (($element[year] <= ($cnt+9) ) && ($element[year] >= $cnt)
   {
   print "$element[title]";
   $eventArray = next($eventArray) // IS THIS CORRECT?
   }

How does this look to you all?

Thanks,
Sledge

sledgeweb

I now have a working version that uses only two mysql queries instead of 1000! Let me know if you think this is a better method of implementation (also, how can I test the speed of it, simulate users?)

//set start year and end year
$startDate = -3000;
$endDate = 2000;

//select historical world events that appear on front page
$select = "SELECT title, eid, year FROM events_sec WHERE isMajor = 1 ORDER BY year";

//store results
$eventResults = mysql_query($select, $db);

//see if there are any results
$rows = mysql_num_rows($eventResults);

//put results in an array, eith Event Id as KEY
//event year and title are stored as array within array
for ($i=0; $i < mysql_num_rows($eventResults); $i++)
	{
	$eventArray[mysql_result($eventResults, $i, "eid")]=array(year => mysql_result($eventResults, $i, 'year'), title => mysql_result($eventResults, $i, 'title'));
	}

//select historical biblical events that appear on front page
$select2 = "SELECT title, eid, year FROM events WHERE isMajor = 1 ORDER BY year";

//store results
$eventResults2 = mysql_query($select2, $db);

//see if there were any results
$rows2 = mysql_num_rows($eventResults2);

//put results in an array, eith Event Id as KEY
//event year and title are stored as array within array
for ($i=0; $i < mysql_num_rows($eventResults2); $i++)
   {
   $eventArray2[mysql_result($eventResults2, $i, "eid")]=array(year => mysql_result($eventResults2, $i, 'year'), title => mysql_result($eventResults2, $i, 'title'));
   }

I then cycle through century and decades as previously described, and check the arrays

if ($rows > 0) //only execture if there were results from the query
   {
   $x = 0; //used to determine if more than one event in decade
   $element = current($eventArray); //get current element in array
   $prevYear = ""; //stores Event Id from previous element

   //while the current element is in the current decade do the following
   //also checks to make sure current element id is not equal
   //to previous element id, because next function doesnt
   //move if is at the end of the array
   while(($element[year] <= ($cnt+9)) && ($element[year] >= $cnt) && (key($eventArray) != $prevYear))
   {

//if there is more than one event in decade, add a comma
   if ($x > 0) { $events .= ", "; }  

//add ID and Title to events variable
$events .=  key($eventArray) . $element[title];
$x++; //move counter for mult decade events
$prevYear = key($eventArray); //set var to store event id
$element = next($eventArray); //move to next element
   }
   }

I haven't included all the code. I left out the html that is written. But, basically, the html is output with the events.

I run the same group of code above for the second set of variables for the biblical events: $eventArray2.

Thoughts?
-Sledge

barand

My approach would be, off the top of my head so don't expect perfection 🙂

current decade = -300; // start position

while not end of results
{

while record decade = current decade
{
read recs
store event
}

process current decade

// now have record waiting from a later decade

for decades between current decade and new record decade
{
process blank (no events) decades
}

current decade = new record decade

}// end of reults loop

hth