indexing some arrays to get things in the right order

Hi Experts,

I have a database structure of games and teams, so I have the tables games, teams and gameteam as the look up table.

A game is played on a certain day and time, and I want to display my results like this:

foreach Day
foreach game
foreach team

I have managed to display each day, and then each game, but I cannot display the teams...

whats the best way to do this... ???


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve BinkCommented:
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 ...
$arr = array();
while ($row=mysql_fetch_assoc($result)) {
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

Open in new window

Bernard S.CTOCommented:
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
MWatkins84Author Commented:

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, 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 inner join games on where$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>
	<?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%">

Open in new window

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Bernard S.CTOCommented:
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, 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:
SELECT t.teamname, LEFT(g.time,5) AS my_time,
FROM teams t
  INNER JOIN gameteam gt ON
  INNER JOIN games g ON
ORDER BY, 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
MWatkins84Author Commented:

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??


Bernard S.CTOCommented:
Hmm.. since you have a Game ID, I need to include that in the single query.

I would probably write along the lines of the attached script. Datailed and not coded as smartly as possible, but then it will be easier to test and debug, and then adapt to your situation if you want to.
SELECT t.teamname,, LEFT(g.time,5) AS my_time, 
FROM teams t 
  INNER JOIN gameteam gt ON
  INNER JOIN games g ON
ORDER BY, my_time,, t.teamname";

$result99 = mysqli_query($link, $sql99); 

//init table and variables
echo "<table>";

//loop thru data
while ($row99 = mysqli_fetch_assoc($result99)) {
	$my_date= $row99['date'];
	$my_time= $row99['my_time'];
	$my_id = $row99['id'];
	$my_team= $row99['teamname'];
	if ($the_date <> $my_date) { // reset current date
		// first we need to check if this is the first time: then we have nothing to "close"
		if (!$begin) { // close previous line
			echo "</td></tr>";
		} else $begin=false;
		$the_date = $my_date; // updates current date
		$the_time=''; // resets for the new times
		echo '<tr><td colspan=3>';
		echo "<b>Games held on $my_date</b>";
		echo '</td></tr>';
	} //if date
	if ($the_time<>$my_time) {//reset current time
		if ($the_time>'')// then another time on the same date
			echo '</td></tr>';
		$the_time=$my_time; // update current time
		$the_id=''; // ensure we will change game
		echo '<tr><td>&nbsp;</td><td colspan=2 class="tx_esq_11_red">';
		echo $my_time;
		echo '</td></tr>';
	} // if time
	if ($the_id<>$my_id) {//reset current gae
		if ($the_id>'')// then another game on the same time
			echo '</td></tr>';
		$the_id=$my_id; // update current game
		$newgame=true; // we have initialized this game
		echo '<tr><td>&nbsp;</td><td>&nbsp;</td><td>';
	} // if id
	if ($newgame) $newgame=false; else echo ' ';
	echo $my_team;
} //while
echo '</td></tr></table>';

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.