Jump to content

MargateSteve

Members
  • Posts

    240
  • Joined

  • Last visited

Everything posted by MargateSteve

  1. That works almost perfectly and result is exactly how you suggested. All that is left to do is instead of for ($i = 2; $i <= 4; $i++) I need to get it to find t.team_id and assign that row number to $i instead of the fixed values of 2 and 4. So in my example in the first post if I wanted to just show the rank of 'City' $i would be 3 and if it was 'Athletic' $i would be 6, but I need to get these dynamically for each teams individual page. Once I have got that part, as I would want to also show the two teams above and below in the rankings I would have to try to call $i -2, $i -1, $i, $ +1, $i + 2. On the Athletic page this would show ranks 4,5,6,7,8 as 'Athletic's' rank is 6. I really hope I am not confusing things with how I am trying to explain it. A good example of what I am trying to achieve can be found at http://www.footballwebpages.co.uk/margate. This is the page for 'Margate' at a different site. On the right hand side of the content area there is 'The League Table' and this shows something similar to what I am trying to achieve, although it does show three teams above and three below instead of the two that I want. Although there are only 7 rows being output there, the ranking is still retained from the full standings so Margate's ranking is 13, although in the displayed table it is row 4. In my database, Margate's 'team_id' is 42 so in this instant I would need to find the row number that relates to 'team_id' 42 and show that row, the two rows above and the two below. Thanks for all the help so far. I am so much closer to getting the right end result than I ever would have been! Steve
  2. Sometimes my own stupidity and forgetfulness amazes even me! I now remember, when setting the code up, trying to work out whether $i++; should go after the echo for that field or after the table row! However, if I try to use $row_table['rank'] no numbers appear at all. Back to original point though (I do have a habit of sidetracking my own threads!) when you say 'read them into an array' does that mean write a static array or create an array from table data. If it is the first it would not work as the data changes constantly and if it was the second I would not know where to start!! I have spent the last couple of hours searching for reading data into arrays and also how to set a row number as array and all sorts of things I found online that seemed to do what I am looking for but they were not. It does seem a simple thing to do to show the row number where team_id = xx and then use that as a parameter but I am all out of search terms! Steve
  3. In hindsight, I do have MySQL 5.1 available on my PC through WAMP. I could copy the site to Localhost and test it all out that way. Thanks Steve
  4. The site that I am working on has a MySQL4.0 database although they do have MySQL5.0 ones available (presumably 4.0 was the most recent when the site was set up) and am considering whether it is worth upgrading. Personally I think that with the nature of the data on the site, just the option to create VIEWS is probably enough but I am looking for some advice before I jump in.... The account that the site is hosted on (1&1) is at the limit of available databases so I will need to drop the current one to create a new one. I assume that this will be as simple as exporting the SQL of the whole site, dropping the database, creating the new one, importing the SQL back in and then finding any references to the old database in the site and updating them with the new details (there are a couple of 3rd party programs being used in the site at the moment). Is there any danger of there being anything in the exported 4.0 SQL that will not import correctly into 5.0? I would assume not but as the site is live, I cannot run any risk whatsoever that anything can go wrong. Along a similar vein, would there be any statements that I may have used in the site that will not work correctly running from MySQL 5.0? I know that is quite a broad question but there is nothing more than very basic code being used and I would only imagine a problem if a previously commonly used statements underwent a name changed or was superceded by a new statement. Once again, unlikely I know, but I need to cover my back! Finally, as I said, VIEWS are the main reason for wanting to change but are there any other pro's and con's that may sway me either way. I have seen snippets of code that suggest that you can aggregate alias's but as I have never made it work, maybe this is another 5.0 feature? If it was a case that upgrading would be a hassle far greater than the benefits I would probably leave it for now. Thanks in advance Steve
  5. I currently have a query that compiles a League Standings Table (the full code is below) and generate the ranking with @rownum := @rownum+1 AS rank This works fine for the main standings page but I want to use the row numbers elsewhere. Each team has it's own page which uses 'team.team_id' as its 'recordID'. On these pages I would like to show that particular teams ranking in the standings. The two options I see would be to run the query filtering by 'team.team_id' but that would only return the one record so ranking would always be '1' or run the whole query and then somehow find which ranking relates to 'team.team_id' (something that may be possible with VIEWS but the database is running on MySQL4) but I cannot figure out how to get around this. For example, if the standings were RankTeam 1Rovers 2United 3City 4Rangers 5Town 6Athletic 7Wanderers 8Hotspur On the 'Rangers' page I would want it to show 'RANKING: 4' and on the 'Athletic' page it would show 'RANKING: 6'. On top of this I would want to show a small version of the rankings with one team above and one team below (it would actually be two teams but I will keep it simple until I understand it!) so one again given the two examples above I would get RANGERS PAGE RankTeam 3City 4Rangers 5Town ATHLETIC PAGE RankTeam 5Town 6Athletic 7Wanderers The query is $i = 1; $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"; $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 , (sum(CASE WHEN (".$ht." AND ".$hw.") THEN 3 ELSE 0 END) + sum(CASE WHEN (".$ht.") AND ".$d." THEN 1 ELSE 0 END)) AS HP , (sum(CASE WHEN (".$at." AND ".$aw.") THEN 3 ELSE 0 END) + sum(CASE WHEN (".$at.") AND ".$d." THEN 1 ELSE 0 END)) AS AP , count(CASE WHEN (".$ht." OR ".$at.") THEN 1 ELSE 0 END) as GP , sum(CASE WHEN (".$ht." ) THEN 1 ELSE 0 END) as HGP , sum(CASE WHEN ".$at." THEN 1 ELSE 0 END) as AGP , sum(CASE WHEN (".$ht." AND ".$hw.") OR (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS W , sum(CASE WHEN (".$ht." AND ".$hw.") THEN 1 ELSE 0 END) AS HW , sum(CASE WHEN (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS AW , sum(CASE WHEN (".$ht." AND ".$d.") OR (".$at." AND ".$d.") THEN 1 ELSE 0 END) AS D , sum(CASE WHEN (".$ht." AND ".$d.") THEN 1 ELSE 0 END) AS HD , sum(CASE WHEN (".$at." AND ".$d.") THEN 1 ELSE 0 END) AS AD , sum(CASE WHEN (".$ht." AND ".$aw.") OR (".$at." AND ".$hw.") THEN 1 ELSE 0 END) AS L , sum(CASE WHEN (".$ht." AND ".$aw.") THEN 1 ELSE 0 END) AS HL , sum(CASE WHEN (".$at." AND ".$hw.") THEN 1 ELSE 0 END) AS AL , SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END) as GF , SUM(CASE WHEN (".$ht.") THEN ".$hg." END) as HGF , SUM(CASE WHEN (".$at.") THEN ".$ag." END) as AGF , SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END) as GA , SUM(CASE WHEN (".$ht.") THEN ".$ag." END) as HGA , SUM(CASE WHEN (".$at.") THEN ".$hg." END) as AGA , (SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END) - SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END)) as GD , (SUM(CASE WHEN (".$ht.") THEN ".$hg." END) - SUM(CASE WHEN (".$ht.") THEN ".$ag." END)) as HGD , (SUM(CASE WHEN (".$at.") THEN ".$ag." END) - SUM(CASE WHEN (".$at.") THEN ".$hg." END)) as AGD from teams t left join all_games g on t.team_id in (g.home_team,g.away_team) WHERE comp = '1' AND home_goals IS NOT NULL AND date BETWEEN '2010-07-01' AND '2011-06-31' GROUP BY t.team_id ORDER BY P desc, GD desc, GF desc The html is <table width="" border="0" cellpadding="0" cellspacing="0" BORDER=1 RULES=ROWS FRAME=BOX> <tr> <td></td><td></td> <td colspan="9" align="center" bgcolor="#00FF99">ALL</td> <td colspan="9" align="center" >Home</td> <td colspan="9" align="center">Away</td> </tr> <tr> <td class="hdcell" >POS</td> <td class="hdcell" >Team</td> <td width="30" class="hdcell">P</td> <td width="30" class="hdcell">W</td> <td width="30" class="hdcell">D</td> <td width="30" class="hdcell">L</td> <td width="30" class="hdcell">F</td> <td width="30" class="hdcell">A</td> <td width="30" class="hdcell">GD</td> <td width="30" class="hdcell">Pts</td> <td width="30" class="hdcell"></td> <td></td> <td width="30" class="hdcell">P</td> <td width="30" class="hdcell">W</td> <td width="30" class="hdcell">D</td> <td width="30" class="hdcell">L</td> <td width="30" class="hdcell">F</td> <td width="30" class="hdcell">A</td> <td width="30" class="hdcell">GD</td> <td width="30" class="hdcell">Pts</td> <td></td> <td width="30" class="hdcell">P</td> <td width="30" class="hdcell">W</td> <td width="30" class="hdcell">D</td> <td width="30" class="hdcell">L</td> <td width="30" class="hdcell">F</td> <td width="30" class="hdcell">A</td> <td width="30" class="hdcell">GD</td> <td width="30" class="hdcell">Pts</td> </tr> <?php while ($row_table = mysql_fetch_assoc($table)){ echo '<tr> <td style="text-align:left" width="30">'.$i.'</td>'; echo '<td style="text-align:left">'.$row_table['Tm'].'</td> <td style="text-align:left">'.$row_table['GP'].'</td> <td style="text-align:left">'.$row_table['W'].'</td> <td style="text-align:left"> '.$row_table['D'].'</td> <td style="text-align:left"> '.$row_table['L']. '</td> <td style="text-align:left"> '.$row_table['GF']. '</td> <td style="text-align:left"> '.$row_table['GA']. '</td> <td style="text-align:left"> '.$row_table['GD']. '</td> <td style="text-align:left"> '.$row_table['P']. '</td> <td style="text-align:left"></td> <td style="text-align:left"></td> <td style="text-align:left">'.$row_table['HGP'].'</td> <td style="text-align:left">'.$row_table['HW'].'</td> <td style="text-align:left">'.$row_table['HD'].'</td> <td style="text-align:left"> '.$row_table['HL']. '</td> <td style="text-align:left"> '.$row_table['HGF']. '</td> <td style="text-align:left"> '.$row_table['HGA']. '</td> <td style="text-align:left"> '.$row_table['HGD']. '</td> <td style="text-align:left"> '.$row_table['HP']. '</td> <td style="text-align:left"></td> <td style="text-align:left">'.$row_table['AGP'].'</td> <td style="text-align:left">'.$row_table['AW'].'</td> <td style="text-align:left">'.$row_table['AD'].'</td> <td style="text-align:left"> '.$row_table['AL']. '</td> <td style="text-align:left"> '.$row_table['AGF']. '</td> <td style="text-align:left"> '.$row_table['AGA']. '</td> <td style="text-align:left"> '.$row_table['AGD']. '</td> <td style="text-align:left"> '.$row_table['AP']. '</td> </tr>'; $i++; } ?> </table> As always, thanks in advance for any tips or suggestions, even if they are telling me to scrap what I have so far and start again!! Steve PS. Can someone explain why @rownum := @rownum+1 AS rank causes $i to increment by one? I found the code while searching and do not understand why $i is necessary unless it is a reserved reference. The way the code looks, I should be able to output the ranking using 'rank' but that does not work. I have no problem with how it does things, it would just be nice to understand!
  6. That is perfect so massive thanks!! I had found a few similar through searching but this was the first to show how to execute the function in the HTML. As I think I have confused things by asking several questions in the same post, I will mark this solved now and ask the other questions separately. Thanks again Steve
  7. I have had a chance to play with these now and have had indifferent success!! This ends up in no records being returned. Anything I try to do that aggregates any alias's does exactly the same. It actually works as it is but I just wanted to tidy things up. This very nearly works. The only problem is that 11, 12 and 13 show as 11st, 12nd and 13rd. I have tried to put extra CASE statements in to allow for this and also tried to tweak it using an IF/Else statement (basically saying that if $pos between 11 and 13 then use $position = $pos . 'th' otherwise use the switch) but there is a problem somewhere with the syntax as it just kept returning an error pointing to the first line of the if statement. In my head the IF/ELSE route be the best way to sort it but my head is not very clued up in these things. I have found some code to do this using functions but as I have not used them before, I am not sure where to put them in my code or how to get them to work! Steve
  8. Apologies for not replying sooner but my hard drive died. I have a new one now and once I have recovered my old data I will give this a go. Thanks Steve
  9. I have finally worked out how to get a football league table query up and running (mainly by stealing code from elsewhere and adapting it to work the way I want it to!) but need to try to tweak it for a couple of other pages. The test page with it on is at http://www.margate-fc.com/content/test/table.php The query is $i = 1; $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"; $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 , (sum(CASE WHEN (".$ht." AND ".$hw.") THEN 3 ELSE 0 END) + sum(CASE WHEN (".$ht.") AND ".$d." THEN 1 ELSE 0 END)) AS HP , (sum(CASE WHEN (".$at." AND ".$aw.") THEN 3 ELSE 0 END) + sum(CASE WHEN (".$at.") AND ".$d." THEN 1 ELSE 0 END)) AS AP , count(CASE WHEN (".$ht." OR ".$at.") THEN 1 ELSE 0 END) as GP , sum(CASE WHEN (".$ht." ) THEN 1 ELSE 0 END) as HGP , sum(CASE WHEN ".$at." THEN 1 ELSE 0 END) as AGP , sum(CASE WHEN (".$ht." AND ".$hw.") OR (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS W , sum(CASE WHEN (".$ht." AND ".$hw.") THEN 1 ELSE 0 END) AS HW , sum(CASE WHEN (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS AW , sum(CASE WHEN (".$ht." AND ".$d.") OR (".$at." AND ".$d.") THEN 1 ELSE 0 END) AS D , sum(CASE WHEN (".$ht." AND ".$d.") THEN 1 ELSE 0 END) AS HD , sum(CASE WHEN (".$at." AND ".$d.") THEN 1 ELSE 0 END) AS AD , sum(CASE WHEN (".$ht." AND ".$aw.") OR (".$at." AND ".$hw.") THEN 1 ELSE 0 END) AS L , sum(CASE WHEN (".$ht." AND ".$aw.") THEN 1 ELSE 0 END) AS HL , sum(CASE WHEN (".$at." AND ".$hw.") THEN 1 ELSE 0 END) AS AL , SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END) as GF , SUM(CASE WHEN (".$ht.") THEN ".$hg." END) as HGF , SUM(CASE WHEN (".$at.") THEN ".$ag." END) as AGF , SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END) as GA , SUM(CASE WHEN (".$ht.") THEN ".$ag." END) as HGA , SUM(CASE WHEN (".$at.") THEN ".$hg." END) as AGA , (SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END) - SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END)) as GD , (SUM(CASE WHEN (".$ht.") THEN ".$hg." END) - SUM(CASE WHEN (".$ht.") THEN ".$ag." END)) as HGD , (SUM(CASE WHEN (".$at.") THEN ".$ag." END) - SUM(CASE WHEN (".$at.") THEN ".$hg." END)) as AGD from teams t left join all_games g on t.team_id in (g.home_team,g.away_team) WHERE comp = '1' AND home_goals IS NOT NULL AND date BETWEEN '2010-07-01' AND '2011-06-31' GROUP BY t.team_id ORDER BY P desc, GD desc, GF desc and the html (although some of the css still is not done and some code still needs tidying) is <table width="" border="0" cellpadding="0" cellspacing="0" BORDER=1 RULES=ROWS FRAME=BOX> <tr> <td></td><td></td> <td colspan="9" align="center" bgcolor="#00FF99">ALL</td> <td colspan="9" align="center" >Home</td> <td colspan="9" align="center">Away</td> </tr> <tr> <td class="hdcell" >POS</td> <td class="hdcell" >Team</td> <td width="30" class="hdcell">P</td> <td width="30" class="hdcell">W</td> <td width="30" class="hdcell">D</td> <td width="30" class="hdcell">L</td> <td width="30" class="hdcell">F</td> <td width="30" class="hdcell">A</td> <td width="30" class="hdcell">GD</td> <td width="30" class="hdcell">Pts</td> <td width="30" class="hdcell"></td> <td></td> <td width="30" class="hdcell">P</td> <td width="30" class="hdcell">W</td> <td width="30" class="hdcell">D</td> <td width="30" class="hdcell">L</td> <td width="30" class="hdcell">F</td> <td width="30" class="hdcell">A</td> <td width="30" class="hdcell">GD</td> <td width="30" class="hdcell">Pts</td> <td></td> <td width="30" class="hdcell">P</td> <td width="30" class="hdcell">W</td> <td width="30" class="hdcell">D</td> <td width="30" class="hdcell">L</td> <td width="30" class="hdcell">F</td> <td width="30" class="hdcell">A</td> <td width="30" class="hdcell">GD</td> <td width="30" class="hdcell">Pts</td> </tr> <?php while ($row_table = mysql_fetch_assoc($table)){ echo '<tr> <td style="text-align:left" width="30">'.$i.'</td>'; echo '<td style="text-align:left">'.$row_table['Tm'].'</td> <td style="text-align:left">'.$row_table['GP'].'</td> <td style="text-align:left">'.$row_table['W'].'</td> <td style="text-align:left"> '.$row_table['D'].'</td> <td style="text-align:left"> '.$row_table['L']. '</td> <td style="text-align:left"> '.$row_table['GF']. '</td> <td style="text-align:left"> '.$row_table['GA']. '</td> <td style="text-align:left"> '.$row_table['GD']. '</td> <td style="text-align:left"> '.$row_table['P']. '</td> <td style="text-align:left"></td> <td style="text-align:left"></td> <td style="text-align:left">'.$row_table['HGP'].'</td> <td style="text-align:left">'.$row_table['HW'].'</td> <td style="text-align:left">'.$row_table['HD'].'</td> <td style="text-align:left"> '.$row_table['HL']. '</td> <td style="text-align:left"> '.$row_table['HGF']. '</td> <td style="text-align:left"> '.$row_table['HGA']. '</td> <td style="text-align:left"> '.$row_table['HGD']. '</td> <td style="text-align:left"> '.$row_table['HP']. '</td> <td style="text-align:left"></td> <td style="text-align:left">'.$row_table['AGP'].'</td> <td style="text-align:left">'.$row_table['AW'].'</td> <td style="text-align:left">'.$row_table['AD'].'</td> <td style="text-align:left"> '.$row_table['AL']. '</td> <td style="text-align:left"> '.$row_table['AGF']. '</td> <td style="text-align:left"> '.$row_table['AGA']. '</td> <td style="text-align:left"> '.$row_table['AGD']. '</td> <td style="text-align:left"> '.$row_table['AP']. '</td> </tr>'; $i++; } ?> </table> As I said, this works fine in the test page but there are two other things that I am looking to do with it and one way I hope to streamline the code. Firstly the code bit..... There is a bit of replication that I was hoping to eliminate by using the sum function but from what I can see this cannot be done with Alias's. I was hoping to change the code , sum(CASE WHEN (".$ht." AND ".$hw.") OR (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS W , sum(CASE WHEN (".$ht." AND ".$hw.") THEN 1 ELSE 0 END) AS HW , sum(CASE WHEN (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS AW to , sum(HW + PW) AS W , sum(CASE WHEN (".$ht." AND ".$hw.") THEN 1 ELSE 0 END) AS HW , sum(CASE WHEN (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS AW but that just causes no records to be shown. Is there a way around this or is there no way to aggregate alias's? Secondly, the other pages. There are 2 other pages that I want to use similar data on and I think what I want to do is quite simple. On the front page of the site I want a stripped down version of the table which shows just five records, that of my team, Margate, and the two positions above and two below. At the moment Margate are in 19th position so I would want it to show rows 17,18,19,20,21. If Margate were 10th I would want it to show 8,9,10,11,12. I would imagine that there is a way of finding which position Margate is in then setting the OFFSET to that -2 but after extensive Googling (I did spot it in exactly the context I need some time back when I did not need it!) I cannot find anything as I basically do not know what I need to ask for! The final thing is that each team has its own page and on that page I want to show their position in the League in a friendly style, such as 19th as opposed to 19. I would imagine that I would need to pull the whole league table in as opposed to using a query that matches the team otherwise it would only have one result and each team would show as being 1st. I would only want to show the position of the relevant team but obviously the whole query would need to be run to get that position. How would I then find the relative teams position? I think that the ideal thing would be to use a VIEW but am working with a MySQL 4 database. Any help or advice will, as always, be appreciated. Thanks in advance Steve
  10. I currently have a query that uses six different COUNT's working at http://www.margate-fc.com/content/1st_team/stats/scorers.php. It uses the following tables (with a selection of data) -- Table structure for table `games` -- CREATE TABLE `games` ( `match_id` int(11) NOT NULL auto_increment, `date` date default NULL, `time` time default NULL, `competition` int(11) default NULL, `round` tinyint(2) default NULL, `replay` char(1) default NULL, `h_a` varchar(45) default NULL, `opponent` int(11) default NULL, `wdl` varchar(45) default NULL, `for` tinyint(4) default NULL, `against` tinyint(4) default NULL, `attendance` int(11) default NULL, `report_url` longtext, `photo_url` longtext, `stadium` int(11) default NULL, `manager` varchar(45) default NULL, `live` varchar(255) default NULL, `notes` varchar(255) default NULL, `extra_time` char(1) default NULL, PRIMARY KEY (`match_id`) ) TYPE=MyISAM AUTO_INCREMENT=312 ; INSERT INTO `games` VALUES (1, '2009-08-15', '15:00:00', 1, NULL, '', 'A', 19, 'L', 0, 4, 508, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1250446274&&archive=1281131284&start_from=&ucat=10&', '', 4, '', 'N', '', ''); INSERT INTO `games` VALUES (2, '2009-08-18', '19:45:00', 1, NULL, '', 'H', 29, 'L', 0, 4, 653, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1250637449&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/6.%20mfc%20v%20Dartford%20-%2018.08.2009&Qiv=thumbs&Qis=M', 7, 'TERRY YORATH', NULL, NULL, NULL); INSERT INTO `games` VALUES (3, '2009-08-22', '15:00:00', 1, NULL, '', 'H', 30, 'W', 2, 1, 345, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1250965567&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/7.%20mfc%20v%20Boreham%20Wood%20-%2022.08.2009&Qiv=thumbs&Qis=M', 7, 'TERRY YORATH', NULL, NULL, NULL); INSERT INTO `games` VALUES (4, '2009-08-24', '19:45:00', 1, NULL, '', 'A', 1, 'W', 3, 0, 243, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1251151671&archive=1281131284&start_from=&ucat=10&', NULL, 2, 'TERRY YORATH', NULL, NULL, NULL); INSERT INTO `games` VALUES (5, '2009-08-29', '15:00:00', 1, NULL, '', 'A', 11, 'L', 0, 3, 156, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1251573000&archive=1281131284&start_from=&ucat=10&', NULL, NULL, 'TERRY YORATH', NULL, NULL, NULL); INSERT INTO `games` VALUES (6, '2009-08-31', '15:00:00', 1, NULL, '', 'H', 7, 'L', 0, 1, 423, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1251746220&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/8.%20mfc%20v%20Cray%20Wanderers%20-%2031.08.2009&Qiv=thumbs&Qis=M', NULL, 'TERRY YORATH', NULL, NULL, NULL); INSERT INTO `games` VALUES (7, '2009-09-05', '15:00:00', 1, NULL, '', 'A', 31, 'D', 2, 2, 120, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1252191674&archive=1281131284&start_from=&ucat=10&', NULL, 9, 'TERRY YORATH', NULL, NULL, NULL); INSERT INTO `games` VALUES (8, '2009-09-12', '15:00:00', 2, 2, '', 'H', 19, 'D', 2, 2, 402, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1252781008&&archive=1281131284&start_from=&ucat=10&', '', NULL, 'TERRY YORATH', '', '', ''); INSERT INTO `games` VALUES (9, '2009-09-15', '19:45:00', 2, 2, 'r', 'A', 19, 'L', 2, 3, 301, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1253050913&archive=1281131284&start_from=&ucat=10&', '', NULL, 'TERRY YORATH', '', '', ''); INSERT INTO `games` VALUES (10, '2009-09-19', '15:00:00', 1, NULL, '', 'H', 15, 'L', 1, 3, 356, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1253386555&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/9.%20mfc%20v%20Horsham%20-%2019.09.2009&Qiv=thumbs&Qis=M', NULL, 'TERRY YORATH', NULL, NULL, NULL); -- -------------------------------------------------------- -- -- Table structure for table `goals` -- CREATE TABLE `goals` ( `goal_id` int(11) NOT NULL auto_increment, `match` int(11) default NULL, `scorer` int(11) default NULL, `goal_type` int(11) default NULL, `goal_time` int(11) default NULL, PRIMARY KEY (`goal_id`) ) TYPE=MyISAM AUTO_INCREMENT=116 ; INSERT INTO `goals` VALUES (1, 3, 48, 1, 90); INSERT INTO `goals` VALUES (2, 3, 53, 1, 49); INSERT INTO `goals` VALUES (3, 4, 6, 1, 23); INSERT INTO `goals` VALUES (4, 4, 6, 1, 33); INSERT INTO `goals` VALUES (5, 4, 38, 1, 73); INSERT INTO `goals` VALUES (6, 7, 6, 2, 34); INSERT INTO `goals` VALUES (7, 7, 68, 1, 23); INSERT INTO `goals` VALUES (8, 8, 8, 1, 41); INSERT INTO `goals` VALUES (9, 8, 33, 1, 43); INSERT INTO `goals` VALUES (10, 9, 38, 1, 43); -- -------------------------------------------------------- -- -- Table structure for table `players` -- CREATE TABLE `players` ( `player_id` int(11) NOT NULL auto_increment, `surname` varchar(255) default NULL, `firstname` varchar(255) default NULL, `date_of_birth` date default NULL, `position` int(11) default NULL, `image` varchar(255) default NULL, `date_joined` date default NULL, `date_left` date default NULL, `previous_clubs` varchar(255) default NULL, `place_of_birth` varchar(255) default NULL, `home_sponsor` varchar(255) default NULL, `away_sponsor` varchar(255) default NULL, `profile` longtext, `Triallist` varchar(10) default NULL, PRIMARY KEY (`player_id`) ) TYPE=MyISAM AUTO_INCREMENT=102 ; INSERT INTO `players` VALUES (66, 'Robinson', 'Stuart', '1901-01-01', 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'defqwerqwrqwe\r\nqwerqwe\r\nrwer\r\nqwer\r\nqwer\r\nwer\r\nwqer\r\nwqe\r\nrqw\r\nrqw\r\ner\r\nqwer\r\nqwe', NULL); INSERT INTO `players` VALUES (61, 'Mullin', 'Pat', '1982-05-28', 1, NULL, '2008-06-01', '2009-10-08', NULL, NULL, NULL, NULL, 'Pat signed from Maidstone United, where he had made over 100 appearances, during the summer of 2008 for his second spell with the club. Initially took over the number one shirt from Scott Chalmers-Stevens but injury saw him sidelined until November. Upon his return he was ever-present for the remainder of the season.\r\n\r\nAs a youth Pat was at Coventry City and Millwall and has also featured for Dover Athletic, Sittingbourne and Herne Bay.\r\n\r\nStruggled to gain a first team spot this season as a plethora of goalkeepers came and went and joined Ramsgate in October.', NULL); INSERT INTO `players` VALUES (5, 'Beresford', 'Marc', '1986-10-12', 1, '', '2008-09-01', '2010-03-01', '', '', '', '', 'Stepped up from local football at the start of September last season to cover a long-term injury to Pat Mullin. Despite featuring on the bench in almost all of last season, he is still to make his first team debut.\r\n\r\nCurrently on a season long loan at Lordswood.', NULL); INSERT INTO `players` VALUES (40, 'Young', 'Dan', '1988-01-06', 2, NULL, '2007-06-01', NULL, NULL, NULL, NULL, NULL, 'Centre back born in Sidcup who started his career with Derby County. Danny captained the Rams’ youth team before progressing to the reserves, playing regularly for them during 2004/05 and 2005/06.\r\n\r\nAfter being released by Derby Danny had a short spell with Bromley at the start of the 2006/07 season before moving on to Croydon Athletic where he won most of the club’s end of season awards that year.\r\n\r\nHe signed for Margate in the summer of 2007 after turning in some impressive displays during the pre-season friendlies and took over the captains role following Louis Smiths long-term injury last season.', NULL); INSERT INTO `players` VALUES (59, 'Lewis', 'Ben', '1977-06-22', 2, NULL, '2009-06-01', '2009-11-01', NULL, NULL, NULL, NULL, 'Ben, seemingly, orignally signed for Margate in March 2009 but confusion over his release from Maidstone United prevented this from being finalised until the summer.\r\n\r\nStarted off Heybridge Swifts before joining Colchester United, where he made two youth appearances before joining Southend in August 1997. and scored the winner on his debut against Fulham.\r\n\r\nKnee problems ended his professional career after 14 appearances and 1 goal for the Roots Hall side and he dropped into non-league in 1999 again with Heybridge before being snapped up by home-town club Chelmsford City the following March.\r\n\r\nHe moved to Grays Athletic in May 2002 before moving on to Ford United that December Following this, he has played for non league teams Grays Athletic, Ford United, Chelmsford City, Heybridge Swifts, Welling United, Bishop''s Stortford and Maidstone United\r\n\r\nMoved onto Bishop''s Stortford in November 2004 joining St Albans City in 2006. He made 21 Conference South appearances that season but after just one more start the following term he joined Welling in the Sptember before moving to Maidstone the following May.', NULL); INSERT INTO `players` VALUES (33, 'Robinson', 'Curtis', '1989-04-22', 2, 'http://www.margate-fc.com/match_gallery/qdig-files/converted-images/Season%202010-11/00.%20Margate%20Squad%20Photos%202010-11/med_IMG_7923.JPG', '2008-06-01', NULL, 'Ramsgate, Dover Athletic', NULL, 'Tom McKenna', NULL, 'Signed for Margate in the summer of 2008 and gradually became a regular and reliable part of the defence with calmness that belies his young age. Curtis also possesses a impressively long throw. <br />\r\n<br />\r\nStarted off with Ramsgate in their Youth and Reserve sides before leaving for Greenwich University and featured for Dover Athletic Reserves before moving to Hartsdown Park.', NULL); INSERT INTO `players` VALUES (69, 'Wheatley', 'Luke', '1991-04-25', 2, '', '2008-10-01', '2010-01-01', '', '', '', '', 'Local youngster who was called up to the first team squad in October 2008 and scored his first goal two weeks later in the win at Boreham Wood.\r\nAnother of the local lads who have come into the first team with more confidence and strength than you would expect from a defender still in his teens.\r\n\r\nMoved to Ramsgate on an initial one month loan deal in October but returned in mid-November.', NULL); INSERT INTO `players` VALUES (15, 'Haverson', 'Jack', '1987-08-22', 2, '', '2009-03-01', NULL, '', '', '', '', 'Jack began his career at Ipswich Town, joining their academy at the age of 16. He left the Suffolk side in the summer of 2006 to join AFC Bournemouth but spent much of the following season on loan at Hayes before joining Grays at the start of this season.\r\n\r\nHe joined Bromley in February 2008. Has played also for Sutton United and Sittingbourne.', NULL); INSERT INTO `players` VALUES (28, 'Morris', 'Kieran', '1987-04-29', 3, '', '2007-08-01', NULL, '', '', '', '', 'Signed for Margate in August 2007 after solid displays in pre-season friendlies. After 22 starts and 15 substitute appearances he was one of the few players who remained with the club under new management for the 2008/09 season.\r\n\r\nKieran started the first ten games but then found himself frequently on the sub''s bench until a surprising appearance at right-back in November saw a new side to the midfielder as he slotted into the position comfortably, although injury did, eventually, restrict him to 29 appearances.\r\n\r\nMoved to Whitstable Town on loan in December.', NULL); INSERT INTO `players` VALUES (22, 'Lacy', 'Aaron', '1981-06-24', 2, 'http://www.margate-fc.com/match_gallery/qdig-files/converted-images/Season%202010-11/00.%20Margate%20Squad%20Photos%202010-11/med_IMG_7918.JPG', '2010-02-01', NULL, 'Gillingham, Lordswood, Chatham Town, Maidstone United', NULL, 'Alan Anstice', NULL, 'Signed for Margate in February 2010 after over 6 years with Maidstone United and immeidiately caught the attention of the fans with his long throws.<br />\r\n<br />\r\nA right-sided defender who started off with Gillingham and has also featured for Lordswood and Chatham Town.', NULL); this query $gls = mysql_query("SELECT *, COUNT(CASE games.competition WHEN 1 THEN goals.goal_id ELSE NULL END) AS lgegls, COUNT(CASE games.competition WHEN 2 THEN goals.goal_id ELSE NULL END) AS facgls, COUNT(CASE games.competition WHEN 3 THEN goals.goal_id ELSE NULL END) AS fatgls, COUNT(CASE games.competition WHEN 4 THEN goals.goal_id ELSE NULL END) AS kscgls, COUNT(CASE games.competition WHEN 5 THEN goals.goal_id ELSE NULL END) AS lgecgls, COUNT(goals.goal_id) AS tgls FROM goals, games INNER JOIN players ON goals.scorer = players.player_id WHERE goals.match = games.match_id AND games.competition <> 6 GROUP BY goals.scorer ORDER BY tgls DESC, lgegls DESC "); if (!$gls) { die("Query to show fields from table failed"); } and this html for the output <table width="577" border="0" cellpadding="0" cellspacing="0" BORDER=1 RULES=ROWS FRAME=BOX style="text-align:center"> <tr> <td width="17" align="center" valign="bottom" style="font-size: 10px; font-weight: bold;">SEE GOALS</td> <td width="100"> </td> <td width="60" align="center" valign="bottom" class="Table_Headers">RYMAN<br /> PREMIER</td> <td width="60" align="center" valign="bottom" class="Table_Headers">FA<br /> CUP</td> <td width="60" align="center" valign="bottom" class="Table_Headers">FA<br /> TROPHY</td> <td width="60" align="center" valign="bottom" class="Table_Headers">KENT SENIOR<br /> CUP</td> <td width="60" align="center" valign="bottom" class="Table_Headers">LEAGUE<br /> CUP</td> <td width="60" align="center" valign="bottom" class="Table_Headers">TOTAL</td> </tr> <?php while ($row_gls = mysql_fetch_assoc($gls)){ if ($row_gls['player_id']=="2") echo '<tr> <td></td> '; else echo '<tr> <td><a href="http://www.margate-fc.com/content/1st_team/player_goals.php?recordID='.$row_gls['player_id'].'" ><img src="/edit/news/data/upimages/more.png" width="16" border="0" alt="More Stats" title="More Stats"/></a></td> '?> <?php if ($row_gls['player_id']=="2") echo '<td style="text-align:left">'.$row_gls['firstname'].' '.$row_gls['surname'].'\'s'; else echo '<td style="text-align:left"><a href="/content/1st_team/squad_details.php?recordID='.$row_gls['player_id']. '">'. $row_gls['surname']. ', '. $row_gls['firstname']. '</a></td>'; ?> <?php echo ' <td bgcolor="#C4D8FD">'. $row_gls['lgegls']. '</td> <td>'. $row_gls['facgls']. '</td> <td bgcolor="#C4D8FD">'. $row_gls['fatgls']. '</td> <td>'. $row_gls['kscgls']. '</td> <td bgcolor="#C4D8FD">'. $row_gls['lgecgls']. '</td> <td class="Normal_Table_Column_bold">'. $row_gls['tgls']. '</td> </tr>'; } ?> </table> Everything works fine but I now want to create another page which uses this query, but with more complex COUNT's from another table as well. The extra table (and some data) is -- -- Table structure for table `appearances` -- CREATE TABLE `appearances` ( `app_id` int(11) NOT NULL auto_increment, `match` int(11) default NULL, `number` int(11) default NULL, `player` int(11) default NULL, `type` int(11) default NULL, `on` int(11) default NULL, `off` int(11) default NULL, `yellows` int(11) default NULL, `red` char(1) default NULL, `replaced` int(11) default NULL, PRIMARY KEY (`app_id`) ) TYPE=MyISAM AUTO_INCREMENT=1759 ; INSERT INTO `appearances` VALUES (1, 1, 1, 66, 1, 0, 90, 0, 'N', NULL); INSERT INTO `appearances` VALUES (2, 1, 2, 28, 1, 0, 90, 1, 'N', NULL); INSERT INTO `appearances` VALUES (3, 1, 3, 33, 1, 0, 90, 0, 'N', NULL); INSERT INTO `appearances` VALUES (4, 1, 4, 59, 1, 0, 90, 0, 'N', NULL); INSERT INTO `appearances` VALUES (5, 1, 5, 69, 1, 0, 46, 0, 'N', NULL); INSERT INTO `appearances` VALUES (6, 1, 6, 15, 1, 0, 90, 0, 'N', NULL); INSERT INTO `appearances` VALUES (7, 1, 7, 22, 1, 0, 31, 0, 'Y', NULL); INSERT INTO `appearances` VALUES (8, 1, 8, 38, 1, 0, 90, 0, 'N', NULL); INSERT INTO `appearances` VALUES (9, 1, 9, 53, 1, 0, 90, 0, 'N', NULL); INSERT INTO `appearances` VALUES (10, 1, 10, 6, 1, 0, 90, 0, 'N', NULL); I would want to still count everything by competition as I have done in the original query but as there are 3 appearances types I would need to show each for each competition, with each combination showing as a separate column in the output ie. CompetitionAppearance TypeOutput Column Name 11Comp1Type1 12Comp1Type2 13Comp1Type3 21Comp2Type1 22Comp2Type2 23Comp2Type3 Having had a good read up on the COUNT function I could not see a way to use multiple criteria so tried to count a select COUNT(SELECT * FROM goals, games, appearances INNER JOIN players ON goals.scorer = players.player_id INNER JOIN players ON appearances.player = players.player_id WHERE goals.match = games.match_id AND appearances.type = 1 AND games.competition = 1) AS comp1type1; COUNT(SELECT * FROM goals, games, appearances INNER JOIN players ON goals.scorer = players.player_id INNER JOIN players ON appearances.player = players.player_id WHERE goals.match = games.match_id AND appearances.type = 2 AND games.competition = 1) AS comp2type1; COUNT(SELECT * FROM goals, games, appearances INNER JOIN players ON goals.scorer = players.player_id INNER JOIN players ON appearances.player = players.player_id WHERE goals.match = games.match_id AND appearances.type = 1 AND games.competition = 2) AS comp1type2 but when I add that into the existing query it just brings up a page with no results. In trying to get it working I am probably further away now than I was to start with and am pretty sure that, once again, I am going the long way round this. My gut feeling is that it would work better with more grouping but having read up on that, I cannot work out how to get the results to output in the columns that I want. Any suggestions, advice or offers to rip it up and start again would be gratefully received! Thanks in advance Steve
  11. Not that it is affecting the problem you mention, shouldn't echo "<table border=\"1\" align=\"left\">"; come before the 'while' statement? Steve
  12. That works perfectly. The strange thing is that when the query was just <php>SELECT * FROM seasons WHERE season_id = %s</php> without the extra line of code, the page returned the correct data but gave the problem with old dates. I have also used 'WHERE xxx = %s' in quite a few other pages and that worked fine so was really puzzled why it did not work here. I will now go away and try to understand that new line of code!! Thanks as always Steve
  13. The value is passed from a LIST page using <code><a href="seasonview.php?season_id='.$row['season_id'].'"><img src="../../images/icons/Search.png" height="20" alt="View" /></a></code> The list page has all of the seasons on it and the page with the query and code that I posted before is the VIEW page to show the details of one record from the list. The query works fine if I do not use DATE_FORMAT, but then strtotime in the php to echo the data wont show dates before 1901. Steve
  14. I have had a little play around and think I have done more harm than good and have completely messed the query up. The page now shows the table but without the data in it instead of showing a blank page with 'Query was empty'. My SQL is <?php mysql_select_db($database_Test, $Test); $seasonview = "SELECT *, DATE_FORMAT(`season_start`,'%D %M %Y') AS startdate, DATE_FORMAT(`season_end`,'%D %M %Y') AS enddate FROM seasons WHERE season_id = %d"; $result_seasonview = mysql_query($seasonview); $row_seasonview = mysql_fetch_assoc($result_seasonview); ?> and the html to show it is <table align="center"> <tr valign="baseline"> <td nowrap="nowrap" align="right">Season_id:</td> <td><?php echo $row_seasonview['season_id']; ?></td> </tr> <tr valign="baseline"> <td nowrap="nowrap" align="right">Season_name:</td> <td><?php echo $row_seasonview['season_name']; ?></td> </tr> <tr valign="baseline"> <td nowrap="nowrap" align="right">Season_start:</td> <td><?php echo $row_seasonview['startdate']; ?></td> </tr> <tr valign="baseline"> <td nowrap="nowrap" align="right">Season_end:</td> <td><?php echo $row_seasonview['enddate']; ?></td> </tr> </table> Can anyone sort out the mess I have made??? Thanks as always Steve
  15. I just tried that but it still came back with an empty query. Really is puzzling me. Steve
  16. Thanks for the quick responses. The table I am working with (with a few rows) is CREATE TABLE `seasons` ( `season_id` int(11) NOT NULL auto_increment, `season_name` char(9) default NULL, `season_start` date default NULL, `season_end` date default NULL, PRIMARY KEY (`season_id`) ) TYPE=MyISAM AUTO_INCREMENT=119 ; -- -- Dumping data for table `seasons` -- INSERT INTO `seasons` VALUES (1, '1896/97', '1896-07-01', '1897-06-30'); INSERT INTO `seasons` VALUES (2, '1897/98', '1897-07-01', '1898-06-30'); INSERT INTO `seasons` VALUES (3, '1898/99', '1898-07-01', '1899-06-30'); INSERT INTO `seasons` VALUES (4, '1899/00', '1899-07-01', '1900-06-30'); INSERT INTO `seasons` VALUES (6, '1901/02', '1901-07-01', '1902-06-30'); INSERT INTO `seasons` VALUES (8, '1903/04', '1903-07-01', '1904-06-30'); INSERT INTO `seasons` VALUES (9, '1904/05', '1904-07-01', '1905-06-30'); INSERT INTO `seasons` VALUES (10, '1905/06', '1905-07-01', '1906-06-30'); The page itself is a VIEW RECORD page called seasonview.php and it is linked to from the LIST page with <a href="seasonview.php?season_id='.$row['season_id'].'"><img src="../../images/icons/Search.png" height="20" alt="View" /></a> The code using strtotime is SELECT * FROM seasons WHERE season_id = %s and <tr valign="baseline"><td nowrap="nowrap" align="right">Season_start:</td> <td><?php echo date("F j, Y ", strtotime($row_seasonview["season_start"])) ; ?></td> </tr> <tr valign="baseline"> <td nowrap="nowrap" align="right">Season_end:</td> <td><?php echo date("F j, Y ", strtotime($row_seasonview["season_end"])) ; ?></td></tr> but anything pre-1901 shows up as 'December 13, 1901' When I try to format it via the query I use SELECT *, DATE_FORMAT(`season_start`,'%D %M %Y') AS startdate, DATE_FORMAT(`season_end`,'%D %M %Y') AS enddate FROM seasons WHERE season_id = %s and <tr valign="baseline"> <td nowrap="nowrap" align="right">Season_start:</td> <td><?php echo $row_seasonview['startdate']; ?></td> </tr> <tr valign="baseline"> <td nowrap="nowrap" align="right">Season_end:</td> <td><?php echo $row_seasonview['enddate']; ?></td> </tr> but that is when I get 'Query was empty' Any suggestions as to what I am doing wrong? Thanks Steve
  17. Been a while since I hit a snag but have a new one! I have hit the old problem that I need to be able to input dates pre 13th December 1901 which appears to not work when using strtotime <?php echo date("F j, Y ", strtotime($row_seasonview['season_start'])); ?> Following a bit of googling it seemed that the alternative was to set the format in the query SELECT *, DATE_FORMAT(`season_start`,'%D %M %Y') AS startdate, DATE_FORMAT(`season_end`,'%D %M %Y') AS enddate FROM seasons WHERE season_id = %s and to call the date from that <?php echo $row_seasonview['startdate']; ?> but this just comes back with 'Query was empty'. If I remove the two DATE_FORMAT lines the query runs fine. I know there must be a way round this but cannot fathom it out. Any suggestions would be greatfully welcomed!!! Thanks in advance Steve
  18. I am trying to clean up some code in my pages and would like some advice on good practice for the structure. Initially the code was generated by Dreamweaver and everything relating to the queries were place at the very top of the page, outside any of the HTML. A lot of the coding help I have had from this forum has put the queries etc in with the actual output php. Is there any right or wrong way to do this? I assume both ways have their pros and cons and both generate the same end result but I want to start getting into good habits before it is too late!! Thanks in advance Steve
  19. That works almost 100% perfectly! Yet another excellent piece of help from the community! The only thing that is not working with it is a slight problem if you are on a page number and you then choose a LIMIT which does not reach that page, but other than that it is just what I was looking for. I will now take time to read through it so I understand it as part of my learning curve. Thanks again Steve
  20. Firstly, I am not sure where the first few lines of my post went so it should have started something like........ I am trying to implement both pagination and a dropdown to limit the number of rows that work with each other. I have a test page up and running at http://www.margate-fc.com/content/test/seasonslist.php and both the pagination and the dropdown (mainly thanks to Pikachu2000 in this thread http://www.phpfreaks.com/forums/php-coding-help/using-dropdown-to-limit-rows/) work fine on their own but they do now work together. Secondly, thanks for the suggestion PFMaBiSmAd. I think I understand what you are trying to suggest but will have a bit of a read up on using values as a get parameter (searching on here first!) before I try anything. Thanks Steve
  21. Sorry, forgot to mention something. Although not vital in the page I am working on at the moment, for some of the others, I would also be wanting to include a 'Start from Row Number' option so they could choose to show 5 records from record 7, for example. If that would affect the way things work again I would be appreciative if someone could explain to implement that with all of the above! Thanks Steve
  22. If a LIMIT on rows is set by the dropdown, the Pagination links do not reflect this. For example, if you had 100 rows showing 25 records per page, the pagination should show 4 pages, whereas if there were 10 records per page it should show 10 pages. At the moment, the pagination shows 4 pages (which is correct for the page default of 25 records) but if someone chooses to show 10 records per page, it still only shows the 4 pages in the pagination links and if you click one it takes you the relevant page, ignoring the user selected LIMIT. If a user selects 5 records per page in the dropdown and then clicks page 2 in the pagination it shows records 26-50 when it should really show records 6-10. On the flip side, without selecting a number of rows in the dropdown, if you click on page 2 it shows records 26-50 and if you then choose 5 in the dropdown it does show the first 5 from that page (ie 26-30). So they do sort of work together but I would really like the pagination to alter depending on the number of rows selected. I hope that has not confused everyone too much but below is the code that I am using, in full as everything is related it seems. I have commented it as best I can to try to clear things up but if it would be better for me to break it down into the relevant bits then just shout! There are two sets of pagination on there, top and bottom, and the top one is after the // TOP PAGINATION comment and the form for the dropdown is after // LIMIT ROWS DROPDOWN If anyone can explain how I can get this to work properly, or whether it is even possible, I would be extremely grateful! Thanks in advance Steve <?php require_once('../../Connections/Test.php'); ?> <?php // Make a MySQL Connection mysql_select_db($database_Test, $Test); // How many rows to show per page $rowsPerPage = 25; // Show the first page by default $pageNum = 1; // if $_GET['page'] defined, use it as page number if(isset($_GET['page'])) { $pageNum = $_GET['page']; } // Counting the offset to show the right records per page $offset = ($pageNum - 1) * $rowsPerPage; // Retrieve all the data from the seasons table $query = "SELECT *, DATE_FORMAT(`season_start`,' %D %M %Y') AS startdate, DATE_FORMAT(`season_end`,'%D %M %Y') AS enddate FROM seasons "; $query .= ( isset($_POST['records_per_page']) && intval($_POST['records_per_page']) > 0 ) ? 'LIMIT ' .$offset.',' . (int) $_POST['records_per_page'] : 'LIMIT ' .$offset.',' . $rowsPerPage; $result = mysql_query( $query ) or die('Query: ' . $query . '<br>Produced error: ' . mysql_error() . '<br>'); //Query for the DECADES INDEX at the top $links = mysql_query("SELECT DISTINCT SUBSTRING(season_name,1,3) as letter FROM seasons ORDER BY 1") or die(mysql_error()); // PAGINATION // Find the number of records $page_query = "SELECT COUNT(season_name) AS numrows FROM seasons"; $page_result = mysql_query($page_query) or die('Error, query failed'); $page_row = mysql_fetch_array($page_result, MYSQL_ASSOC); $page_numrows = $page_row['numrows']; // Divide the number of records by records per page to get number of pages $maxPage = ceil($page_numrows/$rowsPerPage); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Season List</title> <link href="../../styles/databaseedit.css" rel="stylesheet" type="text/css" /> </head> <body> <div class="container"> <?php include("/homepages/46/d98455693/htdocs/Templates/database/header.html"); ?> <?php include("/homepages/46/d98455693/htdocs/Templates/database/left.html"); ?> <div class="content"> <h1> SEASON LIST</h1> <p>Filter by Decade<br /> <?php // DECADES LISTING AT TOP OF PAGE // Fetch the records of the DECADE $row while($linkrow = mysql_fetch_array($links)) { // Show links to DECADE queries and add "0's" to the result echo '<a href="seasonslistdecades.php?searchstring='; echo $linkrow['letter']. '">'.$linkrow['letter'].'0\'s</a> '; } ?> </p> <hr /> <?php // TOP PAGINATION // print the link to access each page $self = $_SERVER['PHP_SELF']; $nav = ''; for($page = 1; $page <= $maxPage; $page++) { if ($page == $pageNum) { $nav .= " $page "; // no need to create a link to current page } else { $nav .= " <a href=\"$self?page=$page\">$page</a> "; } } // creating previous and next link // plus the link to go straight to // the first and last page if ($pageNum > 1) { $page = $pageNum - 1; $prev = " <a href=\"$self?page=$page\"><a href=\"$self?page=$page\"><img src=\"../../images/icons/Prev.png\" height=\"20\" alt=\"Previous\" title=\"Previous Page\" /></a> "; $first = " <a href=\"$self?page=1\"><img src=\"../../images/icons/First.png\" height=\"20\" alt=\"First\" title=\"First Page\" /></a> "; } else { $prev = ' '; // we're on page one, don't print previous link $first = ' '; // nor the first page link } if ($pageNum < $maxPage) { $page = $pageNum + 1; $next = " <a href=\"$self?page=$page\"><img src=\"../../images/icons/Next.png\" height=\"20\" alt=\"Next\" title=\"Next Page\" /></a> "; $last = " <a href=\"$self?page=$maxPage\"><img src=\"../../images/icons/Last.png\" height=\"20\" alt=\"Last\" title=\"Last Page\" /></a> "; } else { $next = ' '; // we're on the last page, don't print next link $last = ' '; // nor the last page link } // print the navigation link echo $first . $prev . $nav . $next . $last;?> // LIMIT ROWS DROPDOWN <div class="drop_right" ><form action="" method="post" name="records_per_page" target="_self"> Number of Records per page <select name="records_per_page" id="records_per_page"> <option value="5">5</option> <option value="10">10</option> <option value="15">15</option> <option value="20">20</option> <option value="25">25</option> <option value="30">30</option> <option value="40">40</option> <option value="50">50</option> </select> <input type="submit" name="SUB" id="SUB" value="Submit" /> </form></div> <p> <?php // SEASONS LISTING // Show the Table Headers echo ' <table align="center" cellspacing="0" cellpadding="0"> <tr> <th>ID</th> <th>Name</th> <th>From</th> <th>To</th> <th>Edit</th> </tr> '; // Show the Season Data while($row = mysql_fetch_array($result)) { echo ' <tr> <td>'.$row['season_id'] .'</td> <td>'.$row['season_name'] .'</td> <td>'.$row['startdate'] .'</td> <td>'.$row['enddate'] .'</td> <td><img src="../../images/icons/Search.png" height="20" alt="View" /> <img src="../../images/icons/Edit.png" height="20" alt="Edit" /> <img src="../../images/icons/Close.png" height="20" alt="Delete" /></td> </tr> ' ; } echo '</table> '; // BOTTOM PAGINATION // print the link to access each page $self = $_SERVER['PHP_SELF']; $nav = ''; for($page = 1; $page <= $maxPage; $page++) { if ($page == $pageNum) { $nav .= " $page "; // no need to create a link to current page } else { $nav .= " <a href=\"$self?page=$page\">$page</a> "; } } // creating previous and next link // plus the link to go straight to // the first and last page if ($pageNum > 1) { $page = $pageNum - 1; $prev = " <a href=\"$self?page=$page\"><a href=\"$self?page=$page\"><img src=\"../../images/icons/Prev.png\" height=\"20\" alt=\"Previous\" title=\"Previous Page\" /></a> "; $first = " <a href=\"$self?page=1\"><img src=\"../../images/icons/First.png\" height=\"20\" alt=\"First\" title=\"First Page\" /></a> "; } else { $prev = ' '; // we're on page one, don't print previous link $first = ' '; // nor the first page link } if ($pageNum < $maxPage) { $page = $pageNum + 1; $next = " <a href=\"$self?page=$page\"><img src=\"../../images/icons/Next.png\" height=\"20\" alt=\"Next\" title=\"Next Page\" /></a> "; $last = " <a href=\"$self?page=$maxPage\"><img src=\"../../images/icons/Last.png\" height=\"20\" alt=\"Last\" title=\"Last Page\" /></a> "; } else { $next = ' '; // we're on the last page, don't print next link $last = ' '; // nor the last page link } // print the navigation link echo $first . $prev . $nav . $next . $last; ?> <br /> <p> </p> <!-- end .content --></div> <div class="footer"> <p>This .footer contains the declaration position:relative; to give Internet Explorer 6 hasLayout for the .footer and cause it to clear correctly. If you're not required to support IE6, you may remove it.</p> <!-- end .footer --></div> <!-- end .container --></div> </body> </html>
  23. I have changed it to action="". I did not realise that would work just as well! Thanks Steve
  24. Magnificent answer, as always. Needed a slight tweak (putting a space between 'FROM seasons' and the ' " ' and changed the form action to "<?php echo $PHP_SELF;?>" but other than that it was seamless! There are a few bits in the code that I have not come across before so will have a read up to understand how it works before moving on to the next bit. Thanks again Steve
  25. I am trying to include a dropdown menu on a page so that a user can choose how many records will be shown and return that data back to the same page. what I have so far is.... The Form <form action="PHP_SELF" method="post" name="records_per_page" target="_self"> Number of Records per page <select name="records_per_page" id="records_per_page"> <option value="5">5</option> <option value="10">10</option> <option value="15">15</option> <option value="20">20</option> <option value="25">25</option> <option value="30">30</option> <option value="40">40</option> <option value="50">50</option> </select> </form> The Query // How many rows to show per page $rowsPerPage = 25; // Retrieve all the data from the seasons table $result = mysql_query ("SELECT *, DATE_FORMAT(`season_start`,' %D %M %Y') AS startdate, DATE_FORMAT(`season_end`,'%D %M %Y') AS enddate FROM seasons LIMIT "; if ($_POST['records_per_page'] = '') $result .= "'$rowsPerPage' "); else $result .= "'$records_per_page' "); or die(mysql_error()); The error I get is Parse error: syntax error, unexpected ';' in /homepages/46/d98455693/htdocs/content/test/seasonslist2.php on line 28 which refers to the line LIMIT "; Having posted the code into Dreamweaver it also shows errors for the two options in the if statement $result .= "'$rowsPerPage' "); $result .= "'$records_per_page' "); so it is clearly that I have set the code up incorrectly, probably with misplacement of ;'s. What I am trying to do is when the page loads normally, the records should be limited to $rowsPerPage but if a user has specified a number of rows in the dropdown it should show that number. Once I have that sorted I will also be trying to implement a'Start from Record Number' option. As always, any advice would be immensely appreciated! Thanks in advance Steve
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.