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!

NogDog

I might try something like this, both to help debug and to sanitize the external input:

// connect/select DB stuff, then...
if(empty($_POST['coll']))
{
   die("No column received");
}
$col = mysql_real_escape_string(trim($_POST['coll']));
$query = "SELECT * FROM `players` ORDER BY `$col`";
$result = mysql_query($query);
if($result == false)
{
   user_error(mysql_error() . "<br />\n$query");
   die("SQL error");
}
// output results...

bradgrafelman

When decoding SQL errors, it's often helpful to echo out the query itself. For this reason, I usually build the query string in a variable ($sql or $query, for example) so that you can use that variable both in mysql_query() as well as outputting it when an error occurs.

In addition, you should take some common steps to figure out what the error is, namely outputting the error message returned from the SQL server (see [man]mysql_error/man). Apprently, the 'col' element you're POST'ing to the script isn't a column (or else it is, but the column name contains illegal characters/words and must be delimited with backticks).

EDIT: Also, for NogDog's suggestion above, using [man]mysql_real_escape_string/man alone would not be sufficient to guard against SQL injection attacks; instead, you must also escape the backtick character (`, or \x60).

Peuplarchie

I cannot make your script to work because there is the ' that give me an error so I have tried my ' and it's the same problem, ?

bradgrafelman

Can you show us what you tried? Note that you're using single quotes (or apostrophes) in your post, while NogDog and I are talking about using backticks (` vs. ') - two different characters.

Peuplarchie

This is what I have tried:

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

bradgrafelman

Okay, so.. did it work? If not, what was the error message?

Peuplarchie

Notice: Unknown column '' in 'order clause'
SELECT * FROM NHL_GBG_PLAYERS ORDER BY `` in /DB_API/sort_column.php on line 19
SQL error

bradgrafelman

Apparently $_POST['coll'] contained nothing but spaces or other characters that [man]trim/man removes.

Peuplarchie

right, stupid error...$_GET it was supposed to be.

Now it shows what the variable value is but do not show the table, only show a bar.


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

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

  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>";

bradgrafelman

What does the raw HTML output look like?

Peuplarchie

I got it working now, but
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");
}

bradgrafelman

Check if $GET['ord'] is empty (or invalid) and, if so, set a default value. Something like:

$ord = (isset($_GET['ord']) && ($_GET['ord'] == 'ASC' || $_GET['ord'] == 'DESC') ? $_GET['ord'] : 'DESC'); // sets 'desc' as default

Then, you could output a link that does the opposite of what is selected, e.g.:

$link = ($ord == 'ASC' ? 'DESC' : 'ASC');

EDIT: Might also want to refer to this post by dagon in a thread dealing with this very same topic.

Peuplarchie

ok, I didn't understand your second part about the link.
Also for me it give an error :

Notice: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC id' at line 1
SELECT * FROM NHL_GBG_PLAYERS ORDER BY DESC id in /DB_API/sort_column.php on line 20
SQL error

Here is my code now :


mysql_select_db("sports", $con);
$ord = (isset($_GET['ord']) && ($_GET['ord'] == 'ASC' || $_GET['ord'] == 'DESC') ? $_GET['ord'] : 'DESC'); // sets 'desc' as default 
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");
}

bradgrafelman

Peuplarchie wrote:
ok, I didn't understand your second part about the link.

Perhaps we weren't talking about the same issue/functionality. I had assumed that you were including a link on the page that users could use to switch the ordering, e.g. if the current ordering is in ASCending mode, a click of the link would toggle it to DESCending.

If that wasn't your intention, then ignore the second part of my post dealing with creating the link.

Peuplarchie wrote:
Also for me it give an error :

Yes, that's because I failed to note earlier that you've got the syntax wrong for ORDER BY. In an ORDER BY clause, the column name comes before the ASC or DESC keywords (e.g. "ORDER BY col1 ASC").

Peuplarchie

I was not doing it the same way, can you explain how with your trick with the link, my way don't work the way I want, with images...

Tell me how with the link, I went on you link, but I don't understand...

bradgrafelman

Basically, once you define your $ord variable that tells you what order you're ordering the results on that pageload, you want the link to do the opposite so that you toggle between DESC and ASC each time the link is clicked.

Thus, you'd use something like what I posted previously:

$link = ($ord == 'ASC' ? 'DESC' : 'ASC');

That will basically set $link equal to the opposite of $ord.

Peuplarchie

so my onclick would be :

onclick=\"load('sort_column.php?coll=id&ord=$link = ($ord == 'ASC' ? 'DESC' : 'ASC');

bradgrafelman

No, the code I posted was for a variable declaration. You'd declare the $link variable and then use it in your links. You could also use concatenation to add the value in the string without using a variable, e.g.:

 echo "blah blah " . ($ord == 'ASC' ? 'DESC' : 'ASC') . " blah blah";