indexing some arrays to get things in the right order

Posted on 2010-04-02
Medium Priority
Last Modified: 2013-12-12
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... ???


Question by:MWatkins84
  • 3
  • 2
LVL 51

Expert Comment

by:Steve Bink
ID: 29523667
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

LVL 29

Expert Comment

ID: 29543473
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

Author Comment

ID: 29559329

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

LVL 29

Expert Comment

ID: 29561389
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:
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

Author Comment

ID: 29562431

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


LVL 29

Accepted Solution

fibo earned 2000 total points
ID: 29568880
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, g.id, 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, g.id, 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


Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
This article discusses how to implement server side field validation and display customized error messages to the client.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question