Trying to sort by headers....

[ccutla]

I am trying to be able to sort a table by the different column headings. The table is the result of a search of a mysql db and I have no idea how or where to start. Any suggestions???

Thanks a lot!

This is the results page of my search where I need the sort to happen:

php:


<center>
<table border="1" cellpadding="5" cellspacing="0" bordercolor="#000000">
<tr>
<td width="60"><b>DT_STRING</b></td>
<td width="100"><b>ACCOUNT</b></td>
<td width="30"><b>ACCOUNT_TYPE</b></td>
<td width="150"><b>CLIENT_ID</b></td>
<td width="150"><b>USER_ID</b></td>
</tr>
<tr>
<td>
<? $hostname = "mysql"; // The Thinkhost DB server.
$username = ""; // The username you created for this database.
$password = ""; // The password you created for the username.
$usertable = "AUDIT"; // The name of the table you made.
$dbName = "AUDITMED"; // This is the name of the database you made.

MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
@mysql_select_db( "$dbName") or die( "Unable to select database");


//error message (not found message)begins
$XX = "No Record Found, to search again please close this window";

//query details table begins

$metode = $_POST['metode'];
$search = $_POST['search'];
$metode2 = $_POST['metode2'];
$search2 = $_POST['search2'];

$query = mysql_query("SELECT * FROM AUDIT WHERE $metode LIKE '%$search%' AND $metode2 LIKE '%$search2%'");

while ($row = @mysql_fetch_array($query))
{
$variable1=$row["DT_STRING"];
$variable2=$row["ACCOUNT"];
$variable3=$row["ACCOUNT_TYPE"];
$variable4=$row["CLIENT_ID"];
$variable5=$row["USER_ID"];
//table layout for results

print ("<tr>");
print ("<td>$variable1</td>");
print ("<td>$variable2</td>");
print ("<td>$variable3</td>");
print ("<td>$variable4</td>");
print ("<td>$variable5</td>");
print ("</tr>");
}
//below this is the function for no record!!
if (!$variable1)
{
print ("$XX");
}
//end
?>
</table>
</center>


Thanks again!

[wildteen88]

Change this:
[code]<td width="60"><b>DT_STRING</b></td>
<td width="100"><b>ACCOUNT</b></td>
<td width="30"><b>ACCOUNT_TYPE</b></td>
<td width="150"><b>CLIENT_ID</b></td>
<td width="150"><b>USER_ID</b></td>[/code]to the following:
[code]<td width="60"><b><a href="?sortby=DT_STRING">DT_STRING</a></b></td>
<td width="100"><b><a href="?sortby=ACCOUNT">ACCOUNT</a></b></td>
<td width="30"><b><a href="?sortby=ACCOUNT_TYPE">ACCOUNT_TYPE</a></b></td>
<td width="150"><b><a href="?sortby=CLIENT_ID">CLIENT_ID</a></b></td>
<td width="150"><b><a href="?sortby=USER_ID">USER_ID</a></b></td>[/code]
Now change the following code:
[code]$query = mysql_query("SELECT * FROM AUDIT WHERE $metode LIKE '%$search%' AND $metode2 LIKE '%$search2%'"); [/code]to this:
[code]//Create an array that sotes the possible values $_GET['sortby'] can hold
$sortvalues = array("DT_STRING", "ACCOUNT", "ACCOUNT_TYPE", "CLIENT_ID", "USER_ID");

//Check that $_GET['sortby'] is set and that the value it holds is in the $sortvalues array
if(isset($_GET['sortby']) && in_array($_GET['sortby'], $sortvalues))
{
    //set up the sortby variable that'll be used in the ORDER BY clause in the MySQL Query
    $sortby = $_GET['sortby'];
}
else
{
    // This is the defualt value if $_GET['sortby'] is not set or the value it
    /// contains isn't in the $sortvalue array.
    $sortby = "DT_STRING";
}

$query = mysql_query("SELECT * FROM AUDIT WHERE $metode LIKE '%$search%' AND $metode2 LIKE '%$search2%' ORDER BY $sortby ASC");[/code]

Hoep that helps.

[ccutla]

Thanks for the reply! I put in the new code and it does try to sort when I click on the headers, but it does not return any results it shows up as the no records found error. The original search still works, but not the sort. I don't know if you have any idea of why that is, I am trying to see if the values for the $sortby are all correct. Any further suggestions would be appreciated greatly!

Thanks again!

[wildteen88]

Chnage this:[code]while ($row = @mysql_fetch_array($query))
{
$variable1=$row["DT_STRING"];
$variable2=$row["ACCOUNT"];
$variable3=$row["ACCOUNT_TYPE"];
$variable4=$row["CLIENT_ID"];
$variable5=$row["USER_ID"];
//table layout for results

print ("<tr>");
print ("<td>$variable1</td>");
print ("<td>$variable2</td>");
print ("<td>$variable3</td>");
print ("<td>$variable4</td>");
print ("<td>$variable5</td>");
print ("</tr>");
}
//below this is the function for no record!!
if (!$variable1)
{
print ("$XX");
}[/code]
to:
[code]// This is the proper way of checking whether your
// MySQL query returned any results or not
if(mysql_num_rows($query) >= 1)
{
    while ($row = mysql_fetch_array($query))
    {
        $variable1=$row["DT_STRING"];
        $variable2=$row["ACCOUNT"];
        $variable3=$row["ACCOUNT_TYPE"];
        $variable4=$row["CLIENT_ID"];
        $variable5=$row["USER_ID"];
        //table layout for results

        print ("<tr>");
        print ("<td>$variable1</td>");
        print ("<td>$variable2</td>");
        print ("<td>$variable3</td>");
        print ("<td>$variable4</td>");
        print ("<td>$variable5</td>");
        print ("</tr>");
    }
}
else
{
    "Your search criterea returned no results";
}[/code]
Also change your mysql query part of the code to this:
[code]$query = mysql_query("SELECT * FROM AUDIT WHERE $metode LIKE '%$search%' AND $metode2 LIKE '%$search2%' ORDER BY $sortby ASC") or die("Unable to perform query: "  . mysql_error());[/code]

[ccutla]

I did that but now when I click on a table heading it gives me this error:

"Unable to perform query: 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 'LIKE '%%' AND LIKE '%%' ORDER BY DT_STRING ASC' at line 1"

I don't know exactly what that means but if anybody does I could really use the help.

Thanks again!

[wildteen88]

Try:
[code]$query = mysql_query("SELECT * FROM AUDIT WHERE $metode LIKE '%{$search}%' AND $metode2 LIKE '%{$search2}%' ORDER BY '$sortby' ASC") or die("Unable to perform query: "  . mysql_error());[/code]

I've never used the LIKE clause, so I'm going in blind sorry. With all the examples I've seen with the LIKE Clause they never use the ORDER BY clause prehaps you cant use it with LIKE.

[ccutla]

Thanks for trying, I am still getting the exact same message when I do that so I don't know what the deal is. If you have any other suggestions, I am willing to try anything at this point. Thanks again!

[gavinandresen]

[!--quoteo(post=362390:date=Apr 6 2006, 05:42 PM:name=BoofBoof)--][div class=\'quotetop\']QUOTE(BoofBoof @ Apr 6 2006, 05:42 PM) [snapback]362390[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Thanks for trying, I am still getting the exact same message when I do that so I don't know what the deal is. If you have any other suggestions, I am willing to try anything at this point. Thanks again!
[/quote]

You need to pass search and search2 (and any other variables from $_POST that matter) through in the 're-sort-me' column headers links.

So... you'd change $_POST['search'] to $_REQUEST['search'], and make the column headers:
[code]
<a href="?search=$search&search2=$search2&sortBy=BLAH">BLAH</a>
[/code]
That will pass in search and search2 via GET (which is why you'd need to switch to _REQUEST-- you'll get them either from _POST or _GET).

You might think about changing the form that shows the page to use GET rather than POST, too-- in general, use GET whenever sending the form info doesn't change any data (e.g. it just displays lists of data), and use POST when it does (e.g. adding/editing something).

[ccutla]

Well, I have tried about everything and I still get an error message saying:
"Unable to perform query: 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 'LIKE '%$search%' AND LIKE '%$search2%' ORDER BY 'DT_STRING' ASC' at line 1"
It doesn't make sense to me, because I changed it so that it would search through including search and search2 in the column headers. Anybody else have any ideas? Here is what my script looks like at this point:

php:


<center>
<table border="1" cellpadding="5" cellspacing="0" bordercolor="#000000">
<tr>
<td width="60"><b><a href="?search=$search&search2=$search2&sortby=DT_STRING">DT_STRING</a></b></td>
<td width="100"><b><a href="?search=$search&search2=$search2&sortby=ACCOUNT">ACCOUNT</a></b></td>
<td width="30"><b><a href="?search=$search&search2=$search2&sortby=ACCOUNT_TYPE">ACCOUNT_TYPE</a></b></td>
<td width="150"><b><a href="?search=$search&search2=$search2&sortby=CLIENT_ID">CLIENT_ID</a></b></td>
<td width="150"><b><a href="?search=$search&search2=$search2&sortby=USER_ID">USER_ID</a></b></td>
</tr>
<tr>
<td>
<? $hostname = "mysql"; // The Thinkhost DB server.
$username = ""; // The username you created for this database.
$password = ""; // The password you created for the username.
$usertable = "AUDIT"; // The name of the table you made.
$dbName = "AUDITMED"; // This is the name of the database you made.

MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
@mysql_select_db( "$dbName") or die( "Unable to select database");


//error message (not found message)begins
$XX = "No Record Found, to search again please close this window";

//query details table begins

$metode = $_REQUEST['metode'];
$search = $_REQUEST['search'];
$metode2 = $_REQUEST['metode2'];
$search2 = $_REQUEST['search2'];




$sortvalues = array("DT_STRING", "ACCOUNT", "ACCOUNT_TYPE", "CLIENT_ID", "USER_ID");


if(isset($_GET['sortby']) && in_array($_GET['sortby'], $sortvalues))
{

$sortby = $_GET['sortby'];
}
else
{

$sortby = "DT_STRING";
}


$query = mysql_query("SELECT * FROM AUDIT WHERE $metode LIKE '%{$search}%' AND $metode2 LIKE '%{$search2}%' ORDER BY '$sortby' ASC")
or die("Unable to perform query: " . mysql_error());



if(mysql_num_rows($query) >= 1)
{
while ($row = mysql_fetch_array($query))
{
$variable1=$row["DT_STRING"];
$variable2=$row["ACCOUNT"];
$variable3=$row["ACCOUNT_TYPE"];
$variable4=$row["CLIENT_ID"];
$variable5=$row["USER_ID"];
//table layout for results

print ("<tr>");
print ("<td>$variable1</td>");
print ("<td>$variable2</td>");
print ("<td>$variable3</td>");
print ("<td>$variable4</td>");
print ("<td>$variable5</td>");
print ("</tr>");
}
}
else
{
"Your search criterea returned no results";
}
//end
?>
</table>
</center>





Thanks again for any ideas!

[blulagoon]

[!--quoteo(post=362528:date=Apr 7 2006, 03:47 PM:name=BoofBoof)--][div class=\'quotetop\']QUOTE(BoofBoof @ Apr 7 2006, 03:47 PM) [snapback]362528[/snapback][/div][div class=\'quotemain\'][!--quotec--]

"Unable to perform query: 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 'LIKE '%$search%' AND LIKE '%$search2%' ORDER BY 'DT_STRING' ASC' at line 1"
It doesn't make sense to me, because I changed it so that it would search through including search and search2 in the column headers.
[/quote]

I still think that this error message means that your SQL query is putting $search and $search2 into the query string, rather than the values of $search and $search2.

Blu