order by question

[sirTemplar]

i have a database and i output the records with a default order. like for example LASTNAME ASC. what i would like to do is to add on the output page option where users may choose to order differently like according to FIRSTNAME or DATE OF BIRTH or COUNTRY OF ORIGIN, etc. How should i implement that with a php code? thanks again!

[darkke]

I would try something like this:

$sql = "SELECT * FROM table";

if(isset($_POST['orderby']))    {
    $order = addslashes($_POST['orderby']);
    $sql.= " ORDER BY $order ASC";
}    else    {
    $sql.= " ORDER BY lastname ASC";
}

$result=mysql_query($sql);

In other words, have a radio button, select box etc. called 'orderby' in your form that passes the value to the script.

[sirTemplar]

this is really strange! i tried to use the code you gave to order. i also have on my script the limit to 50 records per page.

if(isset($_POST['orderby']))    { 
    $order = addslashes($_POST['orderby']); 

$query = "SELECT * FROM tableWHERE  

          (CNOMEN LIKE '%$CNOMEN%' 
          AND NOMRL LIKE '%$NOMRL%' 
		  AND PROVRELCD LIKE '%$PROVRELCD%'
		  AND NATDAT1 LIKE '%$NATDAT1%'
		  AND PROFTDAT1 LIKE '%$PROFTDAT1%'
		  AND PROFSDAT1 LIKE '%$PROFSDAT1%'
		  AND SACDAT1 LIKE '%$SACDAT1%'
		  AND CETO_NAME LIKE '%$CETO_NAME%'
		  AND IDNUMF LIKE '%$IDNUMF%'
		  AND SDATFIN1 = ' '
		  AND MORTDAT1 = ' ') 
          ORDER BY $order ASC
		  LIMIT $limitvalue, $limit ";  

}    else    { 
$query = " SELECT * FROM table WHERE  

          (CNOMEN LIKE '%$CNOMEN%' 
          AND NOMRL LIKE '%$NOMRL%' 
		  AND PROVRELCD LIKE '%$PROVRELCD%'
		  AND NATDAT1 LIKE '%$NATDAT1%'
		  AND PROFTDAT1 LIKE '%$PROFTDAT1%'
		  AND PROFSDAT1 LIKE '%$PROFSDAT1%'
		  AND SACDAT1 LIKE '%$SACDAT1%'
		  AND CETO_NAME LIKE '%$CETO_NAME%'
		  AND IDNUMF LIKE '%$IDNUMF%'
		  AND SDATFIN1 = ' '
		  AND MORTDAT1 = ' ') 
          ORDER BY CNOMEN ASC
		  LIMIT $limitvalue, $limit ";  

}    

when i try to search with my search form it automatically outputs the recored as ORDER BY CNOMEN ASC (which is fine). below the output i have a form that one can use in order to change the order of the output, which is this:

<form method="POST" action="<?$PHP_SELF?>">
<font face="Verdana">
</font><input type="radio" name="orderby" value="NATDAT1"><font size="2">Order by Birthday<br>
</font><input type="radio" name="orderby" value="NOMRL"><font size="2">Order by
First Name</font></font></p>
<p><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>

now the problem is this, when i choose for example order by birthday then submit, it will output not only the searched but the entire database!

the strange thing is this! since i have 53 records and i limit each page to 50, the output will give me a second page, 1 showing 1-50 and page 2 showing 51-53. if i click on the second page and the there the option of ordering the accdg. to bday, the script works well! it does it only for the 53! but when i try to go back to page 1 it will give me again the default order which is the surname! any idea! thanks

[kevinsequeira]

could u post the complete code for the pages that you are using.

reg
kevin

[sirTemplar]

this is the complete code:

<?
$db_host  = 'localhost'; 
$db_user  = 'user'; 
$db_pass  = 'password'; 
$db_name  = 'database; 
$db_table = 'table'; 
$conn = mysql_connect($db_host,$db_user,$db_pass); 
mysql_select_db($db_name,$conn);  


if ($CNOMEN == "")  

{$CNOMEN = '%';}  

if ($NOMRL == "")  

{$NOMRL = '%';} 

if ($PROVRELCD == "")  

{$PROVRELCD = '%';}  

if ($NATDAT1 == "")  

{$NATDAT1 = '%';}  

if ($PROFTDAT1 == "")  

{$PROFTDAT1 = '%';}  

if ($PROFSDAT == "")  

{$PROFSDAT = '%';} 

if ($SACDAT1 == "")  

{$SACDAT1 = '%';}  

if ($MORTDAT1 == "")  

{$MORTDAT1 = '%';}  

if ($IDNUMF == "")  

{$IDNUMF = '%';} 

if ($CETO_NAME == "")  

{$CETO_NAME = '%';}

// Change this to fit your database 

if(isSet($search)){ 
    $d_array = explode("|",$search); 
    $CNOMEN = $d_array[0]; 
    $NOMRL = $d_array[1]; 
	$PROVRELCD = $d_array[2]; 
	$NATDAT1 = $d_array[3]; 
	$PROFTDAT1 = $d_array[4]; 
	$PROFSDAT1 = $d_array[5]; 
	$SACDAT1 = $d_array[6]; 
	$CETO_NAME = $d_array[7]; 
	$IDNUMF = $d_array[8]; 
}   

$limit = 50;  

$query_count = "SELECT * FROM table WHERE  

                (CNOMEN LIKE '%$CNOMEN%' 
                 AND NOMRL LIKE '%$NOMRL%' 
				 AND PROVRELCD LIKE '%$PROVRELCD%'
				 AND NATDAT1 LIKE '%$NATDAT1%'
				 AND PROFTDAT1 LIKE '%$PROFTDAT1%'
				 AND PROFSDAT1 LIKE '%$PROFSDAT1%'
				 AND SACDAT1 LIKE '%$SACDAT1%'
				 AND CETO_NAME LIKE '%$CETO_NAME%'
				 AND IDNUMF LIKE '%$IDNUMF%'
				 AND SDATFIN1 = ' '
				 AND MORTDAT1 = ' ')
				 ";   

$query_count2 = "SELECT * FROM table WHERE
				(SDATFIN1 = ' '
				AND MORTDAT1 = ' ')"; 



$search = $CNOMEN."|".$NOMRL."|".$PROVRELCD."|".$NATDAT1."|".$PROFTDAT1."|".$PROFSDAT1."|".$SACDAT1."|".$CETO_NAME."|".$IDNUMF; 
$search_display = $CNOMEN." AND ".$NOMRL." AND ".$PROVRELCD." AND ".$NATDAT1." AND ".$PROFTDAT1." AND ".$PROFSDAT1." AND ".$SACDAT1." AND ".$CETO_NAME." AND ".$IDNUMF; 
$result_count = mysql_query($query_count);  

$totalrows = mysql_num_rows($result_count); 

$result_count2 = mysql_query($query_count2);  

$total2 = mysql_num_rows($result_count2);  

if(empty($page))  

$page = 1;  

$limitvalue = $page * $limit - ($limit);  

if(isset($_POST['orderby']))    { 
    $order = addslashes($_POST['orderby']); 

$query = "SELECT * FROM table WHERE  

          (CNOMEN LIKE '%$CNOMEN%' 
          AND NOMRL LIKE '%$NOMRL%' 
		  AND PROVRELCD LIKE '%$PROVRELCD%'
		  AND NATDAT1 LIKE '%$NATDAT1%'
		  AND PROFTDAT1 LIKE '%$PROFTDAT1%'
		  AND PROFSDAT1 LIKE '%$PROFSDAT1%'
		  AND SACDAT1 LIKE '%$SACDAT1%'
		  AND CETO_NAME LIKE '%$CETO_NAME%'
		  AND IDNUMF LIKE '%$IDNUMF%'
		  AND SDATFIN1 = ' '
		  AND MORTDAT1 = ' ') 
          ORDER BY $order ASC
		  LIMIT $limitvalue, $limit ";  

}    else    { 
$query = " SELECT * FROM table WHERE  

          (CNOMEN LIKE '%$CNOMEN%' 
          AND NOMRL LIKE '%$NOMRL%' 
		  AND PROVRELCD LIKE '%$PROVRELCD%'
		  AND NATDAT1 LIKE '%$NATDAT1%'
		  AND PROFTDAT1 LIKE '%$PROFTDAT1%'
		  AND PROFSDAT1 LIKE '%$PROFSDAT1%'
		  AND SACDAT1 LIKE '%$SACDAT1%'
		  AND CETO_NAME LIKE '%$CETO_NAME%'
		  AND IDNUMF LIKE '%$IDNUMF%'
		  AND SDATFIN1 = ' '
		  AND MORTDAT1 = ' ') 
          ORDER BY CNOMEN ASC
		  LIMIT $limitvalue, $limit ";  

}    

$result = mysql_query($query) or die("Error: " . mysql_error());  

$count_result = mysql_num_rows($result); 

// Display links at the top to indicate current page and number of pages displayed 
$numofpages = ceil($totalrows / $limit); 
echo"<font face=Verdana size=2 color=#000000><br><p><b>$totalrows</b> record(s) out of <b>$total2</b> found!</p></font>"; 
$from=$limit*$page-$limit+1; 
$to=$from + $count_result-1; 

// start 123 next>> table 
echo "<table align=\"center\" width=\"90%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\"> 
            <tr><td width=\"50%\" bgcolor=\"$bg_colour\" align=\"left\">";if($numofpages>1){echo"<font face=Verdana size=2 color=#000000>Showing: $from - $to</td><td width=\"50%\" bgcolor=\"$bg_colour\" align=\"right\"><b><font face=Verdana size=2 color=#000000>Go to page:</b></font> ";}  

// display previous link if page is not 1 
if($page != 1){  

$pageprev = $page - 1;  

echo("<font face=Verdana size=2 color=#000000><a href=\"$PHP_SELF?page=$pageprev&search=$search\"><< PREV</a>&nbsp;</font>");  

}  

// display page nos if not 1 
for($i = 1; $i <= $numofpages; $i++){  

if($numofpages>1){ 
if($i == $page)  

echo("<font face=Verdana size=2 color=#000000>&nbsp;".$i."&nbsp;</font>");  

else 
echo("<font face=Verdana size=2 color=#000000>&nbsp;<a href=\"$PHP_SELF?page=$i&search=$search\">$i</a>&nbsp;</font>");  

}} 

// display next page link if there is more than one 
if(($totalrows - ($limit * $page)) > 0){  

$pagenext = $page + 1;  

echo("<font face=Verdana size=2 color=#000000><a href=\"$PHP_SELF?page=$pagenext&search=$search\">NEXT >></a></font>");  

} 
// end 123 next>> table 
echo"</td></tr></table<br>";

while($row=mysql_fetch_array($result)){ 

---- prints the records here ---------

}

  echo ("<p>");  

  echo ("<p>"); 
}   

?>

</td>
      </tr>
    </table>
    </td>

  </tr>

</table>
<form method="POST" action="<?$PHP_SELF?>">
  <p><font face="Verdana" size="1"><b>
  <input type="radio" name="orderby" value="NATDAT1"></b></font><b><font size="1" face="Verdana">Order 
  by Birthday</font></b><font face="Verdana" size="1"><b><input type="radio" name="orderby" value="NOMRL"></b></font><b><font size="1" face="Verdana">Order 
  by First Name </font></b><font size="1" face="Verdana"><br>
  <input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></font></p>
</form>

[kevinsequeira]

debug: echo the $query to see what it does, each time, this should help you see why it behaves that way, maybe the limit value is getting overwritten or blank

reg
kevin

[kevinsequeira]

or replace ure query part with this

$query = "SELECT * FROM table WHERE (CNOMEN LIKE '%$CNOMEN%' AND NOMRL LIKE '%$NOMRL%' AND PROVRELCD LIKE '%$PROVRELCD%' AND NATDAT1 LIKE '%$NATDAT1%' AND PROFTDAT1 LIKE '%$PROFTDAT1%' AND PROFSDAT1 LIKE '%$PROFSDAT1%' AND SACDAT1 LIKE '%$SACDAT1%' AND CETO_NAME LIKE '%$CETO_NAME%' AND IDNUMF LIKE '%$IDNUMF%' AND SDATFIN1 = ' ' AND MORTDAT1 = ' ')";
if(isset($_POST['orderby']))    {
  $order = addslashes($_POST['orderby']);
  $query .= " ORDER BY ".$order." ASC";
}else{
	$query .=" ORDER BY CNOMEN ASC";
}
$query .=" LIMIT $limitvalue, $limit ";
echo $query;

reg
kevin