Using a sub query in a WHERE/IN/HAVING clause

[MargateSteve]

Once again, I appear to have bitten off more than I can chew with a query I am trying to put together!
 
I am trying to show the fixtures for certain teams in a football/soccer league. Basically I what to show the remaining matches for teams trying to reach the play-offs and set-them out as in the image below.
 

 
There will be a bit more to it eventually but I think that once I get to that stage, the rest should (a big should) be within my capabilities. At the moment I just cannot get the games for the relevant teams.

So far, I have got the following query which gets me the required teams (those currently ranked 2-8 in the standings) and returns their rank, points and team id.

Select
  rnk.rownum,
  rnk.P,
  rnk.Tid
From
  (Select
    u.Tm,
    @rownum := @rownum + 1 As rownum,
    u.P,
    u.Tid
  From
    (Select
      Left(t.team_name, 18) As Tm,
      t.team_id As Tid,
      Coalesce(Sum(Case
        When (g.home_team = t.team_id) And (g.home_goals > g.away_goals) Or
        (g.away_team = t.team_id) And (g.home_goals < g.away_goals) Then 3
        Else 0 End) + Sum(Case
        When (g.home_team = t.team_id) And (g.home_goals = g.away_goals) Or
        (g.away_team = t.team_id) And (g.home_goals = g.away_goals) Then 1
        Else 0 End)) As P,
      Coalesce(Sum(Case When g.home_team = t.team_id Then g.home_goals
        When g.away_team = t.team_id Then g.away_goals End) - Sum(Case
        When g.home_team = t.team_id Then g.away_goals
        When g.away_team = t.team_id Then g.home_goals End), 0) As GD,
      Coalesce(Sum(Case When g.home_team = t.team_id Then g.home_goals
        When g.away_team = t.team_id Then g.away_goals End), 0) As GF
    From
      teams t Left Join
      all_games g On t.team_id In (g.home_team, g.away_team) Left Join
      seasons As S On g.date Between S.season_start And S.season_end Left Join
      deductions d On d.team = t.team_id And d.season = S.season_id
    Where
      g.comp = '1' And
      S.season_id = 106 And
      g.playoff Is Null
    Group By
      t.team_id
    Order By
      P Desc,
      GD Desc,
      GF Desc,
      Left(t.team_name, 18)) u,
    (Select
      @rownum := 0) r) rnk
Where
  rnk.rownum Between 2 And 8

The first problem is finding all of the matches where any of the team id's (Tid) show in 'all_games.home_team' or 'all_games.away_team'. I have tried using IN and HAVING CLAUSES and used GROUP_CONCAT on Tid but the closest I got was returning the matches for one team. This was the first Tid grouped by GROUP_CONCAT.

 

The other thing that will be vital so probably best for me to solve it early is that I need to show a row for every date that any of the teams have a game on. If one team is playing on a certain date, there must be a row for all teams.

 

It also might be worth mentioning that, as in my example image at the top, there will be times when the when a certain match will be shown twice in a row (once in the home team column and once in the visiting teams column) so I am not sure if those games will need to be pulled out twice?

 

Can anyone give me a starting point on this as I am completely stumped. I have seen this done on other sites but obviously do not know how! I have attached the relevant table data.

 

Thanks in advance for any suggestions.

 

Steve

all_games.txt

[MargateSteve]

Having read back through it, I think that unless anyone has seen what I am trying to do elsewhere it probably does not make a lot of sense so I am going to try to explain it better, It is difficult because I do not know the name of the functionality I am after!

 

• I need to run a query on the standings and find out which teams are ranked between 2nd and 8th. These will be my column headers.
• I need to then find out all of the future dates that there is a game featuring any of these teams. On a particular date only one team may have a game or all may have one. The opponents in these games will feature any of the teams in the league, not just those ranked 2-8. These will be my row headers.
• In the grid, whenever a date corresponds with a team having a game, details of that game should be shown (home_team,away_team will be fine for now as I am sure I can use php to show just the opponent and whether it is a home or away fixture). if a team does not have a game on the corresponding date, then the cell needs to just be left blank. If two of the featured teams (ranked 2-8 ) are playing each other, then that match will show twice in a row, once for the home team and once for the visiting team.
• Finally, I would ideally like to use the standings again to show the ranking of the opposing team. So using my example image in the previous post, the first cell would actually read H v Team 2 (10) with '(10)' being Team  2's rank in the standings.

I hope that may have made things clearer and not confused it more!

 

Thanks

Steve

Edited March 23, 2013 by MargateSteve

[Barand]

I've use a table "top8", which I've assumed would be a temporary table created from your other query, which contains the ids of the teams you want listed.

 

$sql = "SELECT team_name
        FROM top8
        INNER JOIN teams USING (team_id)
        ORDER BY team_name";
$res = $db->query($sql);
$blank_array = array();
while ($row = $res->fetch_row()) {
    $blank_array[$row[0]] = ' ';
}

$output = "<tr><th>Date</th><th>" . join('</th><th>', array_keys($blank_array)) . "</th></tr>\n";

$sql = "SELECT team, date, fixture
FROM
    (
    SELECT h.team_name as team, date,
        CONCAT('H ', a.team_name) as fixture
    FROM all_games g
        INNER JOIN teams h ON g.home_team = h.team_id
        INNER JOIN teams a ON g.away_team = a.team_id
        INNER JOIN top8 th ON th.team_id = g.home_team
        /*INNER JOIN top8 ta ON ta.team_id = g.away_team*/
    WHERE date > CURDATE()
    UNION 
    SELECT a.team_name as team, date,
        CONCAT('A ', h.team_name) as fixture
    FROM all_games g
        INNER JOIN teams h ON g.home_team = h.team_id
        INNER JOIN teams a ON g.away_team = a.team_id
        /*INNER JOIN top8 th ON th.team_id = g.home_team*/
        INNER JOIN top8 ta ON ta.team_id = g.away_team
    WHERE date > CURDATE()
    ) fixtures
ORDER BY date, team";
$res = $db->query($sql);
$currdate = '';
while (list($tm, $dt, $fix) = $res->fetch_row()) {
    if ($currdate != $dt) {
        if ($currdate) {
            $output .= '<tr><th>' . date('d-M', strtotime($dt)) . '<td>' .
                join('</td><td>', $fixarray) . "</td></tr>\n";
        }
        $currdate = $dt;
        $fixarray = $blank_array;
    }
    $fixarray[$tm] = $fix;
}
$output .= '<tr><th>' . date('d-M', strtotime($dt)) . '<td>' .
                join('</td><td>', $fixarray) . "</td></tr>\n";
?>

<table border="1" cellpadding="3">
    <?php echo $output ?>
</table>

PS just noticed you final requirement. If you put all teams with ranks in the temp table but only extract on ranks 2-8 then you should be able to easily get the final ranking didplay requirement

Edited March 23, 2013 by Barand

[MargateSteve]

Thanks Barand.

 

Unfortunately I just tried to give it a go and it fell over, probably due to my connection set-up. The error I am getting is 'Fatal error: Call to a member function query() on a non-object in /homepages/46/d98455693/htdocs/content/1st_team/runin.php on line 525'. Line 525 is 

$res = $db->query($sql);

 

I tried changing this to '$res = mysql_query($sql);' which is what I usually use, which seemed to get past that but that gave me Fatal error: Call to a member function query() on a non-object in /homepages/46/d98455693/htdocs/content/1st_team/runin.php on line 525, which is

while ($row = $res->fetch_row()) {
    $blank_array[$row[0]] = ' ';
}

 

I would I be right to assume that $db->query is expecting me to have $db as part of my connection? If this is what is wrong, the connection script I am using for this is  

$hostname_Test = "host";
$database_Test = "dbname";
$username_Test = "user";
$password_Test = "pass";
$Test = mysql_pconnect($hostname_Test, $username_Test, $password_Test) or trigger_error(mysql_error(),E_USER_ERROR); 

 

I have tried replacing $db with both $database_Test and $Test but neither of these worked.

 

Is there anything I need to change with my connection code to make this work? Forgive me if I am way off the mark but everything I have done to date has been in basic and standard php, this bit of code is new to me! 

 

Thanks

Steve

Edited March 23, 2013 by MargateSteve

[Barand]

in my code

 

 

$db = new mysqli(HOST, USER, PASSWORD, DATABASE);

[MargateSteve]

Thanks Barand, that solved that part.

 

As I have not used temporary tables before I am going to have a read up on those before progressing. Will report back once I have given it a go.

 

Steve

[Barand]

To create

 

 

CREATE TEMPORARY TABLE SELECT .....

 

A temp table exists until the connection is closed

[MargateSteve]

I have only just had a chance to look at this as I have been working away. I am still getting Fatal error: Call to a member function fetch_row() on a non-object in/homepages/46/d98455693/htdocs/content/1st_team/runin.php on line 580, which is 'while ($row = $res->fetch_row()) {'

Any suggestions on what I might be doing wrong?

$sql = "create temporary table top8
Select
  rnk.rownum,
  rnk.P,
  rnk.Tid
From
  (Select
    u.Tm,
    @rownum := @rownum + 1 As rownum,
    u.P,
    u.Tid
  From
    (Select
      Left(t.team_name, 18) As Tm,
      t.team_id As Tid,
      Coalesce(Sum(Case
        When (g.home_team = t.team_id) And (g.home_goals > g.away_goals) Or
        (g.away_team = t.team_id) And (g.home_goals < g.away_goals) Then 3
        Else 0 End) + Sum(Case
        When (g.home_team = t.team_id) And (g.home_goals = g.away_goals) Or
        (g.away_team = t.team_id) And (g.home_goals = g.away_goals) Then 1
        Else 0 End)) As P,
      Coalesce(Sum(Case When g.home_team = t.team_id Then g.home_goals
        When g.away_team = t.team_id Then g.away_goals End) - Sum(Case
        When g.home_team = t.team_id Then g.away_goals
        When g.away_team = t.team_id Then g.home_goals End), 0) As GD,
      Coalesce(Sum(Case When g.home_team = t.team_id Then g.home_goals
        When g.away_team = t.team_id Then g.away_goals End), 0) As GF
    From
      teams t Left Join
      all_games g On t.team_id In (g.home_team, g.away_team) Left Join
      seasons As S On g.date Between S.season_start And S.season_end Left Join
      deductions d On d.team = t.team_id And d.season = S.season_id
    Where
      g.comp = '1' And
      S.season_id = 106 And
      g.playoff Is Null
    Group By
      t.team_id
    Order By
      P Desc,
      GD Desc,
      GF Desc,
      Left(t.team_name, 18)) u,
    (Select
      @rownum := 0) r) rnk
Where
  rnk.rownum Between 2 And 8
	  ";
	
	$sql = "SELECT u.Tm
        FROM top8
        INNER JOIN teams USING (u.Tid)
        ORDER BY u.Tm";
$res = $db->query($sql);
$blank_array = array();
while ($row = $res->fetch_row()) {
    $blank_array[$row[0]] = ' ';
}

$output = "<tr><th>Date</th><th>" . join('</th><th>', array_keys($blank_array)) . "</th></tr>\n";

$sql = "SELECT team, date, fixture
FROM
    (
    SELECT h.team_name as team, date,
        CONCAT('H ', a.team_name) as fixture
    FROM all_games g
        INNER JOIN teams h ON g.home_team = h.team_id
        INNER JOIN teams a ON g.away_team = a.team_id
        INNER JOIN top8 th ON th.team_id = g.home_team
        /*INNER JOIN top8 ta ON ta.team_id = g.away_team*/
    WHERE date > CURDATE()
    UNION 
    SELECT a.team_name as team, date,
        CONCAT('A ', h.team_name) as fixture
    FROM all_games g
        INNER JOIN teams h ON g.home_team = h.team_id
        INNER JOIN teams a ON g.away_team = a.team_id
        /*INNER JOIN top8 th ON th.team_id = g.home_team*/
        INNER JOIN top8 ta ON ta.team_id = g.away_team
    WHERE date > CURDATE()
    ) fixtures
ORDER BY date, team";
$res = $db->query($sql);
$currdate = '';
while (list($tm, $dt, $fix) = $res->fetch_row()) {
    if ($currdate != $dt) {
        if ($currdate) {
            $output .= '<tr><th>' . date('d-M', strtotime($dt)) . '<td>' .
                join('</td><td>', $fixarray) . "</td></tr>\n";
        }
        $currdate = $dt;
        $fixarray = $blank_array;
    }
    $fixarray[$tm] = $fix;
}
$output .= '<tr><th>' . date('d-M', strtotime($dt)) . '<td>' .
                join('</td><td>', $fixarray) . "</td></tr>\n";

Thanks

Steve

[Barand]

$res should be a result object returned by a mysqli object query call