indexing some arrays to get things in the right order

I am assuming 2 teams per game per day, though that limit is not really important.  Also assuming a query like this:

SELECT * FROM teams a INNER JOIN gameteam b ON a.teamid=b.teamid INNER JOIN games c ON c.gameid=b.gameid ORDER BY c.gameday,c.gameid,a.teamid

<?
if (!($result=mysql_query($query))) {
  // query failed.  report and ...
  die();
}
$arr = array();
while ($row=mysql_fetch_assoc($result)) {
  $arr[$row['gameday']][$row['gameid']][$row['teamid']]=$row;
}
foreach ($arr as $key=>$val) {
  // $key = gameday, $val = array of games
  foreach ($val as $key2=>$val2) {
    // $key2 = gameid, $val2 = array of teams
    foreach ($val2 as $key3=>$val3) {
      // $key3 = teamid, $val3 = row from query
    }
  }
}
?>

Select allOpen in new window

Just to be sure: what really is your problem?
- Is it the SQL query? In that case Routinet's sql query is your answer (of course, you will not use select * but name which fields you want)
- Is it the php code? again Routinet's code will drive you into the solution... although you can probably avoid building the complete array, since data from SQL comes sequentially in the right order and you can simply monitor changes for game and day

Hi,

Here is an image of what I have already, and some of the code.
From the image, you can see that I have the date appear twice... so I wanted to group the games into the dates of the games.

As for the assumptions, teams per game depend on sport, normally two.

sql query, thats in the code below.

I split the code between the php controller script and then the html to display it...

$sql11 = "SELECT  games.id, time, date from games order by date"; //where date='$daydate'
$result11 = mysqli_query($link, $sql11);

    while ($row11 = mysqli_fetch_array($result11))
    {
	$games[] = array('time' => $row11['time'] , 'date' => $row11['date'] , 'id' => $row11['id'] ); 
    }

foreach ($games as $index => $game) 
{
		$gameid= mysqli_real_escape_string($link, $game['id']);
		$sql12="SELECT teamname from teams inner join gameteam on teams.id=teamid inner join games on gameid=games.id where games.id=$gameid"; 
		$result12 = mysqli_query($link, $sql12);
		
		while ($row12 = mysqli_fetch_array($result12))
		{
		$teams["$index"][] = array( 'teamname' => $row12['teamname'] );
		}
		
}



//////////////////


 <?php foreach ($games as $index => $game): ?>
      <table width="40%" align="center">

      <form method="post" action="">
      <tr><td height="18" align="center" valign="top" class="tx_esq_11_red"><strong><?php htmlout($game['date']); ?></strong></td><td height="18" colspan="2" align="center" valign="top" class="tx_esq_11_red"><?php htmlout(substr($game['time'],0,5)); ?></td></tr>
      
      
      
      <tr>
	<?php foreach ($teams["$index"] as $team): ?>
      <td align="center" width="40%" class="tx_esq_11_gray"><strong><?php htmlout($team['teamname']); ?></strong></td>
      <?php endforeach ?>
       <form action="?" method="post">
      <td width="30%">

Select allOpen in new window

footballex.png

1 - General remark: you are not listing teams but matches (which usually involve 2 teams). This strongly suggest that you should have some additional data in your tables, eg the match id. Just keep it for the time as a running thought.

2 - Line 1, I would probably be better rewritten as
$sql11 = "SELECT games.id, time, date from games order by date, time";
and in the example (but probably not in your program)
$sql11 = "SELECT id, time, date from games order by date, time";

3 - And in fact, since you are not using seconds
$sql11 = "SELECT id, LEFT(time,5) AS my_time, date from games order by date, my_time";

This would have the effect to place your matches in the right order.

But I believe you should reconsider your queries and your code.
Using not 2 queries but just one:
$sql99="
SELECT t.teamname, LEFT(g.time,5) AS my_time, g.date
FROM teams t
  INNER JOIN gameteam gt ON t.id=gt.teamid
  INNER JOIN games g ON gt.gameid=g.id
ORDER BY g.date, my_time, t.teamname";

Now you can just read sequentially, "breaking" each time you have a change in date or time:
- if date is unchanged and time changed, it is a new match the same day
- if date is changed, then this is another day
 

Thanks

On point 1. gameid is my matchid.

Sure, I could just use 1 query, but I broke it up before so that I could break it up in the html...
so what do you mean by "breaking".

How do I put this together to get something like the image, but grouping the dates??

Thanks

M