stb74

Not sure whether this will help any but I thought that after I generate the table data I sort the league table on $PPoints ( Previous weeks points) I maybe run the data through a foreach and add in a Position number for each team.

Not sure whether the subject is correct but hopefully I can explain it ok. I have a football/soccer League table that after I generate I run a foreach to display the league table, within this I add a position counter. I end up with something like this Position Team P W D L F A Pts 1 Malachians 11 6 3 2 24 16 21 2 Killyleagh YC 7 5 1 1 16 9 16 3 Newington YC 7 4 3 0 21 12 15 4 Comber Rec 9 4 3 2 20 22 15 5 Wellington Rec 8 3 3 2 17 16 12 I am now looking to add another position column but show the previous weeks position. This is my code to generate the table data foreach ($query->result() as $team) { $_wins = $this->getWins($team->ID_TEAM,$ID_DIVISION,$ID_SEASON); $_draws = $this->getDraws($team->ID_TEAM,$ID_DIVISION,$ID_SEASON); $_previous_wins = $this->getPreviousWins($team->ID_TEAM,$ID_DIVISION,$ID_SEASON); $_previous_draws = $this->getPreviousDraws($team->ID_TEAM,$ID_DIVISION,$ID_SEASON); $_loses = $this->getLoses($team->ID_TEAM,$ID_DIVISION,$ID_SEASON); $_goalsfor = $this->getGoalsFor($team->ID_TEAM,$ID_DIVISION,$ID_SEASON); $_goalsagainst = $this->getGoalsAgainst($team->ID_TEAM,$ID_DIVISION,$ID_SEASON); $_goaldiff = ($_goalsfor - $_goalsagainst ); $_played = ($_wins + $_draws + $_loses); $_deductions = $this->getDeductions($team->ID_TEAM,$ID_DIVISION,$ID_SEASON); $_tpoints = $_deductions + (($_wins*$_points_for_win) + ($_draws*$_points_for_draw)); $_previous_points = $_deductions + (($_previous_wins*$_points_for_win) + ($_previous_draws*$_points_for_draw)); $_data[$team->ID_TEAM] = array("ID_TEAM"=> $team->ID_TEAM, "team_name"=> $team->team_name, "Played"=> $_played, "Wins"=> $_wins, "Draws"=> $_draws, "Loses"=> $_loses, "GoalsFor"=> $_goalsfor, "GoalsAgainst"=>$_goalsagainst, "GoalDiff"=> $_goaldiff, "Points"=> $_tpoints, "PPoints"=> $_previous_points, "Deductions"=> $_deductions ); } $_data = sortOldTable($_data);

I had to move onto other things, but now I am back to get this working. bluejay002 I do have a table to maintain history and use seasonID to link them together Table 1 ID_DIVISION division_name abbrev Link Table ID_LINK ID_DIVISION ID_SEASON ID_LEAGUE division_line I created the link table as each season the division_line can change, this new table allowed me to cater for that. But as I said before now with some restructuring the division_name can change. What I have tried to do is create another table, name Link Table ID_LINK ID_DIVISION ID_SEASON division_name I have one entry in here and it contains the new name for the division I wanted changed. I could probably combine the two link tables. With the following query SELECT d.division_name, dc.division_name FROM divisions d LEFT JOIN division_changes dc ON ( d.ID_DIVISION = dc.division_id AND dc.season_id = '4' ) JOIN division_details dt ON ( d.ID_division = dt.ID_DIVISION ) JOIN leagues L ON ( d.ID_LEAGUE = L.ID_LEAGUE ) WHERE DT.ID_LEAGUE = '9' AND DT.ID_SEASON = '4' Returns d.division_name dc.division_name Premier League IFA Premiership First Division IFA Championship Reserve League NULL IFA Youth Section A NULL IFA Youth Section B NULL Which is what I am looking for, but my question now is, can I return d.division_name for the ones that don't have a dc.division_name and vice versa. Or do I have to use php to display the appropriate names So I am returning division_name IFA Premiership IFA Championship Reserve League IFA Youth Section A IFA Youth Section B

Not to worry hopefully I get this right my version in brackets, French Championships(league) Elite (Division) Nationale A (Division) Nationale B (Division) Each Season the names do not change, this was like my system there had been no changes, then the last two seasons the league has decided to restructure and rename the Elite Nationale A and remove Nationale B. The problem I have is that if I change the name it means that for each season the name will be the new name, what I would like to have is last season it stays Elite and Nationale A and this season I can have the new name.

Zwiter Do you follow football

I have a soccer/football website and have a DB table for divisions ID_DIVISION ID_LEAGUE division_name abbrev divisions_line 1 1 Premier Division PremierDivision 1,12 The names and division lines never changed up until last year so I added another DB table called division_details ID_LINK ID_DIVISION ID_SEASON ID_LEAGUE division_line The divisions DB table hasa structure of ID_DIVISION division_name abbrev This enables me to use the division_details DB table to have different data each season as with new seasons there may be more divisions or the division_lines may be different each season. What has happened this year is that One of the leagues has changed its division structure which means that if I change the name of the division under the divisions DB table, it will change the name for all seasons. What I am looking help for is the best way to have the DB tables, I can move the division_name and abbrev into the division_details DB but then that will leave one field in divisions. Apologies about the explaination.

I always wondered why that was doing it that way now I understand. I am building up quite a large stats site for football teams and I have still a long way to go LOL. ??? Thanks for the help.

I do seem to make things a little harder for myself, particular on my site.

Excellent easy when you know how.

To save you some hassle, I have done a compromise but I am not sure whether its the best way to do it. I have kept both queries and then combined them using array_merge.

My first query will get the list of 11 players that started the game, the second query will get the list of substitutes. I am looking to build a query that will give me the one list.

I am trying to get ID_Player, Firstname and Lastname from the core_players table for the players that have played in a particular match (23000). These players will be in the starting 11 (core_player_appearances) or subs (core_player_substitutes).

-- -------------------------------------------------------- -- -- Table structure for table `core_player_appearances` -- CREATE TABLE `core_player_appearances` ( `ID_APPEARANCE` int(10) unsigned NOT NULL auto_increment, `ID_SEASON` mediumint(10) NOT NULL default '0', `ID_MATCH` int(10) NOT NULL default '0', `ID_TEAM` int(10) NOT NULL default '0', `ID_PLAYER` int(10) NOT NULL default '0', PRIMARY KEY (`ID_APPEARANCE`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=772 ; -- -- Dumping data for table `core_player_appearances` -- INSERT INTO `core_player_appearances` VALUES (761, 2, 23000, 405, 11); INSERT INTO `core_player_appearances` VALUES (762, 2, 23000, 405, 30); INSERT INTO `core_player_appearances` VALUES (763, 2, 23000, 405, 9); INSERT INTO `core_player_appearances` VALUES (764, 2, 23000, 405, 33); INSERT INTO `core_player_appearances` VALUES (765, 2, 23000, 405, 24); INSERT INTO `core_player_appearances` VALUES (766, 2, 23000, 405, 16); INSERT INTO `core_player_appearances` VALUES (767, 2, 23000, 405, 28); INSERT INTO `core_player_appearances` VALUES (768, 2, 23000, 405, 41); INSERT INTO `core_player_appearances` VALUES (769, 2, 23000, 405, 2); INSERT INTO `core_player_appearances` VALUES (770, 2, 23000, 405, 12); INSERT INTO `core_player_appearances` VALUES (771, 2, 23000, 405, 4); -- -------------------------------------------------------- -- -- Table structure for table `core_player_substitutes` -- CREATE TABLE `core_player_substitutes` ( `ID_SUBSTITUTE` int(10) unsigned NOT NULL auto_increment, `ID_SEASON` mediumint(10) NOT NULL default '0', `ID_MATCH` int(10) NOT NULL default '0', `ID_TEAM` int(10) NOT NULL default '0', `ID_PLAYER` int(10) NOT NULL default '0', PRIMARY KEY (`ID_SUBSTITUTE`), KEY `ID_SEASON` (`ID_SEASON`,`ID_MATCH`,`ID_PLAYER`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=147 ; -- -- Dumping data for table `core_player_substitutes` -- INSERT INTO `core_player_substitutes` VALUES (144, 2, 23000, 405, 35); INSERT INTO `core_player_substitutes` VALUES (145, 2, 23000, 405, 39); INSERT INTO `core_player_substitutes` VALUES (146, 2, 23000, 405, ; -- -------------------------------------------------------- -- -- Table structure for table `core_players` -- CREATE TABLE `core_players` ( `ID_PLAYER` int(10) NOT NULL auto_increment, `ID_SEASON` mediumint(10) NOT NULL default '0', `ID_CLUB` int(10) NOT NULL default '0', `firstname` varchar(128) NOT NULL default '', `lastname` varchar(128) NOT NULL default '', `number` tinyint(3) NOT NULL default '0', `position` varchar(10) NOT NULL default '', `profile` text NOT NULL, `image` text NOT NULL, `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`ID_PLAYER`), KEY `ID_CLUB` (`ID_CLUB`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=59 ; -- -- Dumping data for table `core_players` -- INSERT INTO `core_players` VALUES (1, 2, 1, 'Stephen', 'Hewitt', 0, 'Goalkeeper', 'Egit', '', '2008-02-26 11:33:30'); INSERT INTO `core_players` VALUES (2, 2, 1, 'Stewart', 'Finn', 0, 'Defender', '', '', '2008-02-09 07:03:38'); INSERT INTO `core_players` VALUES (3, 2, 1, 'Ricky', 'Shuttleworth', 0, 'Defender', '', '', '2008-02-09 07:03:58'); INSERT INTO `core_players` VALUES (4, 2, 1, 'Gavin', 'Fox', 0, 'Defender', '', '', '2008-02-09 07:04:07'); INSERT INTO `core_players` VALUES (5, 2, 1, 'John', 'Hunter', 0, 'Midfielder', '', '', '2008-02-09 07:04:17'); INSERT INTO `core_players` VALUES (6, 2, 1, 'Chris', 'Monahan', 0, 'Midfielder', '', '', '2008-02-09 07:04:46'); INSERT INTO `core_players` VALUES (7, 2, 1, 'Tomas', 'McCann', 0, 'Midfielder', '', '', '2008-02-09 07:05:01'); INSERT INTO `core_players` VALUES (8, 2, 1, 'Eamon', 'McCann', 0, 'Midfielder', '', '', '2008-02-09 07:05:12'); INSERT INTO `core_players` VALUES (9, 2, 1, 'Chris', 'Carberry', 0, 'Forward', '', '', '2008-02-09 07:05:27'); INSERT INTO `core_players` VALUES (10, 2, 1, 'Peter', 'Sharvin', 0, 'Forward', '', '', '2008-02-09 07:05:41'); INSERT INTO `core_players` VALUES (11, 2, 1, 'Chris', 'Beattie', 0, 'Midfielder', '', '', '2008-02-09 07:05:57'); INSERT INTO `core_players` VALUES (12, 2, 1, 'John', 'Finnegan', 0, 'Forward', '', '', '2008-02-09 07:06:18'); INSERT INTO `core_players` VALUES (13, 2, 1, 'Martin', 'McKenna', 0, 'Defender', '', '', '2008-02-09 07:07:37'); INSERT INTO `core_players` VALUES (14, 2, 1, 'Sean', 'Tunney', 0, 'Defender', 'Club Trainer', '', '2008-02-27 05:03:24'); INSERT INTO `core_players` VALUES (15, 2, 1, 'Niall', 'McNulty', 0, 'Midfielder', '', '', '2008-02-09 07:06:55'); INSERT INTO `core_players` VALUES (16, 2, 1, 'Paul', 'Daly', 0, 'Midfielder', '', '', '2008-02-09 07:07:54'); INSERT INTO `core_players` VALUES (17, 2, 1, 'Micheal', 'McCann', 0, 'Forward', '', '', '2008-02-09 07:08:02'); INSERT INTO `core_players` VALUES (18, 1, 1, 'Paul', 'McMullan', 0, 'G', '', '', '2008-02-04 15:12:56'); INSERT INTO `core_players` VALUES (19, 1, 1, 'James', 'Oguz', 0, 'F', '', '', '2008-02-04 15:13:30'); INSERT INTO `core_players` VALUES (20, 1, 1, 'Steven', 'Hollywood', 0, 'F', '', '', '2008-02-04 15:14:09'); INSERT INTO `core_players` VALUES (21, 1, 1, 'Andrew', 'Mitchell', 0, 'M', '', '', '2008-02-04 15:14:52'); INSERT INTO `core_players` VALUES (22, 2, 1, 'Phil', 'McManus', 0, 'D', '', '', '2008-02-04 04:45:19'); INSERT INTO `core_players` VALUES (23, 2, 1, 'Michael', 'Teague', 0, 'M', '', '', '2008-02-04 04:48:32'); INSERT INTO `core_players` VALUES (24, 2, 1, 'Ronan', 'Cullen', 0, 'M', '', '', '2008-02-04 04:50:03'); INSERT INTO `core_players` VALUES (25, 2, 1, 'Ciaran', 'Gorman', 0, 'M', '', '', '2008-02-04 16:50:28'); INSERT INTO `core_players` VALUES (26, 2, 1, 'Marc', 'McGerty', 0, 'D', '', '', '2008-02-04 16:51:02'); INSERT INTO `core_players` VALUES (27, 1, 1, 'Brian', 'Teggart', 0, 'G', '', '', '2008-02-04 16:51:24'); INSERT INTO `core_players` VALUES (28, 2, 1, 'Eamonn', 'Devlin', 0, 'M', '', '', '2008-02-04 16:51:51'); INSERT INTO `core_players` VALUES (29, 1, 1, 'Emmanuel', 'Wilkinson', 0, 'F', '', '', '2008-02-04 16:52:16'); INSERT INTO `core_players` VALUES (30, 2, 1, 'Pablo', 'Berrueco', 0, 'F', '', '', '2008-02-04 16:52:35'); INSERT INTO `core_players` VALUES (31, 2, 1, 'Brian', 'Madden', 0, 'D', '', '', '2008-02-04 16:52:58'); INSERT INTO `core_players` VALUES (32, 2, 1, 'Mark', 'Mullan', 0, 'D', '', '', '2008-02-04 16:53:23'); INSERT INTO `core_players` VALUES (33, 2, 1, 'Mark', 'Coyles', 0, 'D', '', '', '2008-02-04 16:53:48'); INSERT INTO `core_players` VALUES (34, 2, 1, 'Joe', 'McKevitt', 0, 'D', '', '', '2008-02-04 16:54:16'); INSERT INTO `core_players` VALUES (35, 2, 1, 'Peter', 'Johnston', 0, 'F', '', '', '2008-02-04 17:04:28'); INSERT INTO `core_players` VALUES (36, 2, 1, 'Niall', 'McLoughlin', 0, 'M', '', '', '2008-02-04 05:05:07'); INSERT INTO `core_players` VALUES (37, 2, 1, 'Conor', 'O''Neill', 0, 'M', '', '', '2008-02-04 17:05:41'); INSERT INTO `core_players` VALUES (38, 2, 1, 'David', 'Hamilton', 0, 'F', '', '', '2008-02-04 17:06:20'); INSERT INTO `core_players` VALUES (39, 2, 1, 'Joe', 'Kennedy', 0, 'D', '', '', '2008-02-04 17:06:53'); INSERT INTO `core_players` VALUES (40, 1, 1, 'Cormac', 'McGarry', 0, 'M', '', '', '2008-02-04 17:07:43'); INSERT INTO `core_players` VALUES (41, 2, 1, 'David', 'Doherty', 0, 'G', '', '', '2008-02-04 17:08:08'); INSERT INTO `core_players` VALUES (42, 2, 1, 'Conor', 'McSteen', 0, 'F', '', '', '2008-03-16 18:50:07'); INSERT INTO `core_players` VALUES (43, 2, 1, 'Steve', 'Moore', 0, 'D', '', '', '2008-02-04 17:09:31'); INSERT INTO `core_players` VALUES (44, 1, 1, 'Barry', 'Meehan', 0, 'F', '', '', '2008-02-04 17:12:38'); INSERT INTO `core_players` VALUES (45, 2, 7, 'R', 'Galbraith', 0, '', '', '', '2008-02-29 13:10:26'); INSERT INTO `core_players` VALUES (46, 2, 7, 'S', 'Williamson', 0, '', '', '', '2008-02-29 13:10:26'); INSERT INTO `core_players` VALUES (47, 2, 7, 'T', 'Beckett', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (48, 2, 7, 'R', 'McComb', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (49, 2, 7, 'P', 'Magee', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (50, 2, 7, 'D', 'Gallagher', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (51, 2, 7, 'P', 'Murphy', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (52, 2, 7, 'G', 'Beattie', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (53, 2, 7, 'C', 'Rea', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (54, 2, 7, 'A', 'Cardwell', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (55, 2, 7, 'P', 'McKee', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (56, 2, 7, 'Sub', '1', 0, 'Midfielder', '', '', '2008-03-20 14:49:42'); INSERT INTO `core_players` VALUES (57, 2, 7, 'Sub', '2', 0, 'Forward', '', '', '2008-03-20 14:49:57'); INSERT INTO `core_players` VALUES (58, 2, 1, 'test', 'test', 0, 'Defender', '', '', '2008-03-21 21:02:39');

Too early in the morning for this but whats DDL.

If I run two query's individually I get the correct results but I need to return the results from one query but can't seem to get them joined correctly. The following will get 11 match players SELECT P.ID_PLAYER, concat( P.firstname, ' ', P.lastname ) FROM core_players P JOIN core_player_appearances A ON ( P.ID_PLAYER = A.ID_PLAYER AND A.ID_SEASON =2 AND A.ID_MATCH =22998 ) WHERE P.ID_CLUB =1 The following will get the match substitutes SELECT P.ID_PLAYER, concat( P.firstname, ' ', P.lastname ) FROM core_players P JOIN core_player_substitutes S ON ( P.ID_PLAYER = S.ID_PLAYER AND S.ID_SEASON =2 AND S.ID_MATCH =22998 ) WHERE P.ID_CLUB =1 I just can't seem to work out what the correct terminology is when combining the two, maybe you can't do it. SELECT P.ID_PLAYER, concat( P.firstname, ' ', P.lastname ) FROM core_players P JOIN core_player_appearances A ON ( P.ID_PLAYER = A.ID_PLAYER AND A.ID_SEASON =2 AND A.ID_MATCH =22998 ) JOIN core_player_substitutes S ON ( P.ID_PLAYER = S.ID_PLAYER AND S.ID_SEASON =2 AND S.ID_MATCH =22998 ) WHERE P.ID_CLUB =1 The Above returns nothing, can someone please point out my blinding mistake, cheers.