php variable in MYSQL query

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