Sorting of data from database by adding hyperlink to the header

Good dAY

I really need to solve my problem in sorting of my data from my database. Like for example the user has a choice to sort the information in asc or desc order. Honestly i have no idea how can I do that. i tried to research but I can’t understand somecodes and when i tried it, it has a bugs.

Here is my code:


<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Untitled Document</title>
<!--<script type="text/JavaScript">
//function confirmDelete(){
	//var agree = confirm("Are you sure you want to delete this file?");
	//if(agree){
		// direct browser to delete.php
	//	window.location = "./delete.php";
	//} else {
		// do nothing and return false
		//return false ;
	//}
//}
//</script>-->
</head>
 <body>
 <form name="machine1" action="page3.php" method="post">
 <table border="1">
 <tr>
 <td>Emp ID</td>
 <td>Last Name</td>
 <td>First Name</td>
 <td>Birthday</td>
 <td>Option</td>
 </tr>
 
<?php 
// Connects to your Database 

  $host = 'localhost';
  $user = 'root';
  $db = "db_machine1";
  
 mysql_connect("$host", "$user") or die(mysql_error()); 

 mysql_select_db("$db") or die(mysql_error()); 
 

if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
} // if
//$emp_id = $_POST['Emp_ID'];
//$query = "SELECT count(*) FROM table WHERE ...";
$query = "SELECT count(*) FROM tbl_machine1";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

$rows_per_page = 5;
$lastpage      = ceil($numrows/$rows_per_page);

$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} // if
if ($pageno < 1) {
   $pageno = 1;
} // if

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

$query = "SELECT * FROM tbl_machine1 $limit";

$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
//... process contents of $result ...

while($info = mysql_fetch_array( $result ))
{
	$emp_id = $info['Emp_ID'];
	$lname = $info['Last_Name'];
	$fname = $info['First_Name'];
	$bday = $info['Birthday'];
	$date = date('d-m-Y', strtotime($bday));
	?>
	<tr>
	<td><?php echo $emp_id;?> </td>
    <td><?php echo $lname;?> </td>
    <td><?php echo $fname;?> </td>
    <td><?php echo $date;?> </td>
   	<td><a href = 'edit.php?id=<?php echo $emp_id; ?>'>Edit</a> <a href='delete.php?id=<?php echo $emp_id; ?>' onClick="return confirm('Are you sure you want to delete?')">Delete</a></td>
	</tr>
    <?php
}
?>
	</table>
     <A HREF="javascript:void(0)" onClick="window.open('add.php','welcome','width=300,height=200')">
<input type="button" name="add" value="ADD"> </A>&nbsp;&nbsp;

<?php
if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> ";
   $prevpage = $pageno-1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> ";
} // if


echo " ( Page $pageno of $lastpage ) ";

if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
} // if
/*mysql_connect("localhost", "root", "") or die(mysql_error()); 
mysql_select_db("db_machine1") or die(mysql_error()); 
 
 $data_p = mysql_query("SELECT * FROM tbl_machine1") or die(mysql_error());
while($info = mysql_fetch_array( $data_p ))
{
	$emp_id = $info['Emp_ID'];
	$lname = $info['Last_Name'];
	$fname = $info['First_Name'];
	$bday = $info['Birthday'];
	?>
	<tr>
	<td><?php echo $emp_id;?> </td>
    <td><?php echo $lname;?> </td>
    <td><?php echo $fname;?> </td>
    <td><?php echo $bday;?> </td>
   	<td><a href = 'edit.php?id=<?php echo $emp_id; ?>'>Edit</a> <a href='delete.php?id=<?php echo $emp_id; ?>' onClick='confirmDelete();'>Delete</a></td>
	</tr>
<?php
}*/
?>



 </body>
 </html>

MY boss wanted that the headers has a hyperlink like for example when he click the Emp ID the Emp ID will be sort desc because at first it is asc order and when he click again the Emp ID it will go back to ASc order…my boss want is Emp_ID, Last name, Firstname, and bday has a hyperlink to sort it

I hope somebody can help me…because I really need this today…

Thank you so much

Easiest way use javascript like this jquery plugin - jQuery plugin: Tablesorter 2.0

If you want to do it in php change the headings to hyperlinks and have them pass a get param telling your code what filed to sort by and what order (ascending or decending). Then simply use that field in your sql’s order by clause

In your giving sample I don’t know where I can put this code:

Start by telling tablesorter to sort your table when the document is loaded:

$(document).ready(function()
{
$(“#myTable”).tablesorter();
}
);

Click on the headers and you’ll see that your table is now sortable! You can also pass in configuration options when you initialize the table. This tells tablesorter to sort on the first and second column in ascending order.

$(document).ready(function()
{
$(“#myTable”).tablesorter( {sortList: [[0,0], [1,0]]} );
}
);

That will go inside your script tag i.e

<script type="text/javascript">
$(document).ready(function()
{
$("#myTable").tablesorter();
}
); 
</script>

I used the code…it goes like this:


<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Untitled Document</title>
<!--<script type="text/JavaScript">
//function confirmDelete(){
	//var agree = confirm("Are you sure you want to delete this file?");
	//if(agree){
		// direct browser to delete.php
	//	window.location = "./delete.php";
	//} else {
		// do nothing and return false
		//return false ;
	//}
//}
//</script>-->


<script type="text/javascript" src="/path/to/jquery-latest.js"></script> 
<script type="text/javascript" src="/path/to/jquery.tablesorter.js"></script> 

<script type="text/javascript">
$(document).ready(function()
{
$("#myTable").tablesorter();
}
); 
</script>

</head>
 <body>
 <form name="machine1" action="page3.php" method="post">
 <table border="1" id="myTable" class="tablesorter">
 <thead>
 <tr>
 <td>Emp ID</td>
 <td>Last Name</td>
 <td>First Name</td>
 <td>Birthday</td>
 <td>Option</td>
 </tr>
 </thead>
 
<?php 
// Connects to your Database 

  $host = 'localhost';
  $user = 'root';
  $db = "db_machine1";
  
 mysql_connect("$host", "$user") or die(mysql_error()); 

 mysql_select_db("$db") or die(mysql_error()); 
 

if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
} // if
//$emp_id = $_POST['Emp_ID'];
//$query = "SELECT count(*) FROM table WHERE ...";
$query = "SELECT count(*) FROM tbl_machine1";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

$rows_per_page = 5;
$lastpage      = ceil($numrows/$rows_per_page);

$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} // if
if ($pageno < 1) {
   $pageno = 1;
} // if

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

$query = "SELECT * FROM tbl_machine1 $limit";

$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
//... process contents of $result ...

while($info = mysql_fetch_array( $result ))
{
	$emp_id = $info['Emp_ID'];
	$lname = $info['Last_Name'];
	$fname = $info['First_Name'];
	$bday = $info['Birthday'];
	$date = date('d-m-Y', strtotime($bday));
	?>
    <tbody>
	<tr>
	<td><?php echo $emp_id;?> </td>
    <td><?php echo $lname;?> </td>
    <td><?php echo $fname;?> </td>
    <td><?php echo $date;?> </td>
   	<td><a href = 'edit.php?id=<?php echo $emp_id; ?>'>Edit</a> <a href='delete.php?id=<?php echo $emp_id; ?>' onClick="return confirm('Are you sure you want to delete?')">Delete</a></td>
	</tr>
    </tbody>
    <?php
}
?>
	</table>
     <A HREF="javascript:void(0)" onClick="window.open('add.php','welcome','width=300,height=200')">
<input type="button" name="add" value="ADD"> </A>&nbsp;&nbsp;

<?php
if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> ";
   $prevpage = $pageno-1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> ";
} // if


echo " ( Page $pageno of $lastpage ) ";

if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
} // if
/*mysql_connect("localhost", "root", "") or die(mysql_error()); 
mysql_select_db("db_machine1") or die(mysql_error()); 
 
 $data_p = mysql_query("SELECT * FROM tbl_machine1") or die(mysql_error());
while($info = mysql_fetch_array( $data_p ))
{
	$emp_id = $info['Emp_ID'];
	$lname = $info['Last_Name'];
	$fname = $info['First_Name'];
	$bday = $info['Birthday'];
	?>
	<tr>
	<td><?php echo $emp_id;?> </td>
    <td><?php echo $lname;?> </td>
    <td><?php echo $fname;?> </td>
    <td><?php echo $bday;?> </td>
   	<td><a href = 'edit.php?id=<?php echo $emp_id; ?>'>Edit</a> <a href='delete.php?id=<?php echo $emp_id; ?>' onClick='confirmDelete();'>Delete</a></td>
	</tr>
<?php
}*/
?>



 </body>
 </html>

then I insert the jquery.tablesorter and jquery-latest in my folder where my php code location. and when i run my code nothing happened

I have code for sorting my problem now is on my paging because when I sort my data for example my data sorted to desc and when I click the next hyperlink the data was sort as asc which is wrong it should be still desc.

here is my code


<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Untitled Document</title>

</head>
 <body>
 <form name="machine1" action="page3.php" method="post">
 
 <?php
  $sort = "ASC";
  $data_sort = "Emp_ID";
  
  if(isset($_GET['sorting']))
	{
		if($_GET['sorting'] == 'ASC'){
			$sort = "DESC";
		}
		else{
			$sort = "ASC";
		}
		}
	if(isset($_GET['field_name'])  == 'Emp_ID'){
		$data_sort = "Emp_ID";
	}
	elseif(isset($_GET['field_name']) == 'Last_Name'){
		$data_sort = "Last_Name";
	}
	elseif(isset($_GET['field_name']) == 'First_Name'){
		$data_sort = "First_Name";
	}
 ?>
 <table border="1">
 <tr>
 <td><a href="page3.php?sorting=<?php echo $sort; ?>&field_name=Emp_ID">Emp ID</a></td>
 <td><a href="page3.php?sorting=<?php echo $sort; ?>&field_name=Last_Name">Last Name</a></td>
 <td><a href="page3.php?sorting=<?php echo $sort; ?>&field_name=First_Name">First Name</a></td>
 <td><a href="page3.php?sorting=<?php echo $sort; ?>&field_name=Birthday">Birthday</a></td>
 <td>Option</td>
 </tr>
 
<?php 

  $host = 'localhost';
  $user = 'root';
  $db = "db_machine1";
  
 mysql_connect("$host", "$user") or die(mysql_error()); 

 mysql_select_db("$db") or die(mysql_error()); 
 


 

if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
} // if
//$emp_id = $_POST['Emp_ID'];
//$query = "SELECT count(*) FROM table WHERE ...";
$query = "SELECT count(*) FROM tbl_machine1";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

$rows_per_page = 5;
$lastpage      = ceil($numrows/$rows_per_page);

$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} // if
if ($pageno < 1) {
   $pageno = 1;
} // if

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

$query = "SELECT * FROM tbl_machine1 ORDER BY $data_sort $sort $limit";

$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
//... process contents of $result ...

while($info = mysql_fetch_array( $result ))
{
	$emp_id = $info['Emp_ID'];
	$lname = $info['Last_Name'];
	$fname = $info['First_Name'];
	$bday = $info['Birthday'];
	$date = date('d-m-Y', strtotime($bday));
	?>
	<tr>
	<td><?php echo $emp_id;?> </td>
    <td><?php echo $lname;?> </td>
    <td><?php echo $fname;?> </td>
    <td><?php echo $date;?> </td>
   	<td><a href = 'edit.php?id=<?php echo $emp_id; ?>'>Edit</a> <a href='delete.php?id=<?php echo $emp_id; ?>' onClick="return confirm('Are you sure you want to delete?')">Delete</a></td>
	</tr>
    <?php
}
?>
	</table>
     <A HREF="javascript:void(0)" onClick="window.open('add.php','welcome','width=300,height=200')">
<input type="button" name="add" value="ADD"> </A>&nbsp;&nbsp;

<?php

 if(isset($_GET['sorting']))
	{
		if($_GET['sorting'] == 'ASC'){
			$sort = "ASC";
		}
		else{
			$sort = "DESC";
		}
	}
			
if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
   //echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> ";
   //echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> ";
 ?>
  <a href="page3.php?pageno=1<?php if(isset($_GET['field_name'])) { ?> $data_sort <?php echo $data_sort; } ?> <?php if(isset($_GET['sorting'])) { ?> $sort=<?php echo $sort; } ?>">FIRST</a>
<?php
   $prevpage = $pageno-1;
   //  echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> ";
?>
 
  <a href="page3.php?pageno=<?php echo $prevpage; ?><?php if(isset($_GET['field_name'])) { ?> $data_sort <?php echo $data_sort; } ?> <?php if(isset($_GET['sorting'])) { ?> $sort=<?php echo $sort; } ?>">PREV</a>
<?php
} // if


echo " ( Page $pageno of $lastpage ) ";

if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;

//   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";
//   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
 

 ?>
  <a href="page3.php?pageno=<?php echo $nextpage; ?><?php if(isset($_GET['field_name'])) { ?> $data_sort <?php echo $data_sort; } ?> <?php if(isset($_GET['sorting'])) { ?> $sort=<?php echo $sort; } ?>">NEXT</a>
   <a href="page3.php?pageno=<?php echo $lastpage; ?><?php if(isset($_GET['field_name'])) { ?> $data_sort <?php echo $data_sort; } ?> <?php if(isset($_GET['sorting'])) { ?> $sort=<?php echo $sort; } ?>">LAST</a>
 <?php
}

// if
/*mysql_connect("localhost", "root", "") or die(mysql_error()); 
mysql_select_db("db_machine1") or die(mysql_error()); 
 
 $data_p = mysql_query("SELECT * FROM tbl_machine1") or die(mysql_error());
while($info = mysql_fetch_array( $data_p ))
{
	$emp_id = $info['Emp_ID'];
	$lname = $info['Last_Name'];
	$fname = $info['First_Name'];
	$bday = $info['Birthday'];
	?>
	<tr>
	<td><?php echo $emp_id;?> </td>
    <td><?php echo $lname;?> </td>
    <td><?php echo $fname;?> </td>
    <td><?php echo $bday;?> </td>
   	<td><a href = 'edit.php?id=<?php echo $emp_id; ?>'>Edit</a> <a href='delete.php?id=<?php echo $emp_id; ?>' onClick='confirmDelete();'>Delete</a></td>
	</tr>
<?php
}*/
?>



 </body>
 </html>

Not that you got the hang of it, how about using a switch statement instead of that mess of chained ifs.

It’s faster (but you won’t notice it)!

Or an array. :slight_smile:


<?php
$columns = array(
  'emp'   => 'Emp_ID',
  'fname' => 'First_Name',
  'lname' => 'Last_Name',
);

$sql = 'SELECT foo FROM table';

if(array_key_exists($_GET['field_name'], $columns)){
  $sql .= ' ORDER BY ' . $columns[ $_GET['field_name'] ];
}