Jump to content

MargateSteve

Members
 View Profile  See their activity

  • Posts

    240

  • Joined

  • Last visited

 Content Type 

Posts posted by MargateSteve

    Using PHP to update config file

    in PHP Coding Help

    Posted

    I have a very simple password protection set-up on a page as a temporary measure until I can set something better up. The person I set it up for wants to be able change the Username and Password occasionally but wants me to set up a simple form which will overwrite the data in the config file (connection.php) instead of having to open the file himself (don't ask!).

     

    I have has a read round to see if there is any obvious and simple solutions but none seem as simple as I had hoped. I intend to work on a system via MySQL where there can be multiple users and passwords but as time is tight at the moment I just want to get something basic and working.

     

    The nature of the 'hidden' content is not sensitive (it is just a football clubs live commentary stream that they are hoping to get people to subscribe to) so there are no issues at this moment with security. All he needs to do is choose a new Username and Password every game and email it to the subscribers.

     

    The data that he wants to update in connection.php, via a form, is

    $username = "user";
$password = "pass";

     

    The code in the actual page with the log-in is

    <?php 
require_once('connection.php');
if ($_POST['txtUsername'] != $username || $_POST['txtPassword'] != $password) { 
?> 
<h1 style="color: #FFF">Login</h1> 

<form name="form" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>"> 
  <p class="white_text"><label for="txtUsername">Username:</span></label> 
     <br />
  <input type="text" title="Enter your Username" name="txtUsername" /></p> 

  <p class="white_text"><label for="txtpassword">Password:</label> 
     <br />
  <input type="password" title="Enter your password" name="txtPassword" /></p> 

  <p><input type="submit" name="Submit" value="Login" /></p> 

</form> 

<?php 
} 
else { 
?> 
CONTENT TO BE SHOWN................

    Is there a simple way of creating a form just to update those two bits in the connection.php or will I have to bite the bullet and set it up via MySQL from the off?

     

    Thanks in advance

    Steve

     

    Using a generated row number in another query

    in PHP Coding Help

    Posted

    The teams ID is passed to the page as 'Tid' and then the extra formatting is done on the row where the team ID matches 'Tid'.

     if ( $id == $row_smtableall['Tid'])
 	echo '<tr class="tm">';
 	else
 	echo '<tr>';
 

    I am in the process of setting up a site, inviting people to help develop an Open Source solution for people to run their own football stats sites. If you are interested, even in a idea suggesting category, let me know and I will send you a link when it is up and running.

     

    Steve

    Show Last N records per group

    in MySQL Help

    Posted

    I know that this subject has been done to death but I really cannot implement any of the suggestions in the context I need it.

     

    I currently have a query that show a League Standings table

    $lgetable = mysql_query("SELECT *
,COUNT(*) AS ct
, t.team_name as Tm
, @rownum := @rownum+1 AS rank
, (sum(CASE WHEN (".$ht." AND ".$hw.")OR(".$at." AND ".$aw.") THEN 3 ELSE 0 END)+ sum(CASE WHEN (".$ht." OR ".$at.") AND ".$d." THEN 1 ELSE 0 END)) AS P
, count(CASE WHEN (".$ht." OR ".$at.") THEN 1 ELSE 0 END) as GP
, sum(CASE WHEN (".$ht." AND ".$hw.") OR (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS W
, sum(CASE WHEN (".$ht." AND ".$d.") OR (".$at." AND ".$d.") THEN 1 ELSE 0 END) AS D
, sum(CASE WHEN (".$ht." AND ".$aw.") OR (".$at." AND ".$hw.") THEN 1 ELSE 0 END) AS L
, SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END) as GF
, SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END) as GA
, (SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END)- SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END)) as GD

from teams t
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 alias's used are
$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"

     

    I am trying to use that same query to show only the most recent five games for each team (t.team_id) which I thought would be possible just by removing the current ORDER BY statement and replacing it with

    HAVING ct <= 5
ORDER BY date DESC

    But this causes no rows to be returned. Strangely, as a test, I grouped it by date instead of t.team_id, and it returned results, though obviously not the right ones.

     

    Am I simply missing something with the grouping, or am I miles from the solution?

    Thanks

    Steve

    Query against an array

    in PHP Coding Help

    Posted

    All sorted. I did not realise that if you put a subquery in there, it would be ignored by the grouping.

    $att_table = mysql_query("SELECT 
t.team_name as Tm, @rownum := @rownum+1 AS rank
, ROUND(AVG(g.attendance))atte, SUM(g.attendance) tot, MAX(g.attendance) max, MIN(g.attendance) min, COUNT(g.attendance) gms,


(SELECT AVG(g.attendance)
from all_games g 
WHERE comp = '1' AND home_goals IS NOT NULL AND date BETWEEN '2010-07-01' AND '2011-06-31'  
) lgeav


from teams t
left join all_games g 
on t.team_id = g.home_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 atte DESC
")

    Query against an array

    in PHP Coding Help

    Posted

    I am trying to compare a teams average attendance compared to the average across all teams. The query that show the average by team (column 'atte') is

    $att_table = mysql_query("
SELECT 
t.team_name as Tm
, ROUND(AVG(g.attendance))atte
, SUM(g.attendance) tot
, MAX(g.attendance) max
, MIN(g.attendance) min
from teams t
left join all_games g 
on t.team_id = g.home_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 atte DESC
");

    A simple query to show the total average is

    $lgeav = mysql_query("
(AVG(g.attendance))atte2
from all_games g 
WHERE comp = '1' 
AND home_goals IS NOT NULL 
AND date BETWEEN '2010-07-01' AND '2011-06-31'  
");

    I am hoping to put the second query into an array and use the two to show the teams whose own average attendance is higher than the total average attendance in bold, with something along the lines of

    while ($row_att_table = mysql_fetch_assoc($att_table)){
if ($row_att_table['atte'] > $lgeav['atte2'])   
	 echo '<tr  style="font-weight:bold">';
	 else
	 echo '<tr>';

     

    I know that this can be done with views but in an attempt to broaden my knowledge (and also in case I ever have to do something similar on MySQL4) I wondered if there was a simple way to do this through PHP?

     

    Thanks in advance

    Steve

    Problem with array_reverse

    in PHP Coding Help

    Posted

    This turned out to be extremely simple in the end just using SQL. All I had to do was wrap the original query in a separate set of brackets and then us another ORDER BY afterwards.

    $l5all = 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) ORDER BY date ASC
")

    Steve

    Reversing an ORDER BY statement

    in MySQL Help

    Posted

    This turned out to be extremely simple in the end. All I had to do was wrap the original query in a separate set of brackets and then us another ORDER BY afterwards.

    $l5all = 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) ORDER BY date ASC
")

    Steve

    Adding a second table to an existing query

    in MySQL Help

    Posted

    I have got this sorted now. What I needed to do was divide or multiply results from one query by one in another query.

     

    In the end I created a view for each query and queried the two views.

     SELECT view1.player_id as theRealId, view1.surname as theRealSurname, view1.firstname as theRealFirstname, view1.lgemins/view2.lgegls as lminpg, view1.*, view2.* FROM view1 LEFT JOIN view2 on view1.player_id=view2.player_id

     

    The aggregate part I was trying to do is view1.lgemins/view2.lgegls as lminpg. I needed to divide the number of minutes that the player has played in query/view 1 by the number of goals the player had scored in query/view 2 to find the number of minutes between each goal.

     

    Thanks for the help.

    Steve

    Adding a second table to an existing query

    in MySQL Help

    Posted

    There are actually two reasons but if there is a way around them without merging the queries I will be happy to go with any suggestions!

     

    Firstly, I want to be able to show results from both queries in one HTML table, grouped by the player. As I mentioned before, I can get these to work independently but usually a Player Statistics page will show both appearances and goals ie.

    Player

    Starts

    Sub

    Goals

    Smith

    21

    3

    10

    Secondly, I will want to aggregate parts of one query against the other, for example, I would want to divide a players appearances by his goals to get a 'goals per appearance' field.

     

    Steve

    Struggling with first attempt at creating a view

    in MySQL Help

    Posted

    I have been having a little play around to try to understand creating views now that I have upgraded to MySQL 5 and am stuck already (surprisingly enough!).

    $query = mysql_query("
CREATE VIEW 
test 
AS
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 = all_games.home_team AND all_games.home_goals IS NOT NULL 
OR
$id = all_games.away_team AND all_games.home_goals IS NOT NULL 
ORDER BY `date` DESC 
LIMIT 5");

$tpall_games = mysql_query("SELECT * FROM test");
if (!$tpall_games) {
    die(mysql_error());
};

     

    The VIEW works perfectly if it is just used as a query, but I wanted to create a view so I can then run several different queries off of it.

    The error returned is 'Table 'foo.test' doesn't exist' which suggests the VIEW is not being created.

     

    I presume that I am missing something very simple such as actually executing the VIEW in some way before the query tries to read from it.

     

    Steve

    Why have I had to change this query after upgrading to MySQL5?

    in MySQL Help

    Posted

    I think that I have worked it out but please correct me if I am wrong.

     

    In MySQL 4 you could place the joins after all the FROM tables were selected

    FROM goals, games 
INNER JOIN players ON goals.scorer = players.player_id

    In MySQL 5 you have to place the join directly after the FROM table

    FROM goals INNER JOIN players ON goals.scorer = players.player_id, games

     

    Steve

    Why have I had to change this query after upgrading to MySQL5?

    in MySQL Help

    Posted

    I have just upgraded from MySQL 4 to 5 and everything seems to have been ok except for one query.

     

    This used to work fine

    SELECT *,
COUNT(CASE games.competition WHEN 1 THEN goals.goal_id ELSE NULL END) AS lgegls,
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.date BETWEEN '2010-07-01' AND '2011-06-31' AND games.competition <> 6
GROUP BY goals.scorer ORDER BY tgls DESC, lgegls DESC LIMIT ".$limit."

     

    but that now causes the query to show the fields to fail. I have taken out the join and this following query works

    SELECT *,
COUNT(CASE games.competition WHEN 1 THEN goals.goal_id ELSE NULL END) AS lgegls,
COUNT(goals.goal_id) AS tgls
FROM goals, games, players 
WHERE goals.scorer = players.player_id
AND goals.match = games.match_id AND games.date BETWEEN '2010-07-01' AND '2011-06-31' AND games.competition <> 6
GROUP BY goals.scorer ORDER BY tgls DESC, lgegls DESC LIMIT ".$limit."

    but just wondered what I need to do differently in future.

     

    Steve

     

    EDIT**

    I actually seem to have queries failling everywhere. It seems to be anywhere that there is a join

    SELECT * FROM (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) as tbl ORDER BY `date` asc

    Adding a second table to an existing query

    in MySQL Help

    Posted

    I am still banging my head against a brick wall with this so thought I would add some more information to see if that will help.

     

    I have now created the two seperate queries that I want merged into one and both work exactly the way that I want them to individually.

     

    The existing query is

    SELECT *,
COUNT(CASE games.competition WHEN 1 THEN 1 ELSE NULL END) AS lgegls,
COUNT(CASE games.competition WHEN 2 THEN 1 ELSE NULL END) AS facgls,
COUNT(CASE games.competition WHEN 3 THEN 1 ELSE NULL END) AS fatgls,
COUNT(CASE games.competition WHEN 4 THEN 1 ELSE NULL END) AS kscgls,
COUNT(CASE games.competition WHEN 5 THEN 1 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.date BETWEEN '2010-07-01' AND '2011-06-31' AND games.competition <> 6
GROUP BY players.player_id

    and the query that I want to merge with this is

    SELECT *,
COUNT(CASE WHEN games.competition = 1 AND appearances.type = 1 THEN 1 ELSE NULL END) AS lgest,
COUNT(CASE WHEN games.competition = 1 AND appearances.type = 2 THEN 1 ELSE NULL END) AS lgesub,
COUNT(CASE WHEN games.competition = 1 AND appearances.type = 3 THEN 1 ELSE NULL END) AS lgebench,
COUNT(CASE WHEN games.competition = 2 AND appearances.type = 1 THEN 1 ELSE NULL END) AS facst,
COUNT(CASE WHEN games.competition = 2 AND appearances.type = 2 THEN 1 ELSE NULL END) AS facsub,
COUNT(CASE WHEN games.competition = 2 AND appearances.type = 3 THEN 1 ELSE NULL END) AS facbench,
COUNT(CASE WHEN games.competition = 3 AND appearances.type = 1 THEN 1 ELSE NULL END) AS fatst,
COUNT(CASE WHEN games.competition = 3 AND appearances.type = 2 THEN 1 ELSE NULL END) AS fatsub,
COUNT(CASE WHEN games.competition = 3 AND appearances.type = 3 THEN 1 ELSE NULL END) AS fatbench,
COUNT(CASE WHEN games.competition = 4 AND appearances.type = 1 THEN 1 ELSE NULL END) AS kscst,
COUNT(CASE WHEN games.competition = 4 AND appearances.type = 2 THEN 1 ELSE NULL END) AS kscsub,
COUNT(CASE WHEN games.competition = 4 AND appearances.type = 3 THEN 1 ELSE NULL END) AS kscbench,
COUNT(CASE WHEN games.competition = 5 AND appearances.type = 1 THEN 1 ELSE NULL END) AS lcst,
COUNT(CASE WHEN games.competition = 5 AND appearances.type = 2 THEN 1 ELSE NULL END) AS lcsub,
COUNT(CASE WHEN games.competition = 5 AND appearances.type = 3 THEN 1 ELSE NULL END) AS lcbench,
COUNT(CASE WHEN appearances.type = 1 THEN 1 ELSE NULL END) AS tst,
COUNT(CASE WHEN appearances.type = 2 THEN 1 ELSE NULL END) AS tsub,
COUNT(CASE WHEN appearances.type = 3 THEN 1 ELSE NULL END) AS tbench,
SUM(CASE WHEN games.competition = 1 THEN appearances.off ELSE NULL END) - SUM(CASE WHEN games.competition = 1 THEN appearances.on ELSE NULL END) AS lgemins,
SUM(CASE WHEN games.competition = 2 THEN appearances.off ELSE NULL END) - SUM(CASE WHEN games.competition = 2 THEN appearances.on ELSE NULL END) AS facmins,
SUM(CASE WHEN games.competition = 3 THEN appearances.off ELSE NULL END) - SUM(CASE WHEN games.competition = 3 THEN appearances.on ELSE NULL END) AS fatmins,
SUM(CASE WHEN games.competition = 4 THEN appearances.off ELSE NULL END) - SUM(CASE WHEN games.competition = 4 THEN appearances.on ELSE NULL END) AS kscmins,
SUM(CASE WHEN games.competition = 5 THEN appearances.off ELSE NULL END) - SUM(CASE WHEN games.competition = 5 THEN appearances.on ELSE NULL END) AS lcmins,
SUM(appearances.off) - SUM(appearances.on) AS tmins
FROM appearances, games 
INNER JOIN players ON appearances.player = players.player_id
WHERE appearances.match = games.match_id AND games.date BETWEEN '2010-07-01' AND '2011-06-31' AND games.competition <> 6 AND players.player_id <> 1
GROUP BY players.player_id

     

    The one extra relationship that is not shown there, that would probably be required in the joint query is that 'goals.scorer = appearances.player', although that may not be relevant as they are both FK's to 'players.player_id'.

     

    I want to out put all of the results from both queries in one html table. As I said, they both work perfectly individually but no matter how much I read up on joins, I cannot work out how to join them.

     

    One other thing I would need to do is perform aggregates between what is currently the two queries, for example, SUM(tmins / tgls) but I would imagine that would be straightforward once everything is joined correctly.

     

    Any help, advice, suggestions or complete re-writes would be gratefully received!

     

    Steve

    Adding a second table to an existing query

    in MySQL Help

    Posted

    I have some progress thanks to your reply Joel24. Not quite there but closer!!

     

    My current query is 

    SELECT *,
COUNT(CASE WHEN games.competition = 1 AND appearances.type = 1 THEN 1 ELSE NULL END) AS lgestarts,
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 appearances, goals, games
INNER JOIN players p1 ON appearances.player = p1.player_id
INNER JOIN players p2 ON goals.scorer = p2.player_id
WHERE goals.match = games.match_id AND appearances.match = games.match_id
AND goals.match = games.match_id AND games.date BETWEEN '2010-07-01' AND '2011-06-31' AND games.competition <> 6
GROUP BY p1.player_id, p2.player_id ORDER BY tgls DESC, lgegls DESC

     

    and this does actually generate some data but I now think the problem is with the grouping.

     

    This current query is running at http://www.margate-fc.com/content/1st_team/stats/scorers2.php while the original one which is querying the one table is at http://www.margate-fc.com/content/1st_team/stats/scorers.php.

    You can see that the new query seems to be getting the right(ish) results but there is no grouping. I did try to join the 'players' table to itself and group by the player_id in that but this cause everything to have the wrong results and every number on the page was in the hundreds!

     

    Steve

    Reversing an ORDER BY statement

    in MySQL Help

    Posted

    why are you using a variable $id in this line?? the IN clause works like so 

     WHERE column IN ('value1','value2',....)

     

    That would be my naivety in MySQL! I thought that would be the correct way to return results when $id (which is a posted 'team.team_id') was in either of the named columns.

     

    Would it be correct syntax to use 

    WHERE 
$id =  all_games.home_team AND all_games.home_goals IS NOT NULL 
OR $id = all_games.away_team AND all_games.home_goals IS NOT NULL

    ?

     

    The first query gives the correct results (ie. the most recent 5 records by date) but would show

    1st Jan 2011
1st Dec 2010
1st Nov 2010
1st Oct 2010
1st Sep 2010

    when I need it to show

    1st Sep 2010
1st Oct 2010
1st Nov 2010
1st Dec 2010
1st Jan 2011

     

    Steve

     

    Adding a second table to an existing query

    in MySQL Help

    Posted

    I currently have a query that shows goals scored by players in a football team but want to also include data from another table to show the appearances that they have made.

     

    The current query is

    $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.date BETWEEN '2010-07-01' AND '2011-06-31' 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 the tables used, with selected data are

    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);
INSERT INTO `games` VALUES (11, '2009-09-22', '19:45:00', 1, NULL, '', 'A', 13, 'W', 2, 1, 159, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1253659389&archive=1281131284&start_from=&ucat=10&', '', NULL, 'TERRY YORATH', '', '', '');
INSERT INTO `games` VALUES (12, '2009-09-26', NULL, 1, NULL, '', 'H', 14, 'D', 0, 0, 355, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1253990544&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/10.%20mfc%20v%20AFC%20Hornchurch%20-%2026.09.2009&Qiv=thumbs&Qis=M', NULL, 'NEVILLE SOUTHALL', NULL, NULL, NULL);
INSERT INTO `games` VALUES (13, '2009-10-03', NULL, 1, NULL, '', 'H', 16, 'W', 2, 1, 434, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1254597010&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/11.%20mfc%20v%20Kingstonian%20-%2003.10.2009&Qiv=thumbs&Qis=M', NULL, 'NEVILLE SOUTHALL', NULL, NULL, NULL);
INSERT INTO `games` VALUES (14, '2009-10-10', NULL, 1, NULL, '', 'A', 4, 'L', 0, 2, 359, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1255198975&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/12.%20Canvey%20Island%20v%20mfc%20-%2010.10.2009&Qiv=thumbs&Qis=M', NULL, 'NEVILLE SOUTHALL', NULL, NULL, NULL);
INSERT INTO `games` VALUES (15, '2009-10-17', NULL, 3, 2, '', 'A', 22, 'L', 1, 3, 265, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1255797599&archive=1281131284&start_from=&ucat=10&', '', NULL, 'NEVILLE SOUTHALL', '', '', '');
INSERT INTO `games` VALUES (16, '2009-11-07', NULL, 1, NULL, '', 'A', 12, 'L', 1, 2, 517, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1257626642&archive=1281131284&start_from=&ucat=10&', NULL, NULL, 'MARK BUTLER', NULL, NULL, NULL);
INSERT INTO `games` VALUES (17, '2009-11-10', NULL, 5, 7, '', 'A', 32, 'W', 1, 0, 91, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1257895019&archive=1281131284&start_from=&ucat=10&', '', NULL, 'MARK BUTLER', '', '', '');
INSERT INTO `games` VALUES (18, '2009-11-17', '19:45:00', 1, NULL, '', 'A', 33, 'W', 4, 3, 129, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1258495597&archive=1281131284&start_from=&ucat=10&', '', NULL, 'MARK BUTLER', '', '', '');
INSERT INTO `games` VALUES (19, '2009-11-29', NULL, 1, NULL, '', 'A', 7, 'L', 2, 3, 179, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1259518899&archive=1281131284&start_from=&ucat=10&', NULL, NULL, 'MARK BUTLER', NULL, NULL, NULL);
INSERT INTO `games` VALUES (20, '2009-12-05', NULL, 1, NULL, '', 'H', 11, 'D', 2, 2, 363, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1260033638&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/14.%20mfc%20v%20Harrow%20Borough%20-%2005.12.2009&Qiv=thumbs&Qis=M', NULL, 'MARK BUTLER', NULL, NULL, NULL);
INSERT INTO `games` VALUES (21, '2009-12-12', NULL, 1, NULL, NULL, 'A', 35, 'D', 3, 3, 316, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1260614761&archive=1281131284&start_from=&ucat=10&', NULL, NULL, 'MARK BUTLER', NULL, NULL, NULL);
INSERT INTO `games` VALUES (22, '2009-12-16', NULL, 5, 8, '', 'A', 34, 'L', 2, 3, 130, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1260999710&archive=1281131284&start_from=&ucat=10&', '', NULL, 'MARK BUTLER', '', '', '');
INSERT INTO `games` VALUES (23, '2009-12-26', NULL, 1, NULL, NULL, 'A', 18, 'W', 1, 0, 225, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1261853400&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/15%20Maidstone%20United%20v%20mfc%20-%2026.12.2009&Qiv=thumbs&Qis=M', NULL, 'MARK BUTLER', NULL, NULL, NULL);
INSERT INTO `games` VALUES (24, '2010-01-02', '15:00:00', 1, NULL, '', 'H', 18, 'L', 0, 1, 568, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1262461878&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/16%2C%20mfc%20v%20Maidstone%20United%20-%2002.01.2010&Qiv=thumbs&Qis=M', 7, 'MARK BUTLER', '', '', '');
INSERT INTO `games` VALUES (25, '2010-01-16', NULL, 1, NULL, NULL, 'H', 31, 'D', 1, 1, 280, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1263668584&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/17.%20mfc%20v%20Ashford%20Town%20%28Mx%29%20-%2016.01.2010&Qiv=thumbs&Qis=M', NULL, 'MARK BUTLER', NULL, NULL, NULL);
INSERT INTO `games` VALUES (26, '2010-01-23', NULL, 1, NULL, NULL, 'A', 15, 'D', 3, 3, 298, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1264267560&archive=1281131284&start_from=&ucat=10&', NULL, NULL, 'MARK BUTLER', NULL, NULL, NULL);

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=123 ;

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);
INSERT INTO `goals` VALUES (11, 9, 20, 1, 53);
INSERT INTO `goals` VALUES (12, 10, 20, 1, 87);
INSERT INTO `goals` VALUES (13, 11, 6, 1, 89);
INSERT INTO `goals` VALUES (14, 11, 15, 1, 68);
INSERT INTO `goals` VALUES (15, 13, 6, 1, 90);
INSERT INTO `goals` VALUES (16, 13, 49, 1, ;
INSERT INTO `goals` VALUES (17, 15, 55, 1, 68);
INSERT INTO `goals` VALUES (18, 16, 6, 2, 42);
INSERT INTO `goals` VALUES (19, 17, 8, 1, 45);
INSERT INTO `goals` VALUES (20, 18, 6, 2, 90);
INSERT INTO `goals` VALUES (21, 18, 8, 1, 66);
INSERT INTO `goals` VALUES (22, 18, 2, 3, 81);
INSERT INTO `goals` VALUES (23, 18, 53, 1, 31);
INSERT INTO `goals` VALUES (24, 19, 30, 1, 37);
INSERT INTO `goals` VALUES (25, 19, 36, 1, 54);
INSERT INTO `goals` VALUES (26, 20, 6, 1, 38);
INSERT INTO `goals` VALUES (27, 20, 55, 1, 63);
INSERT INTO `goals` VALUES (28, 21, 6, 1, 81);
INSERT INTO `goals` VALUES (29, 21, 46, 1, 90);
INSERT INTO `goals` VALUES (30, 21, 47, 1, 44);
INSERT INTO `goals` VALUES (31, 22, 36, 1, 87);
INSERT INTO `goals` VALUES (32, 22, 36, 1, 88);
INSERT INTO `goals` VALUES (33, 23, 6, 1, 74);
INSERT INTO `goals` VALUES (34, 25, 14, 1, 42);
INSERT INTO `goals` VALUES (35, 26, 6, 1, 90);
INSERT INTO `goals` VALUES (36, 26, 46, 1, 52);
INSERT INTO `goals` VALUES (37, 26, 38, 1, 25);
INSERT INTO `goals` VALUES (38, 27, 33, 1, 6);
INSERT INTO `goals` VALUES (39, 29, 6, 1, 6);
INSERT INTO `goals` VALUES (40, 29, 36, 1, 25);
INSERT INTO `goals` VALUES (41, 30, 53, 1, 70);
INSERT INTO `goals` VALUES (42, 32, 6, 1, 88);
INSERT INTO `goals` VALUES (43, 33, 19, 1, 3);
INSERT INTO `goals` VALUES (44, 36, 17, 1, 68);
INSERT INTO `goals` VALUES (45, 36, 19, 1, 11);
INSERT INTO `goals` VALUES (46, 37, 81, 2, 48);
INSERT INTO `goals` VALUES (47, 37, 17, 1, 90);
INSERT INTO `goals` VALUES (48, 38, 17, 1, 39);
INSERT INTO `goals` VALUES (49, 39, 19, 1, 73);
INSERT INTO `goals` VALUES (50, 40, 30, 1, 65);
INSERT INTO `goals` VALUES (51, 41, 17, 1, 28);

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=103 ;

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);
INSERT INTO `players` VALUES (38, 'Wilson', 'Wayne', '1985-09-12', 3, 'http://www.margate-fc.com/match_gallery/qdig-files/converted-images/Season%202010-11/00.%20Margate%20Squad%20Photos%202010-11/med_IMG_7919.JPG', '2008-06-01', NULL, 'Sorrento, Charlton Athletic, Bishop’s Stortford, Stevenage Borough, Thurrock, Basingstoke, Bromley', 'Perth, Australia', '1/2 Skips', 'Tony Tipple', 'Australian born midfielder who joined from Blue Square South side Bromley in the summer of 2008.<br />\r\n<br />\r\nOriginally with Sorrento under 18''s, based in his home town of Perth he moved to England to join Charlton Athletic’s academy, playing for their youth side, in 2002/03.<br />\r\n<br />\r\nWayne went on the play for Bishop’s Stortford, Stevenage Borough, Thurrock and Basingstoke. <br />\r\n<br />\r\nPlayed in all bar two of Margate''s Ryman Premier games in his first season, finished the season as second top scorer and won the Supporters Player of Year but saw his second season hit by injuries. <br />\r\n<br />\r\nTook temporary charge of the side, with John Keister, for two matches after Mark Butlers resignation in February 2010.', NULL);
INSERT INTO `players` VALUES (80, 'Axon', 'Paul', '1989-05-10', 4, '', '2007-08-01', '2009-11-01', '', '', '', '', 'Paul originally signed for the Blues August 2007 after solid displays in pre-season.\r\n\r\nHaving made 11 appearances for the first team in that season, the nineteen year old was re-signed by new manager Barry Ashby in June 2008 to provide more attacking options but only made three substitute appearances during the season.\r\n\r\nIn 2009/10 Paul just made the bench twice before moving on to Whitstable Town.', NULL);
INSERT INTO `players` VALUES (8, 'Cliff', 'Sam', '1992-03-21', 4, '', '2008-07-01', NULL, '', '', '', '', 'Sam is a centre forward who has impressed the manager during the 2008/09 pre-season and won himself a place in the first team squad.\r\n\r\nOpportunities were few and far between and he had to wait until January for his only appearance, as a substitute.\r\n\r\nHas featured in this summers friendlies and has impressed with his pace.', NULL);

     

    The table I want to join is

    CREATE TABLE `appearances` (
  `app_id` int(11) NOT NULL auto_increment,
  `match` int(11) default NULL,      <--------FK to games.match_id
  `number` int(11) default NULL,
  `player` int(11) default NULL,      <--------FK to players.player_id
  `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);
INSERT INTO `appearances` VALUES (11, 1, 11, 8, 1, 0, 82, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (12, 1, 12, 80, 3, 0, 0, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (13, 1, 14, 67, 3, 0, 0, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (14, 1, 15, 61, 3, 0, 0, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (15, 1, 16, 63, 2, 46, 90, 0, 'N', 5);
INSERT INTO `appearances` VALUES (16, 1, 17, 25, 2, 82, 90, 0, 'N', 11);
INSERT INTO `appearances` VALUES (17, 2, 1, 61, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (18, 2, 2, 28, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (19, 2, 3, 33, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (20, 2, 4, 59, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (21, 2, 5, 69, 1, 0, 83, 1, 'N', NULL);
INSERT INTO `appearances` VALUES (22, 2, 6, 15, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (23, 2, 7, 44, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (24, 2, 8, 38, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (25, 2, 9, 53, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (26, 2, 10, 6, 1, 0, 90, 1, 'N', NULL);
INSERT INTO `appearances` VALUES (27, 2, 11, 8, 1, 0, 73, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (28, 2, 12, 80, 3, 0, 0, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (29, 2, 14, 22, 3, 0, 0, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (30, 2, 15, 5, 3, 0, 0, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (31, 2, 16, 63, 2, 83, 90, 0, 'N', 5);
INSERT INTO `appearances` VALUES (32, 2, 17, 35, 2, 73, 90, 0, 'N', 11);
INSERT INTO `appearances` VALUES (33, 3, 1, 61, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (34, 3, 2, 35, 1, 0, 76, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (35, 3, 3, 33, 1, 0, 56, 1, 'N', NULL);
INSERT INTO `appearances` VALUES (36, 3, 4, 59, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (37, 3, 5, 15, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (38, 3, 6, 44, 1, 0, 90, 1, 'N', NULL);
INSERT INTO `appearances` VALUES (39, 3, 7, 22, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (40, 3, 8, 38, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (41, 3, 9, 53, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (42, 3, 10, 6, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (43, 3, 11, 68, 1, 0, 46, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (44, 3, 12, 8, 2, 56, 90, 0, 'N', 3);
INSERT INTO `appearances` VALUES (45, 3, 14, 48, 2, 46, 90, 1, 'N', 11);
INSERT INTO `appearances` VALUES (46, 3, 15, 69, 2, 76, 90, 0, 'N', 2);
INSERT INTO `appearances` VALUES (47, 3, 16, 5, 3, 0, 0, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (48, 3, 17, 63, 3, 0, 0, 0, 'N', NULL);

     

    I want to retain all of the current COUNT's from the first query but also include some (and some SUM's) from the extra table too.

     

    I would need to still have the basic games.competition "CASE games.competition WHEN........" criteria but there will be 3 variations on each for each type of appearance (1-Starts, 2-Sub, 3-Bench) so am looking along the lines of

    (CASE WHEN games.competition = 1  AND appearances.type = 1 THEN 1 ELSE NULL END) AS lgestarts,
(CASE WHEN games.competition = 1  AND appearances.type = 2 THEN 1 ELSE NULL END) AS lgesub,
(CASE WHEN games.competition = 1  AND appearances.type = 3 THEN 1 ELSE NULL END) AS lgebench,
(CASE WHEN games.competition = 2  AND appearances.type = 1 THEN 1 ELSE NULL END) AS facstarts,
(CASE WHEN games.competition = 2  AND appearances.type = 2 THEN 1 ELSE NULL END) AS facsub, etc etc

     

    I have tried this in a query on its own and it works fine but, despite numerous efforts, I cannot get the two queries to work in one.

     

    I have tried several types of joins (some giving some bizarre results!) and briefly tried to suss out a subquery but this was way beyond my scope!

     

    My last attempt at trying to just get one row from the extra table was 

    $gls = mysql_query("SELECT *,
COUNT(CASE WHEN games.competition = 1 AND appearances.type = 1 THEN 1 ELSE NULL END) AS lgestarts,
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 appearances, goals, games
INNER JOIN players ON appearances.player = players.player_id
INNER JOIN players ON goals.scorer = players.player_id
WHERE goals.match = games.match_id AND appearances.match = games.match_id
AND goals.match = games.match_id AND games.date BETWEEN '2010-07-01' AND '2011-06-31' AND games.competition <> 6
GROUP BY players.player_id ORDER BY tgls DESC, lgegls DESC
");
if (!$gls) {
    die("Query to show fields from table failed");
}

    but this just returned "Query to show fields from table failed".

     

    I am sure that there is a way to do this but I am all googled out! The database is running on MySQL 4.0 and any suggestion would be gratefully received.

     

    Thanks in advance

     

    Steve

    Reversing an ORDER BY statement

    in MySQL Help

    Posted

    This is a follow up from a post that I have already made in the PHP forum (http://www.phpfreaks.com/forums/php-coding-help/problem-with-array_reverse/) as a comment suggested that MySQL may be the way to go.

     

    Basically, I am trying to get the most recent five records by date and then echo them in date ascending order. I can succesfully get the right records using 

    $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");
};

     

    I have tried using array_reverse in the php output but the records still show in descending order.

     

    I have also tried this suggestion from the other post

    $query1 = "SELECT COUNT(`index_field`) FROM `table` WHERE whatever";
$result1 = mysql_query( $query );
$array1 = mysql_fetch_row($result);

$display_num = 5; // the number of records you want displayed
$start = $array1[0] - $display_num; // Total number of results, minus number to display

$query2 = "SELECT `records` FROM `table` ORDER BY `field` ASC LIMIT $start, $display_num";
// etc . . .

    but no records are returned.

     

    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.

     

    Is there a way, via the query that I can reverse the ORDER BY to get the results to show in the right order? The "ORDER BY `date` DESC" is vital as I just want to get the most recent 5 results but then want to order those results by ASC.

     

    Thanks in advance for any suggestions.

     

    Steve

     

    Problem with array_reverse

    in PHP Coding Help

    Posted

    Following your comment about 'someone better with SQL syntax' Pikachu2000, I am going to post this in the MySQL forum.

     

    I hope this is not seen as double posting and if I get a solution in either MySQL or PHP I will mark both solved and post a relevant link in either one.

     

    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.