Variable inside of a sql_query

[meckr]

here is my code:

[code]$id = "id";
$ip = "offender";
$date = "tstamp";

$sort_order = "$date";

//our SQL query
$sql_query = "SELECT id, offender, tstamp FROM banned_ips ORDER BY" . $sort_order . "ASC";

//store the SQL query in the result variable
$result = mysql_query($sql_query);[/code]

My question is I want to be able to allow the order by to be changed from within a variable.  How can I set the oreder by to be a variable which can be changed without have to change the actual code?

thanks

[Barand]

Make sure $sort_order contains a valid column name and put a space after " ... ORDER BY " and before " ASC"

[wildteen88]

You'll want to have three links which will change the order by id, offender or tstamp. Here is the PHP code you'll want to use:
[code=php:0]//we now get get the sort parameter from the url
switch(@$_GET['sort']))
{
    // This checks whether sort is either one of the following
    // id, offender or tstamp
    // if it is it'll set sort_order to sort URL parameter
    case 'id':
    case 'offender':
    case 'tstamp':
        $sort_order = $_GET['sort'];
    break;

    // if sort was not one of the above we use a defualt value, id
    default:
        $sort_order = "id";
    break;
}[/code]

This replaces [code=php:0]$sort_order = "$date";[/code]


Now the three links:
[code]<a href="?sort=id">Sort by ID</a><br />
<a href="?sort=offender">Sort by offender</a><br />
<a href="?sort=tstamp">Sort by Timestamp</a>[/code]

These links sets the sort URL parameter

[.josh]

i'm not quite sure i get what you're saying. so i'm gonna make up a scenario and you can tell me if this is what you mean:

let's say you have a list of information:
[code]
name  id  date
ken    1  2004
george  2  1999
molly  3  2006
[/code]
and you want to be able to, for instance, click on the name column and it resorts the list based on the name? Or go back and click on the date column and it resorts the info based on the date? if this is what you are looking for, then here's what you can do:

make the column names a link that passes a variable by an id number or the name in the link like so:
[code]
echo "<a href='{$_SERVER['PHP_SELF']}'?sortby=name'>name</a>";
[/code]

then you would check for the variable and base your query on the variable, something like this:

[code]
<?php
if(!isset($_GET['sortby'])){
    $sortby = "name";
} else {
  $sortby = $_GET['sortby'];
}

$sql = "SELECT * FROM tablename ORDER BY $sortby ASC";
mysql_query($sql);
?>
[/code]

[meckr]

@ Wildteen88

Both methods worked.  However, I prefer your method wildteen88.  One more question though.  How can I get it to show the total number of rows?

[code]//we now get get the sort parameter from the url
switch(@$_GET['sort'])
{
    // This checks whether sort is either one of the following
    // id, offender or tstamp
    // if it is it'll set sort_order to sort URL parameter
    case 'id':
    case 'offender':
    case 'tstamp':
        $sort_order = $_GET['sort'];
    break;

    // if sort was not one of the above we use a defualt value, id
    default:
        $sort_order = "id";
    break;
}

//our SQL query
$sql_query = "SELECT id, offender, tstamp FROM banned_ips ORDER BY $sort_order ASC";

//store the SQL query in the result variable
$result = mysql_query($sql_query);

$sql = "SELECT count(*) FROM banned_ips";
$res = mysql_query($sql);
$row = mysql_fetch_row($res);



echo ("<div style=\"text-align: left;\"><b><h2>Total Bans: $row</h2></b></div>
<table class=\"border\" cellspacing=\"4\" cellpadding=\"4\" width=\"500\">
<tr align=\"left\">
    <th><a href=\"?sort=id\">Sort by ID</a> </th>
    <th><a href=\"?sort=offender\">Sort by IP ADDRESS</a> </th>
    <th><a href=\"?sort=tstamp\">Sort by DATE OF BAN</a></th>
</tr>");

  if(mysql_num_rows($result))
  {
    //output as long as there are still available fields
    while($row = mysql_fetch_row($result))
    {

echo ("<tr align=\"left\">
        <td><b>ID:</b> $row[0] </td>
      <td><b>IP:</b> $row[1] </td>
      <td><b>Date:</b>" . date('Y-m-d H:i:s',$row[2]) . "<br></td>
  </tr>");
  }
}


//if no fields exist
else
{
  echo "There are currently no bans at this time!";
}
echo ("</table>");[/code]

This gives me "Total Bans: ARRAY" as the result instead of "Total Bans: 127" as the result.

Can you tell me what I'm doing wrong?

[meckr]

*bump*

[wildteen88]

Change this:
[code]$sql = "SELECT count(*) FROM banned_ips";
$res = mysql_query($sql);
$row = mysql_fetch_row($res);



echo ("<div style=\"text-align: left;\"><b><h2>Total Bans: $row</h2></b></div>[/code]
to:
[code]$numBans = mysql_num_rows($result);

echo ("<div style=\"text-align: left;\"><b><h2>Total Bans: $numBans</h2></b></div>[/code]