MargateSteve

And that was exactly my question in the first post. I tried WHERE view1.date BETWEEN '2010-08-01' AND '2011-05-01' in the query and although some results were returned there were fewer than there should have been. My assumption was that the date fields in each view needed joining but could not work out how to do it. Steve

If I add the WHERE clause to the views it will be restricted to that one date range. I want to be able to generate separate statistics for different date ranges as time goes on ie. BETWEEN 2009-01-01 AND 2009-12-31 as one set of results and BETWEEN 2010-01-01 AND 2010-12-31 as another. Steve

Sorry, it always makes more sense in my head than on the screen! The views and query that I currently have successfully generates the records that it should but it takes all records in the database. However, I want to be able to use a WHERE clause on `games`.`date` in both views so I can show different records for each season. I have previously done this within the views by adding WHERE `games`.`date` BETWEEN x AND x but that then restricts the views to just those dates and cannot be used for any different dates. What I am hoping to do is use a similar WHERE clause but within the query and not the views, so that I can alter just the query for each season and not have to create new views for each season. Steve

Sorry for the delay in getting back but have had a major virus issue! The LEFT JOIN's are probably hangovers from when there was a WHERE clause on `games`.`date` to make sure only the relevant records where pulled for the view. If they are superfluous now I will happily have a go at re-writing it. However, at the moment I think that I am going to have create a version of each view for each season with a WHERE `games`.`date` BETWEEN x AND x as I still cannot find a way to filter the date via the query on the page. Steve

Sorry to hijack the thread but something in here was new to me. I noticed the use of @mysql_num_rows in teynon's code. I always just use mysql_num_rows without the @ in front of it. Does the @ do anything different to the execution? Steve

Firstly a huge apology for the double post. I accidentally hit post instead of preview midway through my first attempt and by the time I finished adding the rest of the data, the modify post time limit had expired. Quite some time back, I was given help on here with aggregating fields from two different tables. It still works fine but I failed to foresee a future problem in that I need to use posted data to filter the query. The data generated is statistics for football (soccer) players and is designed to show the number of minutes played per goal scored. Everything works fine but it only shows all data but I need to be able to filter the query between two dates at a time to some the statistics for separate seasons. The first view gets all the games that a player has featured in (from 'appearances') including the number of minutes that he has been on the pitch and counts them by separate competition ('lgemins', 'facmins' etc in the counts towards the end of the view). `view1` AS select `games`.`all_games_id` AS `match_id` ,`games`.`date` AS `date` ,`games`.`time` AS `time` ,`games`.`comp` AS `competition` ,`games`.`round` AS `round` ,`games`.`replay` AS `replay` ,`games`.`home_team` AS `home_team` ,`games`.`away_team` AS `away_team` ,`games`.`home_goals` AS `home_goals` ,`games`.`away_goals` AS `away_goals` ,`games`.`attendance` AS `attendance` ,`games`.`report_url` AS `report_url` ,`games`.`photo_url` AS `photo_url` ,`games`.`notes` AS `notes` ,`games`.`extratime` AS `extra_time` ,`appearances`.`app_id` AS `app_id` ,`appearances`.`match` AS `match` ,`appearances`.`number` AS `number` ,`appearances`.`player` AS `player` ,`appearances`.`type` AS `type` ,`appearances`.`on` AS `on` ,`appearances`.`off` AS `off` ,`appearances`.`yellows` AS `yellows` ,`appearances`.`red` AS `red` ,`appearances`.`replaced` AS `replaced` ,`players`.`player_id` AS `player_id` ,`players`.`surname` AS `surname` ,`players`.`firstname` AS `firstname` ,`players`.`date_of_birth` AS `date_of_birth` ,`players`.`date_joined` AS `date_joined` ,`players`.`date_left` AS `date_left` ,count((case when ((`games`.`comp` = 1) and (`appearances`.`type` = 1)) then 1 else NULL end)) AS `lgest` ,count((case when ((`games`.`comp` = 1) and (`appearances`.`type` = 2)) then 1 else NULL end)) AS `lgesub` ,count((case when ((`games`.`comp` = 1) and (`appearances`.`type` = 3)) then 1 else NULL end)) AS `lgebench` ,count((case when ((`games`.`comp` = 2) and (`appearances`.`type` = 1)) then 1 else NULL end)) AS `facst` ,count((case when ((`games`.`comp` = 2) and (`appearances`.`type` = 2)) then 1 else NULL end)) AS `facsub` ,count((case when ((`games`.`comp` = 2) and (`appearances`.`type` = 3)) then 1 else NULL end)) AS `facbench` ,count((case when ((`games`.`comp` = 3) and (`appearances`.`type` = 1)) then 1 else NULL end)) AS `fatst` ,count((case when ((`games`.`comp` = 3) and (`appearances`.`type` = 2)) then 1 else NULL end)) AS `fatsub` ,count((case when ((`games`.`comp` = 3) and (`appearances`.`type` = 3)) then 1 else NULL end)) AS `fatbench` ,count((case when ((`games`.`comp` = 4) and (`appearances`.`type` = 1)) then 1 else NULL end)) AS `kscst` ,count((case when ((`games`.`comp` = 4) and (`appearances`.`type` = 2)) then 1 else NULL end)) AS `kscsub` ,count((case when ((`games`.`comp` = 4) and (`appearances`.`type` = 3)) then 1 else NULL end)) AS `kscbench` ,count((case when ((`games`.`comp` = 5) and (`appearances`.`type` = 1)) then 1 else NULL end)) AS `lcst` ,count((case when ((`games`.`comp` = 5) and (`appearances`.`type` = 2)) then 1 else NULL end)) AS `lcsub` ,count((case when ((`games`.`comp` = 5) and (`appearances`.`type` = 3)) then 1 else NULL end)) AS `lcbench` ,count((case when (`appearances`.`type` = 1) then 1 else NULL end)) AS `tst` ,count((case when (`appearances`.`type` = 2) then 1 else NULL end)) AS `tsub` ,count((case when (`appearances`.`type` = 3) then 1 else NULL end)) AS `tbench` ,(sum((case when (`games`.`comp` = 1) then `appearances`.`off` else NULL end)) - sum((case when (`games`.`comp` = 1) then `appearances`.`on` else NULL end))) AS `lgemins` ,(sum((case when (`games`.`comp` = 2) then `appearances`.`off` else NULL end)) - sum((case when (`games`.`comp` = 2) then `appearances`.`on` else NULL end))) AS `facmins` ,(sum((case when (`games`.`comp` = 3) then `appearances`.`off` else NULL end)) - sum((case when (`games`.`comp` = 3) then `appearances`.`on` else NULL end))) AS `fatmins` ,(sum((case when (`games`.`comp` = 4) then `appearances`.`off` else NULL end)) - sum((case when (`games`.`comp` = 4) then `appearances`.`on` else NULL end))) AS `kscmins` ,(sum((case when (`games`.`comp` = 5) then `appearances`.`off` else NULL end)) - sum((case when (`games`.`comp` = 5) then `appearances`.`on` else NULL end))) AS `lcmins` ,(sum(`appearances`.`off`) - sum(`appearances`.`on`)) AS `tmins` from (`all_games` `games` join (`appearances` join `players` on((`appearances`.`player` = `players`.`player_id`)))) where ((`appearances`.`match` = `games`.`all_games_id`) and (`games`.`comp` <> 6)) group by `players`.`player_id`; The second view gets all the goals that a player has scored that a player has featured in (from 'goals') and counts them by separate competition (`lgegls`, `facgls` etc in the counts towards the end of the view). `view2` AS select `games`.`all_games_id` AS `match_id` ,`games`.`date` AS `date` ,`games`.`time` AS `time` ,`games`.`comp` AS `competition` ,`games`.`round` AS `round` ,`games`.`replay` AS `replay` ,`games`.`home_team` AS `home_team` ,`games`.`away_team` AS `away_team` ,`games`.`home_goals` AS `home_goals` ,`games`.`away_goals` AS `away_goals` ,`games`.`attendance` AS `attendance` ,`games`.`report_url` AS `report_url` ,`games`.`photo_url` AS `photo_url` ,`games`.`notes` AS `notes` ,`games`.`extratime` AS `extra_time` ,`goals`.`goal_id` AS `goal_id` ,`goals`.`match` AS `match` ,`goals`.`scorer` AS `scorer` ,`goals`.`goal_type` AS `goal_type` ,`goals`.`goal_time` AS `goal_time` ,`players`.`player_id` AS `player_id` ,`players`.`surname` AS `surname` ,`players`.`firstname` AS `firstname` ,`players`.`date_of_birth` AS `date_of_birth` ,`players`.`position` AS `position` ,`players`.`image` AS `image` ,`players`.`date_joined` AS `date_joined` ,`players`.`date_left` AS `date_left` ,count((case `games`.`comp` when 1 then 1 else NULL end)) AS `lgegls` ,count((case `games`.`comp` when 2 then 1 else NULL end)) AS `facgls` ,count((case `games`.`comp` when 3 then 1 else NULL end)) AS `fatgls` ,count((case `games`.`comp` when 4 then 1 else NULL end)) AS `kscgls` ,count((case `games`.`comp` when 5 then 1 else NULL end)) AS `lgecgls` ,count(`goals`.`goal_id`) AS `tgls` from (`all_games` `games` join (`goals` join `players` on((`goals`.`scorer` = `players`.`player_id`)))) where ((`goals`.`match` = `games`.`all_games_id`) and (`games`.`comp` <> 6)) group by `players`.`player_id`; The query that brings it all together (showing just 'comp' = 1 data at the moment) with the actual minutes per game as 'lminpg' is SELECT view1.player_id as theRealId , view1.surname as theRealSurname , view1.firstname as theRealFirstname , view1.lgemins/view2.lgegls as lminpg , view1.date as theDate , view1.* , view2.* FROM view1 LEFT JOIN view2 on view1.player_id=view2.player_id ORDER BY lminpg ASC All of the pages on the site use posted data to filter by season if (isset($_GET['season_id']) ? $_GET['season_id'] : 1); $season_id = $_GET['season_id']; using the following in the query LEFT JOIN seasons as S ON all_games.date BETWEEN S.season_start AND S.season_end WHERE S.season_id = $season_id I need to implement something similar with the query from the views but have come up against blanks! If I use something like WHERE view1.date BETWEEN '2010-08-01' AND '2011-05-01' which is equivalent to 'S.season_start AND S.season_end' for the 2010-11 season, some results are returned but nowhere near the correct number. I am assuming that the date fields from both views need to be joined but everything I have tried just returns the 'die' error message. I am probably missing something very simple here but I just cannot get my head around it! If there is any more information required (such as test data) then just let me know. As always, any pointers would be gratefully received. Thanks in advance Steve

Absolutely 100% perfect!! I made a couple of slight amendments in that I changed the ORDER BY in GROUP_CONCAT to date DESC to get the last 6 games for each team, and the overall ORDER BY is now on Pts then GD. I also joined the seasons table so I can show it season by season and my test run works exactly how it should - http://www.margate-fc.com/content/test/cform3.php. All that is left to do is to turn the case statements into reusable strings just to tidy the code up but massive, massive thanks to you. I would not like to think how many hours have been spent on this! I have put my (current) final query below for other peoples reference. Thanks again Steve SELECT last_games.team_id , last_games.team_name , COUNT(DISTINCT `all_games`.`all_games_id`) AS 'GP' , SUM(CASE WHEN all_games.`home_team` = last_games.team_id AND all_games.`home_goals` > all_games.`away_goals` THEN 1 ELSE 0 END) + SUM(CASE WHEN all_games.`away_team` = last_games.team_id AND all_games.`home_goals` < all_games.`away_goals` THEN 1 ELSE 0 END) AS 'W' , SUM(CASE WHEN all_games.`home_team` = last_games.team_id AND all_games.`home_goals` < all_games.`away_goals` THEN 1 ELSE 0 END) + SUM(CASE WHEN all_games.`away_team` = last_games.team_id AND all_games.`home_goals` > all_games.`away_goals` THEN 1 ELSE 0 END) AS 'L' , SUM(CASE WHEN all_games.`home_team` = last_games.team_id AND all_games.`home_goals` = all_games.`away_goals` THEN 1 ELSE 0 END) + SUM(CASE WHEN all_games.`away_team` = last_games.team_id AND all_games.`home_goals` = all_games.`away_goals` THEN 1 ELSE 0 END) AS 'D' , (SUM(CASE WHEN all_games.`home_team` = last_games.team_id THEN `all_games`.`home_goals` ELSE 0 END) + SUM(CASE WHEN all_games.`away_team` = last_games.team_id THEN `all_games`.`away_goals` ELSE 0 END)) - (SUM(CASE WHEN all_games.`home_team` = last_games.team_id THEN `all_games`.`away_goals` ELSE 0 END) + SUM(CASE WHEN all_games.`away_team` = last_games.team_id THEN `all_games`.`home_goals` ELSE 0 END)) AS 'GD' , ((SUM(CASE WHEN (all_games.`home_team` = last_games.team_id AND all_games.`home_goals` > all_games.`away_goals`)OR(all_games.`away_team` = last_games.team_id AND all_games.`home_goals` < all_games.`away_goals`) THEN 3 ELSE 0 END) + SUM(CASE WHEN (all_games.`home_team` = last_games.team_id OR all_games.`away_team` = last_games.team_id) AND all_games.`home_goals` = all_games.`away_goals` THEN 1 ELSE 0 END))) AS P , SUM(CASE WHEN all_games.`away_team` = last_games.team_id THEN 1 ELSE 0 END) AS 'AWAY_GAMES' , SUM(CASE WHEN all_games.`home_team` = last_games.team_id THEN 1 ELSE 0 END) AS 'HOME_GAMES' , SUM(CASE WHEN all_games.`away_team` = last_games.team_id THEN COALESCE(`all_games`.`away_goals`, 0) ELSE 0 END) AS 'GOALS_WHEN_AWAY' , SUM(CASE WHEN all_games.`home_team` = last_games.team_id THEN COALESCE(`all_games`.`home_goals`,0) ELSE 0 END) + SUM(CASE WHEN all_games.`away_team` = last_games.team_id THEN COALESCE(`all_games`.`away_goals`, 0) ELSE 0 END) AS 'GF' , SUM(CASE WHEN all_games.`home_team` = last_games.team_id THEN COALESCE(`all_games`.`away_goals`,0) ELSE 0 END) + SUM(CASE WHEN all_games.`away_team` = last_games.team_id THEN COALESCE(`all_games`.`home_goals`, 0) ELSE 0 END) AS 'GA' FROM all_games INNER JOIN ( SELECT DISTINCT b.`team_id` , b.`team_name` , SUBSTRING_INDEX( GROUP_CONCAT(a.`all_games_id` ORDER BY a.`date` DESC) ,',',6 ) AS 'last_games_ids' FROM all_games a INNER JOIN teams b ON a.`away_team` = b.`team_id` OR a.`home_team` = b.team_id LEFT JOIN seasons as S ON a.date BETWEEN S.season_start AND S.season_end WHERE a.comp = '1' AND S.season_id = 104 GROUP BY b.team_id ) last_games ON FIND_IN_SET(all_games.`all_games_id`, last_games.last_games_ids) >= 1 GROUP BY last_games.team_id ORDER BY P DESC, GD DESC , GF DESC;

I quick test of this, via my phone, seems to suggest this is going to work! If you look at http://www.margate-fc.com/content/test/cform3.php most of the teams have a 6 in the 'P' column (the ones that don't have a 6 have got less than 6 games in the database). I will have a proper look when I get home but it is looking very promising. Thanks as always Steve

In answer to both of you, in no particular order.......... ebmigue - It is getting close but I am still having a problem! If I use your query leaving out WHERE last_games.`team_name` LIKE '%SUTTON%' I get a table that shows each teams name 6 times so that part of it works (http://www.margate-fc.com/content/test/cform2.php). If I add GROUP BY last_games.team_id just before the ORDER BY statement, it only shows each teams name once so the grouping is fine (http://www.margate-fc.com/content/test/cform3.php). However, no matter where I put the aggregate statement, it results in an empty table but I think that the problem is just my confusion and maybe I am using the wrong aliases. If I change my strings to $ht = "a.home_team = b.team_id"; $at = "a.away_team = b.team_id"; And add the aggregate to find the number of games played ) AS 'last_games_ids' , count(CASE WHEN (".$ht." OR ".$at.") THEN 1 ELSE 0 END) as GP FROM all_games a the theory should be that the 'P' column on the page should have '6' in it for every team but the column is showing up blank. If I can work out how to get that part working correctly at least, then I can move on with all the other aggregates and sorting by date descending (which all should be fairly simple once I have it working). fenway - I revisited that link and have tried to implement it but I have got myself confused again. As I understand it, the grouper would be t.team_id as it is the teams that need to be grouped. The t_limiter would be all_games as that is where the dates are contained and the date is vital (IMO) to get the last 6 games by team. In the WHERE statement, I have translated l.grouper = dl.grouper to WHERE l.team_id IN (dl.home_team,dl.away_team) and l.id = mid to l.date= dl.date But once again, it just returns an empty table. I even stripped the whole thing bare and started again with just the all_games table and still couldn't get that to work. It is frustrating as looking at what I am trying to do seems fairly simple and is used on soccer sites everywhere and looking at that example you posted I can understand the logic and how it works, but for the life of me, I just cannot get it to work the way I need. Thanks to both of you for your continuing patience and help. Steve

I will try it after work and let you know how I get on. Steve

I did try to incorporate it, but could not work out which parts of which table needed to be in the subquery. I tried all sorts of combinations but all returned a blank result. Steve

Sorry for the double quick posting but just wanted to clarify that it is the last 6 games for each team that I am trying to get so in theory, it would be ORDER BY date DESC LIMIT 6 for each team. Steve

Thanks also Fenway. I think the problem I am having is that all the instructions that have been offered, or i have found, have dealt with just one table but this one involves three. Below is a trimmed version of the query to make it easier to work with and I have attached a dump of the tables and data. Thanks again Steve $i = 1; /*ADD th, st, rd TO THE POSITION NUMBERS*/ /* * @return number with ordinal suffix * @param int $number * @param int $ss Turn super script on/off * @return string */ function ordinalSuffix($i, $ss=0) { /*** check for 11, 12, 13 ***/ if ($i % 100 > 10 && $i %100 < 14) { $os = 'th'; } /*** check if number is zero ***/ elseif($i == 0) { $os = ''; } else { /*** get the last digit ***/ $last = substr($i, -1, 1); switch($last) { case "1": $os = 'st'; break; case "2": $os = 'nd'; break; case "3": $os = 'rd'; break; default: $os = 'th'; } } /*** add super script ***/ $os = $ss==0 ? $os : '^'.$os.''; /*** return ***/ return $i.$os; }; /*GET THE CORRECT SEASON*/ if (isset($_GET['season_id']) ? $_GET['season_id'] : 1); $season_id = $_GET['season_id']; /*COMMON STANDINGS QUERY STRINGS*/ $ht = "g.home_team = t.team_id"; $at = "g.away_team = t.team_id"; $hw = "g.home_goals > g.away_goals"; $aw = "g.home_goals < g.away_goals"; $d = "g.home_goals = g.away_goals"; $hg ="g.home_goals"; $ag ="g.away_goals"; /*THE STANDINGS QUERY*/ $table = mysql_query("SELECT *, t.team_name as Tm , @rownum := @rownum+1 AS rank , ((sum(CASE WHEN (".$ht." AND ".$hw.")OR(".$at." AND ".$aw.") THEN 3 ELSE 0 END) + sum(CASE WHEN (".$ht." OR ".$at.") AND ".$d." THEN 1 ELSE 0 END))) AS P , count(CASE WHEN (".$ht." OR ".$at.") THEN 1 ELSE 0 END) as GP , sum(CASE WHEN (".$ht." AND ".$hw.") OR (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS W , sum(CASE WHEN (".$ht." AND ".$d.") OR (".$at." AND ".$d.") THEN 1 ELSE 0 END) AS D , sum(CASE WHEN (".$ht." AND ".$aw.") OR (".$at." AND ".$hw.") THEN 1 ELSE 0 END) AS L , SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END) as GF , SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END) as GA , (SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END) - SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END)) as GD from teams t left join all_games g on t.team_id in (g.home_team,g.away_team) LEFT JOIN seasons as S ON g.date BETWEEN S.season_start AND S.season_end LEFT JOIN deductions d ON ( d.team = t.team_id) AND (d.season = S.season_id) WHERE comp = '1' AND home_goals IS NOT NULL AND S.season_id = $season_id GROUP BY t.team_id ORDER BY P desc, GD desc, GF desc "); [attachment deleted by admin]

Apologies for the double quick post. I realised that I had put more code in the html file than was needed and also thought that it may be easier if code for just the bit that shows all games may be easier to work with so I have attached another version if that is better. Steve [attachment deleted by admin]

Thanks ebmigue. I have attached an sql dump of the relevant tables and data, a text file with the the query and bits I have put into strings and the html of the bit that is being output. All have been the sql and html files have been given a .txt extension as it wouldn't let me attach them otherwise! An online test version (ignore the formatting) of the page can be found at http://follyball.co.uk/oss/standings.php. Basically, the page currently shows the standings of a soccer league with three sections, games where the team was the home side, games where the team was the visiting (away) side and all of the teams games. The home and away parts are just for reference and it is the 'All' games part where any sorting is done (firstly by points descending). What I am trying to achieve is to show the standings for just the last 6 (or any number) games for each team. It cannot be filtered by date as the games are not always played on the same day so some teams may have their 6 games go back over the last 6 weeks and some may have played 6 games in the last 3 weeks. In the table the 'P' column is games played and as you can see some teams by this point had played 20+ games while others had only played 15. In a nutshell I need to get each team, find the 6 most recent games for each of those teams, and then sort the standings based on those results. The nearest I ever got to making it work was for it to select the most recent 6 games from the database and not the most recent for each of the teams. My guess is that it would need to get the last 6 games for each team before doing any grouping but I am probably miles out! Thanks in advance for any suggestions. Steve [attachment deleted by admin]

Absolutely really. I have tried to implement so many different suggestions that I have stumbled across through searching but none of them have worked. Steve

Unfortunately not. I have been searching around again recently and although it seems quite a simple task, none of the solutions that I have tried have worked. Steve

Could anyone point me in the direction of a couple of tutorials? I have searched around but as they often give conflicting advice, I wondered if there were any definitive guides? Login System - with simple registration, admin backend, multiple user levels and restrictive access to pages or sections of pages (for example, guest may be blocked from seeing certain pages while only admins would see an 'edit this page' button. One complication I would need to overcome would be that, in addition to user level restrictions, there may be other restrictions such as certain members of different levels may need to access a downloads area. PhpAdmin style checkboxes - the ability to add, delete or edit multiple records based on checkbox selections. Thanks in advance Steve

Thanks for that. I will have a little play with it later. To be honest, at the moment, they would be used just for my reference, but I would, in the future, possibly use it to show a list of recently updated pages. Thanks again Steve

Can anyone point me in the direction of a good tutorial on Date Created and Date Updated fields? Quite simply they would need to do what it says on the tin, ie. one field automatically updates with the date the record was created and the other automatically updates with the date the record was updated. As a further complication, as it will be used on an existing site, I would ideally like to be able to manually update the Date Created field if required, although an additional field where a date could be manually entered and supercede the Date Created in queries would be a way around this. Thanks in advance Steve

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

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

Thanks for all the suggestions. Through tired eyes and head I was having a bit of a mental block last night. I ended up knocking together a very simple script using MySql that does just the job I am looking for at the moment. Steve

That looks like it may well be just what I was after but will have to wait until after work tomorrow to try it as it is time for bed! Thanks Steve

That is the plan eventually (in fact I will hit it as soon as I next get some time of work), but for now I just need to get something simple set up that he can try in the next couple of days and be happy with, with very little time to spend on it. Having said that, if it is just as easy to put it in a database then I will happily do it that way. If I had 2 fields ('Username' and 'Password') would it be as simple as changing $username = "user"; $password = "pass"; to $username = "SELECT username from table"; $password = "SELECT password from table"; or would there be a bit more to it than that? All of the examples I have seen are more complex and involve separate log-in pages but I would rather (for now) have it all in the one page. Thanks Steve