Question about "isset"

bayken37 · 01-25-2013, 08:52 PM

I'm wondering if the following is even possible, and if so, how do I go about fixing what I already have.

So when I go to "stats.php?STAT_ID=X" I want it to grab and run a query based on what X is.

PHP Code:


			
if (isset($_GET['STAT_ID']))

{

    if (get_magic_quotes_gpc())

    {

        $_GET['STAT_ID'] = stripslashes($_GET['STAT_ID']);

    }

    $stat_id = mysql_real_escape_string($_GET['STAT_ID']);





echo '<table width="575" align="center" cellspacing="0">';

echo '<tr>';

echo '<td valign="top" align="left" width="575">';



$sql = 'SELECT Pos, FName, LName, Year, \'' . $stat_id . '\' '

        . ' FROM batters_career_stats '

        . ' GROUP BY Pos, FName, LName, Year '

        . ' ORDER BY \'' . $stat_id . '\' DESC '

        . ' LIMIT 50'; 

     

$result = mysql_query($sql) or die(mysql_error());





echo "<table border='0' cellpadding='2' cellspacing='0' width='575'>";

echo "<tr class='header'>  <td width='15'>Rk.</td> <td align='right' width='385'>Player</td> <td width='175' align='right'>Stat</td></tr>";



$i=0

while($row = mysql_fetch_assoc( $result ))  

{

    printf('<tr class="odd">

        <td align="right">%d.</td>

        <td align="right">%s %s, %d</td>

        <td align="right">%d</td>

        </tr>' . PHP_EOL, $i, $row['FName'], $row['LName'], $row['Year'], $row['?????']);

} 



echo "</table>";



}

When I've played around with what goes in the ?????? I've only come away with errors. Any ideas?

Thanks in advance!

Old Pedant · 01-25-2013, 09:15 PM

Wrong:

Code:

       . ' ORDER BY \'' . $stat_id . '\' DESC '

Right:

Code:

       . ' ORDER BY `'' . $stat_id . '` DESC '

Those characters I changed to are BACK TICKS. The ` character usually shares the keyboard key with the ~ tilde character.

I am *assuming* that $stat_id is supposed to be the name of an existing field (a.k.a. "column", though that's a misnomer) in the table.

And if that is so, then THIS line is also a mistake:

Code:

    $stat_id = mysql_real_escape_string($_GET['STAT_ID']);

you do *NOT* want to escape a field name the same way you would a text data item.

Unless you have field names that include spaces or other non-standard characters, I wouldn't use

Code:

    stripslashes($_GET['STAT_ID']);

either. Almost surely all you want to do is verify that $_GET['STAT_ID'] contains no characters other than letters, digits, and maybe underlines (if you use underlines in your field names). If not, simply reject the entire request, because then STAT_ID can't possibly be a valid field name.

Tell you what, give me a list of *ALL* the field names in your batters_career_stats table and we will rewrite this simpler.

bayken37 · 01-25-2013, 09:43 PM

So I've got: PNum, Team_ID, POS_ID, Pos, FName, LName, Team, Year, G, AB, H, R, 2B, 3B, HR, RBI, BB, HP, K, SB, CS

So yeah I'm looking to write a simple code and .php file that will get the the top 50 for the above stat (all those after Year) that I choose, rather than writing a different .php file for each.

Along the same lines, not sure if it's possible, but say I needed to use multiple stats in the same query. For instance AVG would be H/AB. I could obviously just simply write another .php file for that specific stat, but if this could somehow be dealt with in one code that would be great.

Old Pedant · 01-25-2013, 10:49 PM

So instead of mucking with all this stuff:

Code:

    if (get_magic_quotes_gpc()) 
    { 
        $_GET['STAT_ID'] = stripslashes($_GET['STAT_ID']); 
    } 
    $stat_id = mysql_real_escape_string($_GET['STAT_ID']);

which is utterly wrong, anyway, for a *FIELD* name, why not simply look for what is allowed and reject anything else?

Code:

<html>
<head>
<style type="text/css">
tr.even { background-color: white; }
tr.odd  { background-color: #eeeeee; }
tr.even td, tr.odd td { text-align: right; }
</styl>
</head>
<body>
<?php

if (! isset($_GET['STAT_ID'])) 
{ 
    echo "Invalid selection...hit BACK and try again.";
    exit();  // maybe should be just exit without ()?
}

$fld = $_GET["STAT_ID"];
switch ( $fld )
{
    case "G": case "AB": case "H": case "R": case "2B": case "3B": 
    case "HR": case "RBI": case "BB": case "HP": case "K": case "SB": case "CS" :
        // simple field name...use it AS IS
        $fname = $fld;
        $sname = $fld;
        $pname = $fld;
        break;

    case "AVG":
        // batting average:
        $fname = " (H/AB) AS `AVG` ";
        $sname = "`AVG`";
        $pname = "AVG";
        break;

    case "OBP":
        // on base percentage:
        $fname = " ((H+HP+BB)/AB) AS OBP ";
        $sname = "OBP"
        $pname = "OBP";
        break;

    default:
        echo "Invalid selection...hit BACK and try again.";
        exit();  // maybe should be just exit without ()?
}
?>

<table width="575" align="center" cellspacing="0">
<tr>
    <td>Rank</td>
    <td>Player</td>
    <td>Position</td>
    <td>Year</td>
    <td><?php echo $pname; ?></td>
</tr>
<?
... make the db connection here ...

$sql = "SELECT CONCAT(Fname,' ',Lname) AS player, Pos, Year, $fname " 
     . " FROM batters_career_stats ORDER BY $sname DESC LIMIT 50";
$result = mysql_query($sql) or die(mysql_error()); 

$rank = 1;
while($row = mysql_fetch_assoc( $result ))   
{ 
    $player = $row["player"];
    $pos    = $row["pos"];
    $year   = $row["year"];
    $stat   = $row[$pname];
    $cls = ($rand % 2 == 0 ) ? "even" : "odd";
    echo "<tr class="$cls"><td>$rank</td><td>$player</td><td>$pos</td><td>$year</td><td>$stat</td></tr>\n";

    ++$rank;
}  
?>
</table>
</body>
</html>

Please understand that I don't use PHP, so apologies if there are minor PHP errors there.

Old Pedant · 01-25-2013, 10:50 PM

And I think I have OBP correct. You can easily fix that. And you can see how to derive other stats, as well. No?

Note that AVG needs those back ticks around it `AVG` because AVG is a MySQL keyword.

It can't hurt to put them around other fields.

bayken37 · 01-25-2013, 11:23 PM

Hmm, after tying up a few loose ends on the php side, I'm just getting a blank page.

Looking at the html source it's showing a repetition of the following:

PHP Code:


			
<tr class="$cls"><td>$rank</td><td>$player</td><td>$pos</td><td>$year</td><td>$stat</td></tr>n

Old Pedant · 01-25-2013, 11:39 PM

Whoops...I had " inside of ".

Code:

    echo "<tr class=\"$cls\"><td>$rank</td><td>$player</td><td>$pos</td><td>$year</td><td>$stat</td></tr>\n";

Need to "escape" the internal quotes.

Hey, I told you I don't code in PHP. <grin/> (But I should have caught that one...so should you.)

Old Pedant · 01-25-2013, 11:40 PM

*IF* that doesn't work--but I think it should--you can go back to your printf() solution.

But just use my variable names, including especially $stat, in place of what you were using.

bayken37 · 01-28-2013, 08:23 PM

After more tweaking, I was wondering how to go about making two "isset"s for one page.

So previously we had it by STAT_ID what if I also wanted to grab those STAT values for whichever TEAM_ID I put in?

/../stats.php?STAT_ID=AVG&Team_ID=ATL

is this something that I need to add to the isset function?

Old Pedant · 01-28-2013, 09:16 PM

Do you know what isset means???

Apparently not. Look here:
http://php.net/manual/en/function.isset.php

isset is NOTHING BUT a simple builtin PHP function that allows you to test to see if there is any value there at all.

You don't even NEED to use it for STAT_ID if you use the code I showed you, because that code will only allow certain values for STAT_ID and reject the rest.

Now... If you want to make the team abbreviation *optional* then, yes, using isset with it is a good idea. If you want to make it required, you can still use it though you would do so differently.

You could alter the code above like this (stuff added is in red, rest stays the same):

Code:

<?
... make the db connection here ...

$team = $_GET["TEAM_ID"];
if ( isset($team) && strlen($team) == 3 ) /* assumes all are 3 letter abbreviations */
{
    $where = " WHERE team_id = '" . mysql_real_escape_string($team) . "' ";
} else
    $where = "";
}

$sql = "SELECT CONCAT(Fname,' ',Lname) AS player, Pos, Year, $fname " 
     . " FROM batters_career_stats $where ORDER BY $sname DESC LIMIT 50";
$result = mysql_query($sql) or die(mysql_error()); 

...