Jump to content

Variable inside of a sql_query


meckr

Recommended Posts

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

@ 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?
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.