Link to home
Create AccountLog in
Avatar of sbsupport
sbsupportFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Need to search ASC/DESC MySQL Fields

Below is a quick preview of my table and I need to create hyperlinks on the Fields Headers...
ID, Start, Complete etc... to sort the fields either ASC or DESC

=====================================================
[________] [Search]

ID   |   Start    |   Complete   |   Name   |   Subject   |   Enquiry   |   Assigned
----------------------------------------------------------------------------------------------
1     |01-01-01|   Yes            |    Jason  |    E-Mail     |    How do..|    JD
2     |02-01-01|   Yes            |    Adam  |    Printer    |    Is this... |    JD
...
=====================================================

The PHP script is attached.

Thanks
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="table.css" type="text/css">
</head>
<body background="/Themes/default/images/main_bg.gif" style="margin: 0 0 0 0;">
<?
 
$search = $_GET['search'];
 
@$start = $_GET["start"];
if($start =='')
    $start =0;
include("lib.php");
$link = mysql_connect($host,$username,$password);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db($db, $link);
if (!$db_selected) {
    die ("Can't use $db : " . mysql_error());
}
//total number of records in the table
 
$res = mysql_query("SELECT helpdesk_data.id as 'ID', helpdesk_data.start as 'Start', helpdesk_data.complete as 'Complete', if(CHAR_LENGTH(helpdesk_data.name)>15, concat(left(helpdesk_data.name,15),'...'), helpdesk_data.name) as 'Name', if(CHAR_LENGTH(helpdesk_data.subject)>20, concat(left(helpdesk_data.subject,20),'...'), helpdesk_data.subject) as 'Subject', if(CHAR_LENGTH(helpdesk_data.enquiry)>50, concat(left(helpdesk_data.enquiry,50),'...'), helpdesk_data.enquiry) as 'Enquiry', helpdesk_data.assigned_to as 'Assigned' FROM `$table`");
@$rows = mysql_num_rows ($res);
 
$result = mysql_query("SELECT helpdesk_data.id as 'ID', helpdesk_data.start as 'Start', helpdesk_data.complete as 'Complete', if(CHAR_LENGTH(helpdesk_data.name)>15, concat(left(helpdesk_data.name,15),'...'), helpdesk_data.name) as 'Name', if(CHAR_LENGTH(helpdesk_data.subject)>20, concat(left(helpdesk_data.subject,20),'...'), helpdesk_data.subject) as 'Subject', if(CHAR_LENGTH(helpdesk_data.enquiry)>50, concat(left(helpdesk_data.enquiry,50),'...'), helpdesk_data.enquiry) as 'Enquiry', helpdesk_data.assigned_to as 'Assigned' FROM `$table` WHERE name LIKE \"%$search%\" OR (subject LIKE \"%$search%\" or  enquiry LIKE \"%$search%\") ORDER BY id DESC LIMIT $start,25");
 
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
echo "<form><input name='search' type='text' id='search' value='$search'><input type='submit' value='search'>";
$cols = mysql_num_fields($result);
$records = mysql_num_rows ($result);
 echo "<table bgcolor='FFFFFF' align='left' width='100%' cellspacing='1' cellpadding='0' border='0' >";
 echo "<tr bgcolor='BBCCDD' class='menu'>";
 for ($i = 0; $i < $cols;$i++)
 {
    echo "<td align='left'>".mysql_field_name($result,$i)."</td>";
 }
 echo "</tr>";
 while ($row = mysql_fetch_array($result, MYSQL_NUM))
 {
    echo "<tr bgcolor='F6F6F6' class='normal'>";
   foreach ($row as $value)
   { echo "<td align='left'>".$value ."</td>";
   } 
   echo "</tr>";
 }
 $end = $start + $records;
 echo "<tr  align = 'center' bgcolor = 'BBCCDD' class='menu'><td colspan=$cols>";
     if($start != 0)
    {
    $prev = $start - 10;
    echo "<a  href='table_display.php?search=$search&start=$prev'>Previous</a>";
    }
 echo " Records $start  to  $end  of $rows ";
      if($start<$rows-10)
     {
       $next = $start + 10;
       echo "<a href = 'table_display.php?search=$search&start=$next'>Next</a>";
    }
echo " </td></tr></table>";
?>
</body>
</html>

Open in new window

SOLUTION
Avatar of Beverley Portlock
Beverley Portlock
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of sbsupport

ASKER

Thank you very much for the replies !!

Last code post from waygood got
Parse error: syntax error, unexpected '{' in /web/htdocs/support/data/1.php on line 24

I have modded using some of your help but I can only sort ASC...
I'm looking for:
if it is already sorted ASC then sort DESC or if already sorted DESC then sort ASC...

My code attached.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="table.css" type="text/css">
</head>
<body background="/Themes/default/images/main_bg.gif" style="margin: 0 0 0 0;">
<?
 
$search = $_GET['search'];
 
@$start = $_GET["start"];
if($start =='')
    $start =0;
include("lib.php");
$link = mysql_connect($host,$username,$password);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db($db, $link);
if (!$db_selected) {
    die ("Can't use $db : " . mysql_error());
}
//total number of records in the table
 
$res = mysql_query("SELECT helpdesk_data.id as 'ID', helpdesk_data.start as 'Start', helpdesk_data.complete as 'Complete', if(CHAR_LENGTH(helpdesk_data.name)>15, concat(left(helpdesk_data.name,15),'...'), helpdesk_data.name) as 'Name', if(CHAR_LENGTH(helpdesk_data.subject)>20, concat(left(helpdesk_data.subject,20),'...'), helpdesk_data.subject) as 'Subject', if(CHAR_LENGTH(helpdesk_data.enquiry)>50, concat(left(helpdesk_data.enquiry,50),'...'), helpdesk_data.enquiry) as 'Enquiry', helpdesk_data.assigned_to as 'Assigned' FROM `$table`");
@$rows = mysql_num_rows ($res);
 
$sort = $_GET['sort'];
$order = $_GET['order'];
 
if($order ==''){
$result = mysql_query("SELECT helpdesk_data.id as 'ID', helpdesk_data.start as 'Start', helpdesk_data.complete as 'Complete', if(CHAR_LENGTH(helpdesk_data.name)>15, concat(left(helpdesk_data.name,15),'...'), helpdesk_data.name) as 'Name', if(CHAR_LENGTH(helpdesk_data.subject)>20, concat(left(helpdesk_data.subject,20),'...'), helpdesk_data.subject) as 'Subject', if(CHAR_LENGTH(helpdesk_data.enquiry)>50, concat(left(helpdesk_data.enquiry,50),'...'), helpdesk_data.enquiry) as 'Enquiry', helpdesk_data.assigned_to as 'Assigned' FROM `$table` WHERE name LIKE \"%$search%\" OR (subject LIKE \"%$search%\" or  enquiry LIKE \"%$search%\") ORDER BY complete ASC, id DESC LIMIT $start,25");}
else{
$result = mysql_query("SELECT helpdesk_data.id as 'ID', helpdesk_data.start as 'Start', helpdesk_data.complete as 'Complete', if(CHAR_LENGTH(helpdesk_data.name)>15, concat(left(helpdesk_data.name,15),'...'), helpdesk_data.name) as 'Name', if(CHAR_LENGTH(helpdesk_data.subject)>20, concat(left(helpdesk_data.subject,20),'...'), helpdesk_data.subject) as 'Subject', if(CHAR_LENGTH(helpdesk_data.enquiry)>50, concat(left(helpdesk_data.enquiry,50),'...'), helpdesk_data.enquiry) as 'Enquiry', helpdesk_data.assigned_to as 'Assigned' FROM `$table` WHERE name LIKE \"%$search%\" OR (subject LIKE \"%$search%\" or  enquiry LIKE \"%$search%\") ORDER BY $sort $order LIMIT $start,25");}
 
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
echo "<form>
<input type='hidden' name='sort' value='complete'>
<input type='hidden' name='order' value='ASC'>
<input name='search' type='text' value='$search'><input type='submit' value='search'>
";
$cols = mysql_num_fields($result);
$records = mysql_num_rows ($result);
 echo "<table bgcolor='FFFFFF' align='left' width='100%' cellspacing='1' cellpadding='0' border='0' >";
 echo "<tr bgcolor='BBCCDD' class='menu'>";
 
 for ($i = 0; $i < $cols;$i++)
 {
    echo "<td align='left'><a href='?sort=".mysql_field_name($result,$i)."&order=ASC'>".mysql_field_name($result,$i)."</a></td>";
 }
 echo "</tr>";
 while ($row = mysql_fetch_array($result, MYSQL_NUM))
 {
    echo "<tr bgcolor='F6F6F6' class='normal'>";
 
   foreach ($row as $value)
   { echo "<td align='left'>".$value ."</td>";
   } 
   echo "</tr>";
 }
 $end = $start + $records;
 echo "<tr  align = 'center' bgcolor = 'BBCCDD' class='menu'><td colspan=$cols>";
 
     if($start != 0)
    {
    $prev = $start - 10;
    echo "<a  href='table_display.php?search=$search&sort=$sort&order=$order&start=$prev'>Previous</a>";
    }
 
 echo " Records $start  to  $end  of $rows ";
 
 
      if($start<$rows-10)
     {
       $next = $start + 10;
       echo "<a href = 'table_display.php?search=$search&sort=$sort&order=$order&start=$next'>Next</a>";
    }
 
 
echo " </td></tr></table></form>";
?>
</body>
</html>

Open in new window

Okay fixed myself :)
Just a quick 'if' sorted me out.
<?php
if($order =='ASC'){
 $vorder = 'DESC';
}
else{
 $vorder = 'ASC';
}
 
etc...
 
<a href='?sort=".mysql_field_name($result,$i)."&order=$vorder'>".mysql_field_name($result,$i)."</a>
?>

Open in new window