Undefined SQL variable

[blinks]

When I run the MySQL queries in the code below in SQLyog, the $pokemon_pos in the "SELECT max(pokemon_pos)" part of the statement is always correct (i.e. alias "x" is correct). Yet when wrapped up in PHP, $newpos, which relies on "x" is always undefined.

 

I'd greatly appreciate any help in troubleshooting this code -

 

$getdeclined="SELECT *
      FROM trade_offers
      WHERE pokemon_trade_id=$_GET[id]";
$declined=mysql_query($getdeclined);
while ($row = mysql_fetch_array($declined)){
$getdecusername="SELECT MAX(pokemon_pos)
                                     FROM (
                                      SELECT pokemon_pos
                                      FROM pokemon_trainer
                                      WHERE pokemon_trainer = '$row[pokemon_trainer]'
                                      UNION
                                      SELECT pokemon_pos 
                                      FROM trade_offers
                                              WHERE pokemon_trainer = '$row[pokemon_trainer]' AND pokemon_trade_id = $_GET[id]
                                             ) AS x";
$decusername=mysql_query($getdecusername);
while($row2 = mysql_fetch_array($decusername)){
          $newpos = $row2['x'];}
          echo $newpos;
          $newpos +=1;
         }
}

[spiderwell]

try $_GET['id'] not $_GET[id] ?

 

not good practice to put raw data into an sql statement, pass the value to a local variable first and validate its what you expect it to be.

 

echoing out the sql statement will show you what is being passed to the database, handy when things arent working

[Maq]

As stated above, this is a security issue.  You should be escaping any user input that goes anywhere near the DB.  You should change your code to look something like:

$id = mysql_real_escape_string($_GET['id']);
$getdeclined="SELECT *
      FROM trade_offers
      WHERE pokemon_trade_id=$id";

 

Change this line to and tell us the exact error message:

$decusername=mysql_query($getdecusername) or die(mysql_error());

[blinks]

Thanks spiderwell and maq. The error displaying is _

 

Notice: Undefined index: x in /home/pokemond/public_html/trade.php on line 170

 

[DavidAM]

When I run the MySQL queries in the code below in SQLyog, the $pokemon_pos in the "SELECT max(pokemon_pos)" part of the statement is always correct (i.e. alias "x" is correct). Yet when wrapped up in PHP, $newpos, which relies on "x" is always undefined.

 

Look carefully at your query. You have aliased the PSEUDO TABLE as "x" NOT the MAX(pokemon_pos) value.

 

SELECT MAX(pokemon_pos)
FROM ( ... ) AS x

 

probably needs to be:

 

SELECT MAX(pokemon_pos) AS x
FROM ( ... ) AS PT

 

(I put PT as the table alias because, I believe, mySql requires all pseudo-tables to have an alias).