Issue with variables in select statement

bigtall

I'm chugging along on my project, but i've hit a bit of a roadblock. I have a player database for a baseball league and on each team's page i'm trying to output a table of the team record holders for various stats. I've made 2 arrays that hold the category text for the first column and one for the corresponding column names i'll be searching (in the same order as the category array). I post the team's name from a form and it's stored as $data. I'm finding that the results I'm getting are not the correct values if the table had been sorted correctly with the DESC option. Help please!

$category = array("Games Played","Batting Average","At Bats","Hits","Doubles","Triples","Home Runs","Walks","Strikeouts","Stolen Bases","Caught Stealing","Runs Scored","RBI's","Slugging Percentage","On Base Percentage");
$column = array("g","avg","ab","h","doub","trip","hr","bb","k","sb","cs","r","rbi","slg","obp");
$query = "SELECT * FROM hitting WHERE team='" . $POST['data'] . "' ORDER BY '" . $POST['string'] . "' DESC";
$sort = mysql_query($query, $connection) or exit(mysql_error());

print "<center><table border=\"1\">";
print "<tr bgcolor=\"#0000A0\"><th colspan=\"18\">Individual Single Season Records</th></tr>";

print "<tr>
<th>Category</th>
<th>Leader</th>
<th>Record</th>
<th>Year</th>
</tr>";

for ($x=0; $x<15; $x++)
{
$string = $column[$x];
$row = mysql_fetch_assoc($sort);
print "<tr>";
print "<td><center>" . $category[$x] . "</center></td>";
print "<td><center>" . $row[firstname] . " " . $row[lastname] . "</center></td>";
print "<td><center>" . $row[$string] . "</center></td>";
print "<td><center>" . $row[year] . "</center></td>";
print "</tr>";
}

print "</table>";

sarahk

I'd be inclined to change the query to

$query = "SELECT firstname, lastname, category, year, {$_POST['string']} FROM hitting WHERE team='{$_POST['data']}' ORDER BY '{$_POST['string']}' DESC"; 
echo $query;

then check the query. I'm figuring that you just aren't asking the question you think you are asking. Copy the query from your browser into phpMyAdmin and see if it makes sense there. Once you have that sussed you can put the changes back into your script.

bigtall

The server I'm using has version 4.0.15 of mysql and unfortunately I don't have access to phpmyadmin as I'm the 2nd or 3rd user on a hosted site to which I can't get the user name and pw for phpmyadmin.

The query you suggested gives me some errors on the syntax. I changed the $query to:

$query = "SELECT firstname, lastname, '{$POST['column[$x]']}', year FROM hitting WHERE team='{$POST['data']}' ORDER BY '{$_POST['string']}' DESC";

When it prints on the screen I get this result:
SELECT firstname, lastname, '', year FROM hitting WHERE team='ANH' ORDER BY '' DESC

It's not getting the variables for some reason. The form method I'm using IS post. Does the version have something to do with that?

sarahk

at the top of your code put these debug lines

echo '<hr>';
var_dump($_POST);
echo '<hr>';
var_dump($string);
echo '<hr>';

You're getting the team name and the column name so the $_POST is working, but the name isn't what you expect.

Also view the source of the webpage. something's not as expected.

bigtall

Yeah that's exactly it sarahk, one of the variables isn't right. I put that bit of code into the for loop and the var_dump($string) showed the right results when the loop ran and the team name portion of the $_POST was correct but the second part is a bit wierd. Here's what it gave me for that:

array(2) { ["data"]=> string(3) "MIL" ["submit"]=> string(6) "Search" }

The second part of that is from the value of the submit button of the form page. It still doesn't make any sense to me because the team name is correct and the string is holding the right data. So confused...

sarahk

can you post me the url of your test page please.

bigtall

The URL is below. I left in the var_dump in the for loop so you can see the results. Thanks for the help so far and any you can provide with this.

http://baseballmogul.net/mbml/php/teamsearch.htm

Edit: Just pick a team and click submit and it'll take you to the test page.

sarahk

so you just want the leader for each category? I don't see that the field changes at all, nor the order by statement. You just need to put hard code that into the sql. The only thing that ever changes is the team isn't it?

do you have instant messaging, I prefer icq. my number is in my profile.

bigtall

Yeah i'll drop you a line. My ICQ number is in my profile as well.

The goal of this page is to sort the database based on every column and output the first record (who would be the leader in that category) as the result.

sarahk

see the code working

just incase anyone is watching this thread, this is what we ended up with

<?php
$column = array("g"=>"Games Played",
	"avg" => "Batting Average",
	"ab" => "At Bats",
	"h" => "Hits",
	"doub" => "Doubles",
	"trip" => "Triples",
	"hr" => "Home Runs",
	"bb" => "Walks",
	"k" => "Strikeouts",
	"sb" => "Stolen Bases",
	"cs" => "Caught Stealing",
	"r" => "Runs Scored",
	"rbi" => "RBI's",
	"slg" => "Slugging Percentage",
	"obp" => "On Base Percentage"
	); 
$team = $_GET['data'];
?>
<style>
.results {
	font-size : 14px;
	text-align : center;
}
.results TH {
	color : Red;
	font-weight : bold;
}
.resultbanner {
	background-color : #0000A0;
	color : White;
}
</style>
<center><table border='1' class='results'>
  <tr><td colspan='4' class='resultbanner'>Individual Single Season Records</td></tr>
  <tr> 
	<th>Category</th> 
	<th>Leader</th> 
	<th>Record</th> 
	<th>Year</th> 
  </tr>
<?php
foreach($column as $key => $val)
{
  if ($key == 'avg')
    $query = "SELECT firstname, lastname, h/ab as avg, year FROM hitting WHERE team='{$team}' ORDER BY 3 DESC limit 0,1";   

  elseif ($key == 'slg') // I don't even know what this means!
    $query = "SELECT firstname, lastname, h/ab as slg, year FROM hitting WHERE team='{$team}' ORDER BY 3 DESC limit 0,1";   

  else
    $query = "SELECT firstname, lastname, {$key}, year FROM hitting WHERE team='{$team}' ORDER BY '{$key}' DESC limit 0,1";   

  $sort = mysql_query($query, $connection) or exit(mysql_error()); 

  if ($row = mysql_fetch_assoc($sort))
  {
    echo "<tr>
	<td>{$val}</td>
	<td>{$row[firstname]} {$row[lastname]}</td>
	<td>{$row[$key]}</td>
	<td>{$row[year]}</td>
	</tr>"; 
  }
}//foreach
echo '</table>'; 
?>

bigtall

I've tweaked the code a bit just for formatting and suchlike, but here's what I have right now. Thanks again to sarahk. I don't think I can say it enough. Days of frustration have melted away!

<?php
$column = array("g"=>"Games Played",
	"avg" => "Batting Average",
	"ab" => "At Bats",
	"h" => "Hits",
	"doub" => "Doubles",
	"trip" => "Triples",
	"hr" => "Home Runs",
	"bb" => "Walks",
	"k" => "Strikeouts",
	"sb" => "Stolen Bases",
	"cs" => "Caught Stealing",
	"r" => "Runs Scored",
	"rbi" => "RBI's",
	"slg" => "Slugging Percentage",
	"obp" => "On Base Percentage"
	); 
$team = $_GET['data'];
?>
<style>
.results {
	font-size : 14px;
	text-align : center;
}
.results TH {
	color : Red;
	font-weight : bold;
}
.resultbanner {
	background-color : #0000A0;
	color : White;
}
</style>
<center><table border='1' class='results'>
  <tr><td colspan='4' class='resultbanner'><b>Individual Single Season Records</b></td></tr>
  <tr> 
	<th>Category</th> 
	<th>Leader</th> 
	<th>Record</th> 
	<th>Year</th> 
  </tr>
<?php
foreach($column as $key => $val)
{
if($z % '2' == '0')
 {
 $tr="tr";
 }
 else {
  $tr="tr bgcolor=\"#E7FAED\"";
  }

  if ($key == 'avg' OR 'slg' OR 'obp')
  {
    $query = "SELECT firstname, lastname, {$key}, year FROM hitting WHERE team='{$team}' AND ab>'350' ORDER BY 3 DESC limit 0,1";
  }   

  $sort = mysql_query($query, $connection) or exit(mysql_error()); 

  if ($row = mysql_fetch_assoc($sort))
  {
    echo "<$tr>
	<td>{$val}</td>
	<td>{$row[firstname]} {$row[lastname]}</td>
	<td>{$row[$key]}</td>
	<td>{$row[year]}</td>
	</tr>"; 
  }

$z++;
}//foreach
echo '</table>';
?>