MargateSteve
-
Posts
240 -
Joined
-
Last visited
Posts posted by MargateSteve
-
-
I have tried everything over the last few days to try to get this to work but to no avail. I have even looked into using MAX to see if there was a way of selecting the MAX 5 dates then sorting them in ascending order but it seems as though MAX is restricted to just one record.
I have seen this done on several sites so unless they are using a different language there must be a way to do it. It does seem a simple thing in principle but I just cannot get it to work!
Steve
-
Query to show fields from All games Team Page table failed
It is really puzzling as if I just use the subquery part it works fine but once the outer select is put in, it just does not work. I cannot see any reason why it wont work.
The tables, with some sample data are
CREATE TABLE `all_games` ( `all_games_id` int(11) NOT NULL auto_increment, `date` date default NULL, `time` time default NULL, `comp` int(11) NOT NULL default '1', `round` tinyint(4) default NULL, `replay` char(1) default NULL, `home_team` int(11) NOT NULL default '1', `away_team` int(11) NOT NULL default '1', `home_goals` int(11) default NULL, `away_goals` int(11) default NULL, `home_ht` int(11) default NULL, `away_ht` int(11) default NULL, `attendance` int(11) default NULL, PRIMARY KEY (`all_games_id`) ) TYPE=MyISAM AUTO_INCREMENT=732 ; INSERT INTO `all_games` VALUES (1, '2009-08-15', '15:00:00', 1, NULL, '0', 19, 42, 4, 0, 0, 0, 508); INSERT INTO `all_games` VALUES (2, '2009-08-18', '19:45:00', 1, NULL, '0', 42, 29, 0, 4, 0, 0, 653); INSERT INTO `all_games` VALUES (3, '2009-08-22', '15:00:00', 1, NULL, '0', 42, 30, 2, 1, 0, 0, 345); INSERT INTO `all_games` VALUES (4, '2009-08-24', '19:45:00', 1, NULL, '0', 1, 42, 0, 3, 0, 0, 243); INSERT INTO `all_games` VALUES (5, '2009-08-29', '15:00:00', 1, NULL, '0', 11, 42, 3, 0, 0, 0, 156); INSERT INTO `all_games` VALUES (6, '2009-08-31', '15:00:00', 1, NULL, '0', 42, 7, 0, 1, 0, 0, 423); INSERT INTO `all_games` VALUES (7, '2009-09-05', '15:00:00', 1, NULL, '0', 31, 42, 2, 2, 0, 0, 120); INSERT INTO `all_games` VALUES (8, '2009-09-12', '15:00:00', 2, 2, '0', 42, 19, 2, 2, 0, 0, 402); INSERT INTO `all_games` VALUES (9, '2009-09-15', '19:45:00', 2, 2, 'r', 19, 42, 3, 2, 0, 0, 301); INSERT INTO `all_games` VALUES (10, '2009-09-19', '15:00:00', 1, NULL, '0', 42, 15, 1, 3, 0, 0, 356); INSERT INTO `all_games` VALUES (11, '2009-09-22', '19:45:00', 1, NULL, '0', 13, 42, 1, 2, 0, 0, 159); CREATE TABLE `competitions` ( `comp_id` int(11) NOT NULL auto_increment, `comp_name` varchar(45) default NULL, `comp_short_name` varchar(4) default NULL, `logo` varchar(255) NOT NULL default '', PRIMARY KEY (`comp_id`) ) TYPE=MyISAM AUTO_INCREMENT=8 ; INSERT INTO `competitions` VALUES (1, 'Isthmian League Premier Division', 'ILP', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/tRymanLeague.png'); INSERT INTO `competitions` VALUES (2, 'FA Cup', 'FAC', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/facup.png'); INSERT INTO `competitions` VALUES (3, 'FA Trophy', 'FAT', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/fattophy.png'); INSERT INTO `competitions` VALUES (4, 'Kent Senior Cup', 'KSC', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/kentseniorcup.png'); INSERT INTO `competitions` VALUES (5, 'Isthmian League Cup', 'ILC', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/isthmianleaguecup.png'); INSERT INTO `competitions` VALUES (6, 'Friendly', 'FR', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/friendly.png'); INSERT INTO `competitions` VALUES (7, 'Kent Messenger Cup', 'KMC', ''); CREATE TABLE `teams` ( `team_id` int(11) NOT NULL auto_increment, `team_name` varchar(45) default NULL, `short_name` varchar(5) default NULL, `badge` varchar(255) default NULL, `year_formed` int(11) default NULL, `nickname` varchar(255) default NULL, `previous_names` varchar(255) default NULL, `website` varchar(255) default NULL, `twitter` varchar(255) default NULL, `facebook` varchar(255) default NULL, `telephone_number` varchar(255) default NULL, `chairman` varchar(255) default NULL, `manager` varchar(255) default NULL, `home_colours` varchar(255) default NULL, `away_colours` varchar(255) default NULL, `stadium` int(11) default NULL, `2009/10` varchar(255) default NULL, PRIMARY KEY (`team_id`) ) TYPE=MyISAM AUTO_INCREMENT=74 ; INSERT INTO `teams` VALUES (1, 'Aveley', 'Ave', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/aveley.png', 1927, 'The Millers', 'None', 'http://www.aveleyfc.net/', NULL, NULL, '01708865940', 'Graham Gennings', 'Alan Kimble', 'Black & Blue/Blue/Blue', 'Black & White/White/White', 3, 'Isthmian League Premier Division, 3rd'); INSERT INTO `teams` VALUES (2, 'Billericay Town', 'Bill', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/billericaytown.png', 1880, 'Ricay, The Blues', 'None', 'http://www.billericaytownfc.co.uk/', 'http://twitter.com/BTFC', 'http://www.facebook.com/billericaytownfc', '01277652188', 'Steve Kent', 'Craig Edwards', 'Blue/White/Blue', 'White/Black/White', 2, 'Isthmian Premier, 13th'); INSERT INTO `teams` VALUES (3, 'Bury Town', 'Bur', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/burytown.png', 1872, 'The Blues', 'Bury St Edmunds, Bury United', 'http://www.burytownfc.co.uk', NULL, NULL, '01277999999', 'Russell Ward', 'Richard Wilkins', 'Blue & Red/Blue/Blue', 'White/Black/White', 10, 'Southern League Division One Midlands, 1st'); INSERT INTO `teams` VALUES (4, 'Canvey Island', 'Can', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/canveyisland.png', 1926, 'The Gulls', 'None', 'http://www.canveyislandfc.com', 'http://twitter.com/CIFC', NULL, NULL, 'George Frost', 'John Batch', 'Yellow/Blue/White', 'White/White/White', 11, 'Isthmian League Premier Division, 16th'); INSERT INTO `teams` VALUES (5, 'Carshalton Athletic', 'Car', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/carshaltonathletic.png', 1905, 'The Robins', 'Mill Lane Misson', 'http://www.carshaltonathletic.co.uk/', 'http://twitter.com/carshaltonath', NULL, '02086428658', 'Harry Driver', 'Mark Butler', 'Red/Red/Red', 'Maroon/White/Maroon', 9, 'Isthmian League Premier Division, 17th'); INSERT INTO `teams` VALUES (6, 'Concord Rangers', 'Con', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/concordrangers.png', 1967, 'Beach Boys', 'None', 'http://www.concordrangers.co.uk/', 'http://twitter.com/ConcordRangers', NULL, '01268691780', 'Antony Smith', 'Danny Scopes / Danny Cowley', 'Yellow/Blue/Blue', 'TBA', 12, NULL); INSERT INTO `teams` VALUES (7, 'Cray Wanderers', 'Cray', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/craywanderers.png', 1860, 'The Wands', 'None', 'http://www.craywands.co.uk', 'http://twitter.com/craywanderers', NULL, '02084605291', 'Gary Hillman', 'Ian Jenkins', 'Amber/Black/Amber', 'Blue/White/White', 13, 'Isthmian League Premier Division, 15th'); INSERT INTO `teams` VALUES (8, 'Croydon Athletic', 'Croy', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/croydonathletic.png', 1986, 'The Rams', 'Wandsworth & Norwood', 'http://croydonathletic.net', NULL, NULL, '0208664834', 'TBA', 'Dave Garland & Bob Langford', 'Maroon & White/Maroon/Maroon', 'Yellow/Blue/Blue', 14, 'Isthmian League Division One South, 1st'); INSERT INTO `teams` VALUES (9, 'Folkestone Invicta', 'Fol', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/folkestoneinvicta.png', 1936, 'Invicta, The Seasiders', 'None', 'http://www.folkestoneinvicta.co.uk/', 'http://twitter.com/FIFC', NULL, '01303257461', 'TBA', 'Neil Cugley', 'Amber/Black/Amber', 'Red/White/Red', 15, 'Isthmian League Division One South, 2nd'); INSERT INTO `teams` VALUES (10, 'Grays Athletic', 'Gra', 'N/A', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); INSERT INTO `teams` VALUES (11, 'Harrow Borough', 'Har', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/harrowborough.png', 1933, 'The Boro, The Reds', 'Roxoinian, Harrow Town', 'http://www.harrowboro.com/', 'http://twitter.com/harrowboro_fc', NULL, '08706091959', 'Peter Rogers', 'David Howell', 'Red/Red/Red', 'White/White/Black', 16, 'Isthmian League Premier Division, 14th'); INSERT INTO `teams` VALUES (12, 'Hastings United', 'Has', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/hastingsunited.png', 1894, 'The Arrows, The U''s', 'Hastings Town, Hastings & St. Leonards', 'http://www.hastingsunitedfc.co.uk/', 'http://twitter.com/hastingsufc', NULL, '01424444635', 'David Walter', 'Tony Dolby', 'Maroon/Maroon/Maroon', 'White/White/White', 17, 'Isthmian League Premier Division, 7th'); INSERT INTO `teams` VALUES (13, 'Hendon', 'Hen', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/hendon.png', 1908, 'The Greens, The Dons', 'Christ Church Hampstead, Hampstead Town, Hampstead, Golders Green', 'http://www.hendonfc.net/', 'http://twitter.com/HendonFC', NULL, '02089083553', 'Simon Lawrence', 'Gary McCann', 'Green/Green/Green', 'Blue/Blue/Blue', 18, 'Isthmian League Premier Division, 10th'); INSERT INTO `teams` VALUES (14, 'AFC Hornchurch', 'AFC', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/afchornchurch.png', 1923, 'The Urchins', 'Upminster Wanderers, Hornchurch & Upminster', 'http://www.afchornchurch.com/', 'http://twitter.com/AFCHornchurch', 'http://www.facebook.com/AFCHornchurch', '01708220080', 'TBA', 'Colin McBride', 'Red & White/Black/Red', 'Blue & White/Blue/Blue', 1, 'Isthmian League Premier Division, 9th');
The site, if it is relevant, is running PHP 4.4.9 and MySQL 4.0
Steve
-
How would I go about that?
Obviously I can get the right results buy using ORDER BY date DESC LIMIT 5 but i want to print it as ORDER BY date ASC.
I have also tried to amend this example that I have seen elsewhere
SELECT * FROM ( SELECT ... FROM ... ORDER BY ID ASC LIMIT 3 ) AS sq ORDER BY ID DESC
but that game me a 'Query to show fields failed' error.
Steve
-
I am trying to reverse the results of a query in the output but cannot quite get it working. Basically, I am trying to get the most recent five records by date and then echo them in date ascending order.
I have tried using array_reverse but the records still show in descending order.
My query is
$tpall_games2 = mysql_query(" SELECT * , DATE_FORMAT(`date`,'%W, %D %M %Y') AS showdate, HT.team_name as HomeTeam, VT.team_name as AwayTeam, COMP.comp_short_name as CP FROM all_games JOIN teams as HT ON all_games.home_team = HT.team_id JOIN teams as VT ON all_games.away_team = VT.team_id JOIN competitions as COMP ON all_games.comp = COMP.comp_id WHERE $id IN (all_games.home_team, all_games.away_team) AND all_games.home_goals IS NOT NULL ORDER BY `date` DESC LIMIT 5 "); if (!$tpall_games2) { die("Query to show fields from All games Team Page table failed"); };
and the html is
if(!mysql_num_rows($tpall_games2)) { echo "<tr><td style=\"text-align:left\" COLSPAN=\"8\">No records returned</td></tr>"; } else { while ($row_tpall_gamesrev = array_reverse(mysql_fetch_array($tpall_games2, true))){ if ($row_tpall_gamesrev['home_team']==$id) { if ($row_tpall_gamesrev['date']=="") echo '<tr style="font-weight:bold"><td class="lftplcell" >TBA</td>'; else echo '<tr style="font-weight:bold"> <td class="lftplcell" >'.$row_tpall_gamesrev['showdate'].'</td>'; echo' <td class="lftplcell" >.............
I have looked up a lot of array_reverse samples but none of them reversed the results and also tried to do it in a different way using a select subquery but I could not make that work at all.
Is there something simple that I am missing (again!)?
Steve
-
Bingo! And pretty obvious now!
Done, dusted, solved and closed!
Thanks again
Steve
-
Apologies for unsolving this but I thought I would be able to solve the next bit on my own, but couldn't and thought it would be rude to start another thread.
Everything worked fine until I tried to add the extra separation by competition that I mentioned.
I have added a 'comp_id ASC' ORDER BY to the query and have amended the code as such
if ($all_games) { if(mysql_num_rows($all_games)) { $lastdate = 0; while ($row_all_games = mysql_fetch_assoc($all_games)) { // when the date changes output a new heading if($lastdate != $row_all_games['date']) { echo "<br/><b>".strtoupper($row_all_games['date'])."</b><br/>"; $lastdate = $row_all_games['date']; }; // when the competitione changes output a new heading $lastcomp = 0; if($lastcomp != $row_all_games['comp']) { echo "<b>".$row_all_games['comp_name']."</b><br/>"; $lastcomp = $row_all_games['comp']; }; echo $row_all_games['HomeTeam']." v ".$row_all_games['AwayTeam']."<br/>"; } } else { echo "No results!"; } }
Once again I really thought that I had got it dead right but it shows the competition above every game (http://www.margate-fc.com/content/test/stats/games2.php?date1=2011-04-02&date2=2011-04-25&teams=&submit=Filter).
I have run through the processing theory in my head, checked for typos, have double-checked placement of curly brackets and semi-colons and moved things around to see what the problem might be. I have even tried to enclose the whole new competitions bit and tried to combine it with the original as an if/else but cannot get anything to work.
What have I done wrong this time?!?!?!?
Thanks as always
Steve
-
if($lastdate = $row_all_games['date'])
^^^ You buggered up that line by changing the original != comparison operator to an = assignment operator. You are assigning $row_all_games['date'] to the variable $lastdate, not comparing the two.
I knew that it would be something to do with my stupid chunky fingers!! I looked and looked but could not find the error!
Thanks as always
Steve
-
Sorry for the topic title not explaining much, but I did not now how to word it!
I was previous given some code at http://www.phpfreaks.com/forums/php-coding-help/streamlining-dreamweaver-generated-php-code/msg1459677/#msg1459677 that created a header every time a crtieria changed, in this case a players position.
I have tried to tailor this to another page but have got it wrong somewhere. In this instance I was a change of date to be the determining factor. For example, with what I have tried so far, the output is
2011-04-02
Lowestoft Town v Tonbridge Angels
2011-04-02
Tooting & Mitcham United v Maidstone United
2011-04-02
Wealdstone v Concord Rangers
2011-04-09
Hastings United v Margate
2011-04-09
AFC Hornchurch v Carshalton Athletic
2011-04-09
Billericay Town v Aveley
when I actually want
2011-04-02
Lowestoft Town v Tonbridge Angels
Tooting & Mitcham United v Maidstone United
Wealdstone v Concord Rangers
2011-04-09
AFC Hornchurch v Carshalton Athletic
Billericay Town v Aveley
I have tried to adjust the code to reflect the different query etc but something is missing.
Thinking further ahead, there will also be a second criteria, in that games in different competitions could be played on the same day so I would also want to group the output by the competition ('comp' in the table) to get
2011-04-02
League
Lowestoft Town v Tonbridge Angels
Tooting & Mitcham United v Maidstone United
Cup
Wealdstone v Concord Rangers
2011-04-09
League
AFC Hornchurch v Carshalton Athletic
Cup
Billericay Town v Aveley
I would imagine that would just mean drilling down one more level in the code once I get this bit right.
The tables, with a sample of data are
CREATE TABLE `all_games` ( `all_games_id` int(11) NOT NULL auto_increment, `date` date default NULL, `time` time default NULL, `comp` int(11) NOT NULL default '1', `round` tinyint(4) default NULL, `replay` char(1) default NULL, `home_team` int(11) NOT NULL default '1', `away_team` int(11) NOT NULL default '1', `home_goals` int(11) default NULL, `away_goals` int(11) default NULL, `home_ht` int(11) default NULL, `away_ht` int(11) default NULL, `attendance` int(11) default NULL, PRIMARY KEY (`all_games_id`) ) TYPE=MyISAM AUTO_INCREMENT=732 ; INSERT INTO `all_games` VALUES (700, '2011-04-23', '00:00:00', 1, NULL, '', 11, 20, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (701, '2011-04-23', '00:00:00', 1, NULL, '', 12, 13, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (702, '2011-04-23', '00:00:00', 1, NULL, '', 15, 22, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (703, '2011-04-23', '00:00:00', 1, NULL, '', 21, 7, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (704, '2011-04-25', '00:00:00', 1, NULL, '', 1, 14, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (705, '2011-04-25', '00:00:00', 1, NULL, '', 4, 6, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (706, '2011-04-25', '00:00:00', 1, NULL, '', 8, 15, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (707, '2011-04-25', '00:00:00', 1, NULL, '', 13, 2, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (708, '2011-04-25', '00:00:00', 1, NULL, '', 16, 21, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (709, '2011-04-25', '00:00:00', 1, NULL, '', 17, 3, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (710, '2011-04-25', '00:00:00', 1, NULL, '', 18, 9, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (711, '2011-04-25', '00:00:00', 1, NULL, '', 19, 5, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (712, '2011-04-25', '00:00:00', 1, NULL, '', 20, 12, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (713, '2011-04-25', '00:00:00', 1, NULL, '', 22, 11, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (714, '2011-04-30', '00:00:00', 1, NULL, '', 14, 18, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (715, '2011-04-30', '00:00:00', 1, NULL, '', 2, 19, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (716, '2011-04-30', '00:00:00', 1, NULL, '', 3, 22, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (717, '2011-04-30', '00:00:00', 1, NULL, '', 5, 13, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (718, '2011-04-30', '00:00:00', 1, NULL, '', 6, 8, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (719, '2011-04-30', '00:00:00', 1, NULL, '', 9, 17, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (720, '2011-04-30', '00:00:00', 1, NULL, '', 11, 7, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (721, '2011-04-30', '00:00:00', 1, NULL, '', 12, 1, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (722, '2011-04-30', '00:00:00', 1, NULL, '', 15, 20, NULL, NULL, NULL, NULL, NULL); INSERT INTO `all_games` VALUES (723, '2011-04-30', '00:00:00', 1, NULL, '', 21, 4, NULL, NULL, NULL, NULL, NULL); CREATE TABLE `competitions` ( `comp_id` int(11) NOT NULL auto_increment, `comp_name` varchar(45) default NULL, `comp_short_name` varchar(4) default NULL, `logo` varchar(255) NOT NULL default '', PRIMARY KEY (`comp_id`) ) TYPE=MyISAM AUTO_INCREMENT=8 ; INSERT INTO `competitions` VALUES (1, 'Isthmian League Premier Division', 'ILP', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/tRymanLeague.png'); INSERT INTO `competitions` VALUES (2, 'FA Cup', 'FAC', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/facup.png'); INSERT INTO `competitions` VALUES (3, 'FA Trophy', 'FAT', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/fattophy.png'); INSERT INTO `competitions` VALUES (4, 'Kent Senior Cup', 'KSC', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/kentseniorcup.png'); INSERT INTO `competitions` VALUES (5, 'Isthmian League Cup', 'ILC', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/isthmianleaguecup.png'); INSERT INTO `competitions` VALUES (6, 'Friendly', 'FR', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/friendly.png'); INSERT INTO `competitions` VALUES (7, 'Kent Messenger Cup', 'KMC', ''); CREATE TABLE `teams` ( `team_id` int(11) NOT NULL auto_increment, `team_name` varchar(45) default NULL, `short_name` varchar(5) default NULL, `badge` varchar(255) default NULL, `year_formed` int(11) default NULL, `nickname` varchar(255) default NULL, `previous_names` varchar(255) default NULL, `website` varchar(255) default NULL, `twitter` varchar(255) default NULL, `facebook` varchar(255) default NULL, `telephone_number` varchar(255) default NULL, `chairman` varchar(255) default NULL, `manager` varchar(255) default NULL, `home_colours` varchar(255) default NULL, `away_colours` varchar(255) default NULL, `stadium` int(11) default NULL, `2009/10` varchar(255) default NULL, PRIMARY KEY (`team_id`) ) TYPE=MyISAM AUTO_INCREMENT=74 ; INSERT INTO `teams` VALUES (1, 'Aveley', 'Ave', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/aveley.png', 1927, 'The Millers', 'None', 'http://www.aveleyfc.net/', NULL, NULL, '01708865940', 'Graham Gennings', 'Alan Kimble', 'Black & Blue/Blue/Blue', 'Black & White/White/White', 3, 'Isthmian League Premier Division, 3rd'); INSERT INTO `teams` VALUES (2, 'Billericay Town', 'Bill', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/billericaytown.png', 1880, 'Ricay, The Blues', 'None', 'http://www.billericaytownfc.co.uk/', 'http://twitter.com/BTFC', 'http://www.facebook.com/billericaytownfc', '01277652188', 'Steve Kent', 'Craig Edwards', 'Blue/White/Blue', 'White/Black/White', 2, 'Isthmian Premier, 13th'); INSERT INTO `teams` VALUES (3, 'Bury Town', 'Bur', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/burytown.png', 1872, 'The Blues', 'Bury St Edmunds, Bury United', 'http://www.burytownfc.co.uk', NULL, NULL, '01277999999', 'Russell Ward', 'Richard Wilkins', 'Blue & Red/Blue/Blue', 'White/Black/White', 10, 'Southern League Division One Midlands, 1st'); INSERT INTO `teams` VALUES (4, 'Canvey Island', 'Can', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/canveyisland.png', 1926, 'The Gulls', 'None', 'http://www.canveyislandfc.com', 'http://twitter.com/CIFC', NULL, NULL, 'George Frost', 'John Batch', 'Yellow/Blue/White', 'White/White/White', 11, 'Isthmian League Premier Division, 16th'); INSERT INTO `teams` VALUES (5, 'Carshalton Athletic', 'Car', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/carshaltonathletic.png', 1905, 'The Robins', 'Mill Lane Misson', 'http://www.carshaltonathletic.co.uk/', 'http://twitter.com/carshaltonath', NULL, '02086428658', 'Harry Driver', 'Mark Butler', 'Red/Red/Red', 'Maroon/White/Maroon', 9, 'Isthmian League Premier Division, 17th'); INSERT INTO `teams` VALUES (6, 'Concord Rangers', 'Con', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/concordrangers.png', 1967, 'Beach Boys', 'None', 'http://www.concordrangers.co.uk/', 'http://twitter.com/ConcordRangers', NULL, '01268691780', 'Antony Smith', 'Danny Scopes / Danny Cowley', 'Yellow/Blue/Blue', 'TBA', 12, NULL); INSERT INTO `teams` VALUES (7, 'Cray Wanderers', 'Cray', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/craywanderers.png', 1860, 'The Wands', 'None', 'http://www.craywands.co.uk', 'http://twitter.com/craywanderers', NULL, '02084605291', 'Gary Hillman', 'Ian Jenkins', 'Amber/Black/Amber', 'Blue/White/White', 13, 'Isthmian League Premier Division, 15th'); INSERT INTO `teams` VALUES (8, 'Croydon Athletic', 'Croy', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/croydonathletic.png', 1986, 'The Rams', 'Wandsworth & Norwood', 'http://croydonathletic.net', NULL, NULL, '0208664834', 'TBA', 'Dave Garland & Bob Langford', 'Maroon & White/Maroon/Maroon', 'Yellow/Blue/Blue', 14, 'Isthmian League Division One South, 1st'); INSERT INTO `teams` VALUES (9, 'Folkestone Invicta', 'Fol', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/folkestoneinvicta.png', 1936, 'Invicta, The Seasiders', 'None', 'http://www.folkestoneinvicta.co.uk/', 'http://twitter.com/FIFC', NULL, '01303257461', 'TBA', 'Neil Cugley', 'Amber/Black/Amber', 'Red/White/Red', 15, 'Isthmian League Division One South, 2nd'); INSERT INTO `teams` VALUES (10, 'Grays Athletic', 'Gra', 'N/A', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); INSERT INTO `teams` VALUES (11, 'Harrow Borough', 'Har', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/harrowborough.png', 1933, 'The Boro, The Reds', 'Roxoinian, Harrow Town', 'http://www.harrowboro.com/', 'http://twitter.com/harrowboro_fc', NULL, '08706091959', 'Peter Rogers', 'David Howell', 'Red/Red/Red', 'White/White/Black', 16, 'Isthmian League Premier Division, 14th'); INSERT INTO `teams` VALUES (12, 'Hastings United', 'Has', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/hastingsunited.png', 1894, 'The Arrows, The U''s', 'Hastings Town, Hastings & St. Leonards', 'http://www.hastingsunitedfc.co.uk/', 'http://twitter.com/hastingsufc', NULL, '01424444635', 'David Walter', 'Tony Dolby', 'Maroon/Maroon/Maroon', 'White/White/White', 17, 'Isthmian League Premier Division, 7th'); INSERT INTO `teams` VALUES (13, 'Hendon', 'Hen', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/hendon.png', 1908, 'The Greens, The Dons', 'Christ Church Hampstead, Hampstead Town, Hampstead, Golders Green', 'http://www.hendonfc.net/', 'http://twitter.com/HendonFC', NULL, '02089083553', 'TBA', 'Gary McCann', 'Green/Green/Green', 'Blue/Blue/Blue', 18, 'Isthmian League Premier Division, 10th'); INSERT INTO `teams` VALUES (14, 'AFC Hornchurch', 'AFC', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/afchornchurch.png', 1923, 'The Urchins', 'Upminster Wanderers, Hornchurch & Upminster', 'http://www.afchornchurch.com/', 'http://twitter.com/AFCHornchurch', 'http://www.facebook.com/AFCHornchurch', '01708220080', 'TBA', 'Colin McBride', 'Red & White/Black/Red', 'Blue & White/Blue/Blue', 1, 'Isthmian League Premier Division, 9th'); INSERT INTO `teams` VALUES (15, 'Horsham', 'Hor', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/horsham.png', 1871, 'The Hornets', 'None', 'http://www.hornetsreview.co.uk/', NULL, NULL, '01403266888', 'Kevin Borrett', 'John Maggs', 'Yellow & Green/Yellow/Yellow', 'Blue/Black/White', 19, 'Isthmian League Premier Division,11th');
The query is
$all_games = mysql_query("SELECT *, HT.team_name as HomeTeam, VT.team_name as AwayTeam, COMP.comp_short_name as CP FROM all_games JOIN teams as HT ON all_games.home_team = HT.team_id JOIN teams as VT ON all_games.away_team = VT.team_id JOIN competitions as COMP ON all_games.comp = COMP.comp_id ORDER BY date ASC "); if (!$all_games) { die("Query to show fields from table failed");
And the code that echoes the right values, but does not seperate the the dates is
if($all_games) { if(mysql_num_rows($all_games)) { $lastdate = 0; while ($row_all_games = mysql_fetch_assoc($all_games)) { // when the date changes output a new heading if($lastdate = $row_all_games['date']) { echo "<h1>".strtoupper($row_all_games['date'])."</h1>"; $lastdate = $row_all_games['date']; } echo $row_all_games['HomeTeam']." v ".$row_all_games['AwayTeam']; } } else { echo "No results!"; } }
I must admit that I have tried a few variations but the nearest I have got is for the page to echo "No results!"
I may be a fraction off getting it right or a million miles away so if anyone can give any pointers I will be very grateful.
Thanks in advance
Steve
-
I am not at the stage where I have the queries ready, but am just thinking ahead. There is, however, on query that I had help with at http://www.phpfreaks.com/forums/php-coding-help/using-a-generated-row-number-in-another-query that is a good example.
In that post I mentioned a League Standings Table. I will have the whole standings in one page whilst, as per that thread, on each teams individual page I would want a smaller version based around that teams rank. Elsewhere I will want a version that only shows records for the last five games of each team, versions that will show just the top five or bottom five teams and various ORDER BY and WHERE variations.
So even with that query there will be 6 or 7 slight variations around the site.
I have not got into functions yet but if that is the right way to go I will fixed my attention to understanding them.
Steve
-
Following on from the excellent help in the thread at http://www.phpfreaks.com/forums/php-coding-help/using-a-generated-row-number-in-another-query I have another question regarding the League Standings that are generated there.
I want to have another version that limits the records to the last 5 games for each team. Just using 'LIMIT 5' would return the first five teams and all their records but I want it to retrieve all of the teams and each teams last five records (games). The easy way would be to restrict a the dates but as games get postponed and moved using WHERE with a specific date would not guarantee the same number of games for each team.
There is the possibility that this could be done via the query but I have not come across anything in some quite extensive searching (although, once again, I do not know if I am using the right search terms!) so I assume PHP would, once again be the saviour!
Thanks in advance for any suggestions.
Steve
-
Eventually I will have around 5 or 6 queries that will be replicated in several different pages, albeit with a slight difference in each (generally just a change in the WHERE part).
I have been thinking about placing these common queries in a separate file and calling that file with an 'include' on the pages the query is needed.
My two questions are....
Would this be more beneficial than place the query in the page itself and would there be any downside (as in loading times) if I placed all of these queries in one external file (ie. would this cause all the queries to load each time.
Thanks in advance for any advice.
Steve
-
Perfect!!!! Pikachu, you are a star!
I knew it was something simple but just could not see it!
Thanks for the help
Steve
-
Wouldn't I only need to do that if I was echoing each <td> individually? All of the <td>'s are combined in one echo. The code to echo is enclosed in single quotation marks and any data is concatenated with '.' . When I tried your code it just returned the echoed data enclosed in double quotation marks.
Once again, the records displayed when there is no filter requested are correct so the html side of the table works fine. I just need to know why it is not showing any records when a date is selected.
Steve
-
I now think that I am more confused than when I started. I know that there is something wrong but do not know what is wrong otherwise I would try to solve it.
However I do not think there is anything in the query structure itself. It is a similar sort of setup to what I have used, and seen used, successfully in other queries.
On top of that, the basis of the code came from this very forum (http://www.phpfreaks.com/forums/php-coding-help/(solved)-clever-way-to-make-a-dropdown-menu-filter/msg747338/#msg747338) so I personally trust the syntax.
There could, of course, be a typo or I may have changed something incorrectly but I have double checked and cannot see anything.
Whatever code anomalies there may be in the query it does seem to be doing it's job.
When the page first loads, or you hit submit with no date selected, it sets $filter = ""; and all records load correctly. So the query in these instances picks up $filter perfectly.
When a date is picked and you hit submit, no records are returned. However
http://www.margate-fc.com/content/test/games.php
changes to
http://www.margate-fc.com/content/test/games.php?date=2005-08-13&submit=Filter
So the form is collecting the date correctly, passing the date on correctly, the page is getting the date correctly, as shown in the URL. There is just something wrong or missing that is stopping it from displaying the records.
Any suggestions on what I am missing??
Steve
-
is not allowed to have a "," after "SELECT *".
Are you sure? I must use that in 95% of my queries, albeit not ones that are trying to filter by date, ones
mysql_query($sql) or die(mysql_error() . " In $sql");
I tried that, but amended it to reflect my query and it returned
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Resource id #3' at line 1 In Resource id #3
Steve
-
The dropdown is pulling the date directly from the database and passing it on without any changing or formatting so I would not imagine it is the date format. I could be wrong though!
Steve
-
I have set up a dynamic dropdown to filter records on a page but it is not working and I feel it is down to the way I am using the GET. My guess is that you cannot use dates as a filter in this way but I cannot find any evidence of that through searching, mainly as I do not know what to search for!
The url generated is 'games.php?date=2005-09-03&submit=Filter' so it is passing the data from the dropdown correctly but I am clearly missing something as it just returns that no records have been found.
I am probably missing something simple and am sure that someone on here will spot it within seconds but I am lost. The actual testing page for this, in case you want to have a look at it not working, is http://www.margate-fc.com/content/test/games.php.
As always, any advice would be greatly appreciated!
Steve
The Query Part
// The 'WHERE' bit if (isset($_GET['date'])) { if ($_GET['date'] == "") { $filter = ""; } else { $filter = "WHERE date = {$_GET['date']}"; } } // Main Query $limit = 5; $all_games = mysql_query("SELECT *, HT.team_name as HomeTeam, VT.team_name as AwayTeam, COMP.comp_short_name as CP FROM all_games JOIN teams as HT ON all_games.home_team = HT.team_id JOIN teams as VT ON all_games.away_team = VT.team_id JOIN competitions as COMP ON all_games.comp = COMP.comp_id $filter ORDER BY all_games_id ASC "); if (!$all_games) { die("Query to show fields from table failed"); } // Filter query $dates = mysql_query("SELECT DISTINCT date FROM all_games ORDER BY date ASC ");
The form
<!--Filter Form--> <form method='get' enctype='text/plain'> <select size='1' name='date'> <?php while ($row_dates = mysql_fetch_assoc($dates)){ echo "<option value='".$row_dates['date']."'>".$row_dates['date']."</a></option>"; } ?> </select> <input type='submit' name='submit' value='Filter'> </form>
The table part (excluding header row)
<?php if(!mysql_num_rows($all_games)) { echo "<tr><td style=\"text-align:left\" COLSPAN=\"8\">No records returned</td></tr>"; } else { while ($row_all_games = mysql_fetch_assoc($all_games)){ echo '<tr> <td style="text-align:left" width="10PX">'.$row_all_games['all_games_id'].'</td>'; if ($row_all_games['date']=="") echo '<td>TBA</td>'; else echo '<td style="text-align:left" width="100PX">'.date('d M y',strtotime($row_all_games['date'])).'</td> <td style="text-align:left">'.$row_all_games['CP'].'</td> <td style="text-align:left"> '.$row_all_games['HomeTeam'].'</td> <td style="text-align:left"> '.$row_all_games['home_goals']. '</td> <td style="text-align:left"> '.$row_all_games['AwayTeam']. '</td> <td style="text-align:left"> '.$row_all_games['away_goals']. '</td> <td style="text-align:left"> '.$row_all_games['attendance']. '</td> </tr>'; }} ?>
-
Note to self - never mess around with code via your phone, on the bus, first thing in the morning!
It is amazing what problems a space can be and with that out it worked perfectly!
I have made a little tweak to account for the teams in the first two positions having no rows before them and the teams in last two positions having no rows after them and this is the final code
$current_team = 16; foreach ($rankings_arr as $rank => $row_table) { if ($row_table['Tid'] == $current_team) { $current_team_rank = $rank; break; } } $start_rank = max(1, $current_team_rank - 2); $end_rank = min(count($rankings_arr), $current_team_rank + 2); $sr = $start_rank; $er = $end_rank; if ($rank <= 3) { $sr = 1;$er = 5; } elseif ($rank >= 21) { $sr = 18;$er = 22; } else { $sr = $start_rank; $er = $end_rank; } for ($i = $sr; $i <= $er; ) { $row_table = $rankings_arr[$i] ;
(the final '}' comes after '</tr>')
Massive thanks for that, the help here always amazes me. If there was a 'Thank' button on here it would be getting a click!
Problem fully solved and a very happy person here! Until the next time.........
Thanks again
Steve
-
I have had a chance to try it on the bus on the way to work and get the same result.
I have changed the first part of the query to
$table = mysql_query("SELECT *,
t.team_name as Tm, t.team_id as Tid, @rownum := @rownum+1 AS rank
And changed your code to
$current_team = 1;
foreach ($rankings_arr as $rank => $row_table) {
if ($row_table['Tid '] == $current_team) {
$current_team_rank = $rank;
break;
}
}
$start_rank = max(1, $current_team_rank - 2);
$end_rank = min(count($rankings_arr), $current_team_rank + 2);
for ($i = $start_rank; $i <= $end_rank; ) {
$row_table = $rankings_arr[$i] ;
(apologies for not using code blocks but my phone won't let me use the formatting buttons)
The end result is at http://www.margate-fc.com/content/test/tablemini.php
The numbers in brackets are team_id's.
It does seem to me that the reason it is showing rows 1 and 2 is because it does not understand what the query is asking so there is no matching record and therefore no 2 records before it. It seems to be showing the first two rows as the two rows after.
Steve
-
I did try it that way but all that was returned were rows 1 and 2. Maybe there was a typo in what I put so will give it another go this evening.
Steve
-
Thanks to the other excellent help on some of the other sections on here I thought I would turn to the MySQL board for some structural advice!
I help out with a Football (Soccer) Club's web site and part of it will eventually be a historical archive of results and of the other Club's that they have played against. It is the other Clubs parts of the database that I am trying to develop at the moment.
The situation I have that each club may have had several different names over the years for example, the Club currently known as Manchester United was known as Newton Heath from 1878 until 1902. Some Clubs may also have used the same name in two different periods such as Leyton Orient who have been called that since 1987 and were also called that between 1946 and 1966, as well as being called Orient (without the Leyton prefix) from 1888 to 1898 and from 1966 to 1987.
On top of that two different clubs could have used the same team name at different times. Hastings United, as they are now, used to be Hastings Town and there was a completely different club called Hastings United.
With this in mind my idea was for three tables......
'Club' would hold the the Club Name, Year Formed and other data such as web site url. The club name would generally be the name that they go by now or that which they last went by if they no longer exist.
'TeamName' would simply be a list of the all of the names that the all of the Clubs had been known by over the years. However, if I was ever to list all the team names it would have to show 'Hastings United' twice to show the two different clubs that have used that name.
'ClubName' would use foreign keys to 'Club.Club Name' and 'TeamName.name' with from and to dates to link a club to a name between certain dates.
I had reached this point and was pondering whether I would need to have a separate team name for each club that used it (the Hastings United example) when I realised there was yet another complication - the fact that some current clubs are actually the result of mergers between two (or more) other clubs!
If a Club was the result of a merger there would need to be some way of also showing all of the the Team Names for all of the previous Clubs that make the current one.
As a bit of an example - in 1979, Ilford and Leytonstone merged to become Leytonstone/Ilford. In 1988 this club merged with Walthamstow Avenue to become Redbridge Forest. This club then merged with Dagenham in 1992 to become Dagenham & Redbridge.
So the current Dagenham & Redbridge is actually the end product of mergers of 6 different clubs over the years. It does not actually happen in this case but it is possible that each of those previous clubs may have has several different team names!!
It was at this point I got confused beyond belief. Quite simply a game involving Ilford in 1978 would need to be included in the statistics for the current Dagenham & Redbridge (as well as any of the other clubs from the subsequent mergers) and if a record were to show the history of a Club it would have to show all of the mergers and the previous team names if there were any!
If anyone has managed to read to this point and is not wondering what the hell the question actually is, I thank you! If anyone can offer any suggestions on the best way to set this all up I would be eternally greatful! If you want me to try to explain it a bit better then just ask!
Thanks in advance
Steve
-
Massive, massive thanks. That gives just the result I was after.
Just one more tiny favour if I can. As the data pulled to the pages is to be call from the teams id number, is it possible to use that as the filter?
I have tried changing
$current_team = "Margate"; foreach ($rankings_arr as $rank => $row_table) { if ($row_table['Tm'] == $current_team) { $current_team_rank = $rank; break;
to
$current_team = 42; foreach ($rankings_arr as $rank => $row_table) { if ($row_table['t.team_id'] == $current_team) { $current_team_rank = $rank; break;
but that only returns two rows and not the correct ones. I have also tried a few permutations to that to no avail.
Obviously in all reality the $current_team would = $d (or similar) as that would be the RecordID for the page, but for testing I would like to just put a manual team_id in $current_team.
If you can solve this a well I promise that will be my very last question on the subject!
Thanks again
Steve
-
When viewing the forum via an iPhone, the php and code blocks often cut off some of the text within them so you cannot actually see the whole code.
This glitch is certainly not unique to PHP Freaks and I do not think it is unique to SMF forums either but wondered if there was a way around it?
Steve
-
An initial look suggests that is going to be spot on. I will give it a try straight after work.
Thanks
Steve
Problem with array_reverse
in PHP Coding Help
Posted
Had a bit of a play with that but am still firmly stuck. I have gone for this on the query side (with new query names)
and this on the html side
As things stand, it returns 'No Records Returned'.
If I remove the 'LIMIT $start, $display_num' it shows the earliest 5 results as it is sorting it by the requested 'date ASC' but without 'LIMIT $start, $display_num' it just goes to the first five, not the last 5 as I need.
One other strange thing (and I will post this code right at the very end) is that the results returned give the right games, but none of the conditions in the html part work correctly, even though they are exactly the same as a very similar part elsewhere on the page.
Thanks for all the help and patience.
Steve
The full html which is ignoring requests
This part shows up as 'TBA' whether there is a date is entered or not.