Initial steps of a 'On This Day in History' script

[MargateSteve]

A friend of mine is looking at relaunching a site he used to run and I am going to try to persuade him to move over to a dynamic site.

 

The site is a Football Clubs history site and contains around 2,000 games and several hundred players. The obvious advantage of a dynamic site is that even the bits I just mentioned run to around 2,500 static pages, which could be reduced to just 2 pages if dynamically driven.

 

I am fairly confident that I can do the conversion, but there is one thing that I already know I may struggle with and that is the page which shows game, births and deaths on this day in history. A rough example (using a very heavy Javascript code) can be found at http://follyball.co.uk/jeff/.

 

In a simplified form (ignoring for now the foreign keys to relationships that these tables actually have) there will be two tables

GAMES

game_id | date | competition | opponent | score | attendance

 

PLAYERS

player_id | surname | firstname | date of birth | date of death

 

What I would be looking for is a way to take todays date, and find any corresponding records in games.date, players.date of birth, players.date of death on the same date in previous years. This would be simple to do as three different queries but as I would like to actually mix the three and show them in date order, I would like to do it in one query, presumably using UNIONS.

 

On top of this, I would also like a slightly different output for each different set, for example, if it was a game I would like

year, competition, opponent, score, attendance,

 

Whereas if it is a players birth or death, I would like it to show something like

firstname surname was born in year

 

Before I go delving too deep into how it may be done, I would like to know if it can be done! I cannot see why it would not be able to generate this but if it is going to be way out of my ability, then I will look for another option!

 

Thanks in advance

Steve

[fugix]

it can absolutely be done. from what you described...it looks like you'll want to use JOIN in your query for one query, and you could use basic if statements for the output changes..

[MargateSteve]

I never imagined I could put it together just using a JOIN as there are no linked fields between those two tables. I will see what I can put together starting with 3 seperate queries, then see how I can put them together.

Thanks

Steve

[fugix]

yes you can use LEFT JOIN or RIGHT JOIN if there are no matching fields