MargateSteve Posted May 3, 2011 Share Posted May 3, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/235456-initial-steps-of-a-on-this-day-in-history-script/ Share on other sites More sharing options...
fugix Posted May 3, 2011 Share Posted May 3, 2011 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.. Quote Link to comment https://forums.phpfreaks.com/topic/235456-initial-steps-of-a-on-this-day-in-history-script/#findComment-1210126 Share on other sites More sharing options...
MargateSteve Posted May 3, 2011 Author Share Posted May 3, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/235456-initial-steps-of-a-on-this-day-in-history-script/#findComment-1210139 Share on other sites More sharing options...
fugix Posted May 3, 2011 Share Posted May 3, 2011 yes you can use LEFT JOIN or RIGHT JOIN if there are no matching fields Quote Link to comment https://forums.phpfreaks.com/topic/235456-initial-steps-of-a-on-this-day-in-history-script/#findComment-1210142 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.