php variable in MYSQL query

[Peuplarchie]

Good day to you all,

                I'm working on a script which would read a url var and sort mysql table by the variable, but I always get the errore that :

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in DB_API/sort_column.php on line 13

 

Can somebody help me fix my problem. I think it's the variable that is not use right in the MYSQL query (

$result = mysql_query("SELECT * FROM NHL_GBG_PLAYERS ORDER BY {$col}");

)

 

Here is my code :

 

<?php
$con = mysql_connect("localhost","sports","sports");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("sports", $con);

$col = $_POST['coll'];
$result = mysql_query("SELECT * FROM players ORDER BY {$col}");

while($row = mysql_fetch_array($result))
  {

  
  echo "<div style=\"width:1090px; align-left:auto; margin-right:auto;\">";
  echo "<div style=\"float:left; width:30px; border:1px solid #000000; \">" . $row['id'] . "</div>";
  echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['date'] . "</div>";
  echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['heure'] . "</div>";
  echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['lieu'] . "</div>";
  echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['assistance'] . "</div>";
  echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['adversaire'] . "</div>";
  echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['equipe'] . "</div>";
  echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['nom'] . "</div>";
  echo "<div style=\"float:left; width:20px; border:1px solid #cccccc;\">" . $row['b'] . "</div>";  
  echo "<div style=\"float:left; width:20px; border:1px solid #cccccc;\">" . $row['p'] . "</div>";
  echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['pts'] . "</div>";
  echo "<div style=\"float:left; width:20px; border:1px solid #cccccc;\">" . $row['l'] . "</div>";
  echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['mdp'] . "</div>";
  echo "<div style=\"float:left; width:30px; border:1px solid #cccccc;\">" . $row['bg'] . "</div>";
  echo "<div style=\"float:left; width:30px; border:1px solid #cccccc;\">" . $row['be'] . "</div>";
  echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['bea'] . "</div>";
  echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['bed'] . "</div>";
  echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['PM'] . "</div>";
  echo "</div><br>";  
  
  
  
  
  }

mysql_close($con);
?> 

 

 

Thanks!

[andrewgauger]

When you pass text to mysql it varies how it treats your text.  In this example, you wouldn't want the {} around your variable name, because to MySQL you are going to want to reference a column name.  I think what you want is backticks such are found on the ~ key :

$result = mysql_query("SELECT * FROM NHL_GBG_PLAYERS ORDER BY `$col`");

 

Another possibility is that the $_POST is not what you were expecting.  How are you validating that what is sent is a column name?  Is it a drop down box?  If so is it sending an id or is it actually sending the column name?

[mrMarcus]

changed a couple things up.  run this code:

 

<?php
$sql = "SELECT * FROM players ORDER BY {$col}";
echo '<pre>'. $sql .'</pre>'; //will echo query so you can see if it's what you expect it to be;

if ($result = @mysql_query($sql))
{
if (mysql_num_rows($result) > 0)
{
	while($row = mysql_fetch_array($result))
	{
		echo "<div style=\"width:1090px; align-left:auto; margin-right:auto;\">";
		echo "<div style=\"float:left; width:30px; border:1px solid #000000; \">" . $row['id'] . "</div>";
		echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['date'] . "</div>";
		echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['heure'] . "</div>";
		echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['lieu'] . "</div>";
		echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['assistance'] . "</div>";
		echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['adversaire'] . "</div>";
		echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['equipe'] . "</div>";
		echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['nom'] . "</div>";
		echo "<div style=\"float:left; width:20px; border:1px solid #cccccc;\">" . $row['b'] . "</div>";  
		echo "<div style=\"float:left; width:20px; border:1px solid #cccccc;\">" . $row['p'] . "</div>";
		echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['pts'] . "</div>";
		echo "<div style=\"float:left; width:20px; border:1px solid #cccccc;\">" . $row['l'] . "</div>";
		echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['mdp'] . "</div>";
		echo "<div style=\"float:left; width:30px; border:1px solid #cccccc;\">" . $row['bg'] . "</div>";
		echo "<div style=\"float:left; width:30px; border:1px solid #cccccc;\">" . $row['be'] . "</div>";
		echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['bea'] . "</div>";
		echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['bed'] . "</div>";
		echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['PM'] . "</div>";
		echo "</div><br>";
	}
}
else
{
	echo 'No records found in db.';
}
}
else
{
trigger_error(mysql_error()); //will display any mysql errors;
}
?>

 

@andrewgauger - curly braces are perfectly fine in the fashion they were being used, and backticks, while good practice, are not necessary and only become necessary when a column name is a mysql reserved word.

[vividona]

$result = mysql_query("SELECT * FROM players ORDER BY {$col}") or die(mysql_error());

 

this will specify your error

[Peuplarchie]

how would I also variably give the order asc or desc ?

 

mysql_select_db("peuplarc_sports", $con);
$ord = $_GET['ord'];
if(empty($_GET['coll']))
{
   die("No column received");
}

$col = mysql_real_escape_string(trim($_GET['coll']));
$query = "SELECT * FROM NHL_GBG_PLAYERS ORDER BY $ord `$col`";
$result = mysql_query($query);
if($result == false)
{
   user_error(mysql_error() . "<br />\n$query");
   die("SQL error");
} 
  

[mrMarcus]

k, you gotta scrub your incoming variables ($ord), 'cause you're wide open to sql injection otherwise.

 

instead of killing script:

 

if(empty($_GET['coll']))
{
   die("No column received");
}

 

when there is no column selected by user, just have it default to a preset one.  less user frustration.

 

and, what do $ord and $col represent in the query?  it goes, ORDER BY `column_name` then you can set ASC or DESC

 

and to stay on absolute safe-side, use triple comparison operator === here: if($result === false)