khagler
12 Dec 2012, 10:24 PM
Having trouble manipulating the way that MySql data is displayed when trying to output it to an html table. Here is an example of first table file, which works fine, but I do not want to display in this fashion.
http://www.spotonsurvey.com/images/pic1.jpg
As you can see above, there are 3 users who have made picks for three games. For these purposes, just ignore ATS column. I want the table, when displayed, to look like below...
http://www.spotonsurvey.com/images/pic2.jpg
Two issues here,
1) I don't want username in the left hand column to show up all three times (this happens because each individual pick is linked to the user who made the pick, so 3 games = user name showing up 3 times). Just once. So 3 rows total instead of nine.
2) Want picks to now display horizontally under the corresponding th for that particular game.
-Row one would be TCU / Baylor / Kansas St
-Row two would be Oklahoma / Oklahoma St / Kansas St
-Row three would be TCU / Texas / Oklahoma St
My code for first table looks like this:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style>
body { font-family: Verdana, Arial, sans-serif; }
table, th, tr, td { border-collapse: collapse; }
.su_win { background-color: yellow; }
.ats_win { background-color: #94d67a; }
.loss { color: red; border-color: black; text-decoration: line-through; }
</style>
</head>
<body>
<?php
//connects to Db
$db_host = "localhost";
$db_username = "root";
$db_pass = "";
$db_name = "pete_pool";
@mysql_connect("$db_host","$db_username","$db_pass") or die("Could not connect to MySQL");
@mysql_select_db("$db_name") or die("No database");
$query = "SELECT * FROM picks
INNER JOIN schedules
ON picks.game_id = schedules.game_id
INNER JOIN players
ON picks.user = players.user_name";
$result = mysql_query($query) or die(mysql_error());
echo "<table border cellpadding=3>";
echo "<tr>";
echo "<th>User</th>";
echo "<th>Game</th>";
echo "<th>SU</th>";
echo "<th>ATS</th>";
echo "</tr>";
while($row = mysql_fetch_array($result)){
// add one to overall_rec column for every win
if ($row['su'] == $row['home_team']) {
if ($row['home_score'] > $row['away_score']) {
//mysql_query("UPDATE players SET overall_rec = overall_rec + 1 WHERE user_name='{$row['user']}'");
}
} else if ($row['su'] == $row['away_team']) {
if ($row['home_score'] < $row['away_score']) {
//mysql_query("UPDATE players SET overall_rec = overall_rec + 1 WHERE user_name='{$row['user']}'");
}
}
// display picks in html table
echo "<tr><td>".$row['user_name']."</td> ";
echo "<td>".$row['home_team']." ".$row['spread']." vs. ".$row['away_team']." (".$row['ml_odds'].")</td> ";
if ($row['su'] == $row['home_team']) {
if ($row['home_score'] > $row['away_score']) {
echo "<td class='su_win'>"; // highlight su wins yellow
} else {
echo "<td class='loss'>"; // strikethrough losses
}
} else if ($row['su'] == $row['away_team']) {
if ($row['home_score'] < $row['away_score']) {
echo "<td class='su_win'>"; // highlight su wins yellow
} else {
echo "<td class='loss'>"; // strikethrough losses
}
}
echo $row['su']."</td> ";
if ($row['ats'] == $row['home_team']) {
if ($row['home_score'] + $row['spread'] > $row['away_score']) {
echo "<td class='ats_win'>"; // highlight ats wins green
} else {
echo "<td class='loss'>"; // strikethrough losses
}
} else if ($row['ats'] == $row['away_team']) {
if ($row['home_score'] + $row['spread'] < $row['away_score']) {
echo "<td class='ats_win'>"; // highlight ats wins green
} else {
echo "<td class='loss'>"; // strikethrough losses
}
}
echo $row['ats']."</td></tr>";
}
echo "</table>";
?>
</body>
</html>
So far, this is what I have for second table:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style>
body { font-family: Verdana, Arial, sans-serif; }
table, th, tr, td { border-collapse: collapse; }
th { width: 150px; font-size: 12px; }
.su_win { background-color: yellow; }
.ats_win { background-color: #94d67a; }
.loss { color: red; border-color: black; text-decoration: line-through; }
.ml_disp { font-size: 0.9em; font-style: italic; color: #999; }
.spreadDisp { color: red; }
</style>
</head>
<body>
<?php
//connects to Db
$db_host = "localhost";
$db_username = "root";
$db_pass = "";
$db_name = "pete_pool";
@mysql_connect("$db_host","$db_username","$db_pass") or die("Could not connect to MySQL");
@mysql_select_db("$db_name") or die("No database");
$query = "SELECT * FROM picks
INNER JOIN schedules
ON picks.game_id = schedules.game_id
INNER JOIN players
ON picks.user = players.user_name";
$query2 = 'SELECT home_team, away_team, spread, ml_odds FROM schedules';
$query3 = 'SELECT user_name FROM players';
$result = mysql_query($query) or die(mysql_error());
$result2 = mysql_query($query2) or die(mysql_error());
$result3 = mysql_query($query3) or die(mysql_error());
echo "<table border cellpadding=3>";
echo "<tr>";
echo "<th style='padding-top: 20px; color: blue; border-color: black;'>Name</th>";
while($row = mysql_fetch_array($result2)){
echo "<th><span class='home_hi'>".$row['home_team']."</span> ";
if ($row['spread'] > 0) {
echo " <span class='spreadDisp'>+";
} else {
echo " <span class='spreadDisp'>";
}
echo $row['spread']."</span> <br />vs. ".$row['away_team']." <span class='ml_disp'>(".$row['ml_odds'].")</span></th> ";
}
while($row = mysql_fetch_array($result)) {
echo "<tr><td>".$row['user_name']."</td> ";
echo "<td>".$row['su']."</td>";
}
echo "</table>";
?>
</body>
</html>
Any ideas?
http://www.spotonsurvey.com/images/pic1.jpg
As you can see above, there are 3 users who have made picks for three games. For these purposes, just ignore ATS column. I want the table, when displayed, to look like below...
http://www.spotonsurvey.com/images/pic2.jpg
Two issues here,
1) I don't want username in the left hand column to show up all three times (this happens because each individual pick is linked to the user who made the pick, so 3 games = user name showing up 3 times). Just once. So 3 rows total instead of nine.
2) Want picks to now display horizontally under the corresponding th for that particular game.
-Row one would be TCU / Baylor / Kansas St
-Row two would be Oklahoma / Oklahoma St / Kansas St
-Row three would be TCU / Texas / Oklahoma St
My code for first table looks like this:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style>
body { font-family: Verdana, Arial, sans-serif; }
table, th, tr, td { border-collapse: collapse; }
.su_win { background-color: yellow; }
.ats_win { background-color: #94d67a; }
.loss { color: red; border-color: black; text-decoration: line-through; }
</style>
</head>
<body>
<?php
//connects to Db
$db_host = "localhost";
$db_username = "root";
$db_pass = "";
$db_name = "pete_pool";
@mysql_connect("$db_host","$db_username","$db_pass") or die("Could not connect to MySQL");
@mysql_select_db("$db_name") or die("No database");
$query = "SELECT * FROM picks
INNER JOIN schedules
ON picks.game_id = schedules.game_id
INNER JOIN players
ON picks.user = players.user_name";
$result = mysql_query($query) or die(mysql_error());
echo "<table border cellpadding=3>";
echo "<tr>";
echo "<th>User</th>";
echo "<th>Game</th>";
echo "<th>SU</th>";
echo "<th>ATS</th>";
echo "</tr>";
while($row = mysql_fetch_array($result)){
// add one to overall_rec column for every win
if ($row['su'] == $row['home_team']) {
if ($row['home_score'] > $row['away_score']) {
//mysql_query("UPDATE players SET overall_rec = overall_rec + 1 WHERE user_name='{$row['user']}'");
}
} else if ($row['su'] == $row['away_team']) {
if ($row['home_score'] < $row['away_score']) {
//mysql_query("UPDATE players SET overall_rec = overall_rec + 1 WHERE user_name='{$row['user']}'");
}
}
// display picks in html table
echo "<tr><td>".$row['user_name']."</td> ";
echo "<td>".$row['home_team']." ".$row['spread']." vs. ".$row['away_team']." (".$row['ml_odds'].")</td> ";
if ($row['su'] == $row['home_team']) {
if ($row['home_score'] > $row['away_score']) {
echo "<td class='su_win'>"; // highlight su wins yellow
} else {
echo "<td class='loss'>"; // strikethrough losses
}
} else if ($row['su'] == $row['away_team']) {
if ($row['home_score'] < $row['away_score']) {
echo "<td class='su_win'>"; // highlight su wins yellow
} else {
echo "<td class='loss'>"; // strikethrough losses
}
}
echo $row['su']."</td> ";
if ($row['ats'] == $row['home_team']) {
if ($row['home_score'] + $row['spread'] > $row['away_score']) {
echo "<td class='ats_win'>"; // highlight ats wins green
} else {
echo "<td class='loss'>"; // strikethrough losses
}
} else if ($row['ats'] == $row['away_team']) {
if ($row['home_score'] + $row['spread'] < $row['away_score']) {
echo "<td class='ats_win'>"; // highlight ats wins green
} else {
echo "<td class='loss'>"; // strikethrough losses
}
}
echo $row['ats']."</td></tr>";
}
echo "</table>";
?>
</body>
</html>
So far, this is what I have for second table:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style>
body { font-family: Verdana, Arial, sans-serif; }
table, th, tr, td { border-collapse: collapse; }
th { width: 150px; font-size: 12px; }
.su_win { background-color: yellow; }
.ats_win { background-color: #94d67a; }
.loss { color: red; border-color: black; text-decoration: line-through; }
.ml_disp { font-size: 0.9em; font-style: italic; color: #999; }
.spreadDisp { color: red; }
</style>
</head>
<body>
<?php
//connects to Db
$db_host = "localhost";
$db_username = "root";
$db_pass = "";
$db_name = "pete_pool";
@mysql_connect("$db_host","$db_username","$db_pass") or die("Could not connect to MySQL");
@mysql_select_db("$db_name") or die("No database");
$query = "SELECT * FROM picks
INNER JOIN schedules
ON picks.game_id = schedules.game_id
INNER JOIN players
ON picks.user = players.user_name";
$query2 = 'SELECT home_team, away_team, spread, ml_odds FROM schedules';
$query3 = 'SELECT user_name FROM players';
$result = mysql_query($query) or die(mysql_error());
$result2 = mysql_query($query2) or die(mysql_error());
$result3 = mysql_query($query3) or die(mysql_error());
echo "<table border cellpadding=3>";
echo "<tr>";
echo "<th style='padding-top: 20px; color: blue; border-color: black;'>Name</th>";
while($row = mysql_fetch_array($result2)){
echo "<th><span class='home_hi'>".$row['home_team']."</span> ";
if ($row['spread'] > 0) {
echo " <span class='spreadDisp'>+";
} else {
echo " <span class='spreadDisp'>";
}
echo $row['spread']."</span> <br />vs. ".$row['away_team']." <span class='ml_disp'>(".$row['ml_odds'].")</span></th> ";
}
while($row = mysql_fetch_array($result)) {
echo "<tr><td>".$row['user_name']."</td> ";
echo "<td>".$row['su']."</td>";
}
echo "</table>";
?>
</body>
</html>
Any ideas?