Display returned MySQL data properly using PHP....

[galvin]

I have two tables based on this structure...

 

INSERT INTO `teams` (`team_id`, `nickname`) VALUES

(1, 'eagles'),

(2, 'redskins'),

(3, 'cowboys'), etc;

 

INSERT INTO `schedule` (`game_id`, `home_id`, `away_id`, `week_no`, `season_no`) VALUES

(1, 1, 3, 1, 1),

(2, 2, 1, 2, 1),

(3, 2, 3, 1, 1),

(4, 3, 1, 3, 1), etc;

 

I want to display the full schedule, which would display similar to this...

 

Eagles:  Week 1 - @ Browns, Week 2 - v. Ravens, Week 3 - @Cardinals, etc.

Cowboys:  Week 1 - @Giants, Week 2 - @ Seahawks, etc.

Etc..

 

So far I have this MySQL below to get all the data that I think I need, but I'm having trouble writing the proper PHP below the MySQL to get the data to display how I would like above.  I'm most confused about getting the "@" to show just for that away games and the "v." to appear for just the home games, and more generally, doing looping to do the data for the Eagles, then move on to the Cowboys, and so on.

 

Can anybody help me understand how to do this (i.e. something which probably should be easier than I'm making it)?

 

$sql = "SELECT 
  h.nickname as home,
  a.nickname as away,
  s.week_no
FROM
  schedule s
  LEFT JOIN teams h ON h.team_id = s.home_id
  LEFT JOIN teams a ON a.team_id = s.away_id
WHERE
  s.season_no = 2012
ORDER BY s.week_no";
$schedule= mysql_query($sql, $connection);
if (!$schedule) {
die("Database query failed: " . mysql_error());
} else {
while ($theschedule=mysql_fetch_array($schedule)) {
	$alldata .= $theschedule['home'] . " " . $theschedule['away'] . " " . $theschedule['week_no'] . "<br>";
	$alldata .= "<br/>";
}
}
?>

[xyph]

Well, I lost my writeup, but since doing this involves selecting redundant/repeat data from SQL, I prefer to simply dump the data into an array, and use PHP to sort/deal with arranging it to display properly. Here's what I'd do, using the sample data from your previous thread.

 

<?php

$db = new MySQLi('localhost','root','','db');

$q = 'SELECT
h.nickname AS home,
a.nickname AS away,
s.week_no
FROM schedule s
INNER JOIN teams h ON s.home_id = h.team_id
LEFT JOIN teams a ON s.away_id = a.team_id
WHERE s.season_no =1';

$r = $db->query($q);

// Placeholder for data
$data = array();
// Loop through results
while( $row = $r->fetch_assoc() ) {
// Store the results in an ordered array. Lets say the
// home team is redskins, away team is cowboys for this
// iteration (week 1)
$data[$row['home']][$row['week_no']] = $row['away'];
// results in $data['redskins'][1] = 'cowboys'
$data[$row['away']][$row['week_no']] = '@'.$row['home'];
// results in $data['cowboys'][1] = '@redskins'

// with this format, we can easily loop through each team
// then each week, and it will contain the correct opponent
// with the correct location
}

// I'd then loop through the placeholder
// If you want to order by team names
ksort($data);
foreach( $data as $team => $weeks ) {
// Sort weeks low to high
ksort($weeks);
// Output the current team
echo "<h3>$team</h3><ul>";
// Loop through weeks
foreach( $weeks as $number => $opponent )
	// Output the current week number and opponent
	echo "<li>Week $number: $opponent";
// Close the list
echo "</ul>";
}

?>

 

output

 

cowboys
   Week 1: @redskins
   Week 3: eagles

eagles
   Week 1: cowboys
   Week 2: @redskins
   Week 3: @cowboys

redskins
   Week 1: cowboys
   Week 2: eagles

 

I know there's missing data, but the sample data I used was incomplete

[galvin]

That does the trick!  I am extremely jealous of your knowledge   Thank you so much! 

[galvin]

Just curious, why did you use INNER JOIN on one and LEFT JOIN on the other?

[xyph]

INNER JOIN because there will always be a matching home team result.

 

LEFT JOIN in case you used away_id as 0 for bye-weeks. In that case, there would be no matching result.

 

INNER JOIN is faster, so we use it when we can.