Using "Order by" by a hyperlink

[yoda69]

Hey,

So this time i want to create a page that loads up some database fields into a table.

That works great.

Next, I want to create a hyper link on the table header (one or two fields) that will allow the user to order the table by their results:

 

Here's the code for the original table:

 

  <?PHP

$db = mysql_connect("localhost","root","pass") or die("Problem connecting");

mysql_select_db("testing") or die("Problem selecting database");

$query = "SELECT * FROM teaching";

$result = mysql_query($query) or die ("Query failed");

//let's get the number of rows in our result so we can use it in a for loop

$numofrows = mysql_num_rows($result);

 

echo "<TABLE class=\"maintable\" BORDER=1 frame=box width=100% CELLPADDING=0 CELLSPACING=1 >\n";

echo "<TR bgcolor=\"lightblue\">

<TD>Start Date</TD>

<TD>Title</TD>

<TD>Education</TD>

<TD>Institution</TD>

</TR>\n";

for($i = 0; $i < $numofrows; $i++) {

$row = mysql_fetch_array($result); //get a row from our result set

if($i % 2) { //this means if there is a remainder

echo "<TR bgcolor=\"#CCFFCC\">\n";

} else { //if there isn't a remainder we will do the else

echo "<TR bgcolor=\"#BFD8BC\">\n";

}

echo"

<TD>".$row['start_date']."</TD>

<TD><a href='/showall.php?primary_index={$row[primary_index]}'>".stripslashes($row[title])."</a></TD>

<TD>".$row['education']."</TD>

<TD>".$row['institution']."</TD>

\n";

echo "</TR>\n";

}

//now let's close the table and be done with it

echo "</TABLE>\n";

mysql_free_result($result);

?>

 

If anyone could tip me, how to enter a link into the header in a way that would allow the user to "order by" the table it would be great.

 

Thanks ahead,

Yoda

[king arthur]

Simply add a GET variable to the URL in the link. Use this GEt variable to determine the string with the "order by" clause in it. I usually use a switch statement for this kind of thing.

 

For example, at the top of your code:

<?PHP

$order = $_GET["orderby"];

switch($order)
{
   case "date":
    $order_clause = " order by start_date";
    break;
   case "title":
    $order_clause = " order by title";
    break;
   default:
    $order_clause = "";
    break;
}

$db = mysql_connect("localhost","root","pass") or die("Problem connecting");
mysql_select_db("testing") or die("Problem selecting database");

$query = "SELECT * FROM teaching" . $order_clause;

 

I've assumed two columns in your databse called "start_date" and "title" there, obviously insert the real names if those aren't right. Also I've only done cases for two columns, you may or may not want others.

 

Further down,

echo "<TABLE class=\"maintable\" BORDER=1 frame=box width=100% CELLPADDING=0 CELLSPACING=1 >\n";
echo "<TR bgcolor=\"lightblue\">
<TD>Start Date <a href="thispage.php?orderby=date">(order by)</a></TD>
<TD>Title <a href="thispage.php?orderby=title">(order by)</a></TD>
<TD>Education</TD>
<TD>Institution</TD>
</TR>\n";

 

Replace "thispage.php" in the links with the actual name of your script. And again, I've only put those two in. Try something like that, it's how I do it.

 

[GingerRobot]

Ok, so you'll need to make your headings into links, and pass a couple of variables through. The way ive set it up is that one click on the link would order ASC, a second would order DESC.

 

<?php
$db = mysql_connect("localhost","root","pass") or die("Problem connecting");
mysql_select_db("testing") or die("Problem selecting database");
//set a direction for the search. If none is set, we'll choose ASC
if(!isset($_GET['direction'])){
$direction = 'ASC';
}else{
$direction = $_GET['direction'];
}
//set the newdirection as the opposite to the old
if($direction=="ASC"){
$newdirection= "DESC";
}else{
$newdirection = "ASC";
}


//change the query if we are ordering by something
if(isset($_GET['orderby'])){
$query = "SELECT * FROM teaching ORDER BY '$_GET[orderby]' $direction";	
}else{
$query = "SELECT * FROM teaching";		
}


$result = mysql_query($query) or die ("Query failed");
//let's get the number of rows in our result so we can use it in a for loop
$numofrows = mysql_num_rows($result);

echo "<TABLE class=\"maintable\" BORDER=1 frame=box width=100% CELLPADDING=0 CELLSPACING=1 >\n";
echo "<TR bgcolor=\"lightblue\">
<TD><a href='".$_SERVER["PHP_SELF"]."?orderby=startdate&direction=".$newdirection."'>Start Date</a></TD>
<TD>Title</TD>
<TD>Education</TD>
<TD>Institution</TD>
</TR>\n";
for($i = 0; $i < $numofrows; $i++) {
$row = mysql_fetch_array($result); //get a row from our result set
if($i % 2) { //this means if there is a remainder
echo "<TR bgcolor=\"#CCFFCC\">\n";
} else { //if there isn't a remainder we will do the else
echo "<TR bgcolor=\"#BFD8BC\">\n";
}
echo"
<TD>".$row['start_date']."</TD>
<TD><a href='/showall.php?primary_index={$row[primary_index]}'>".stripslashes($row[title])."[/url]</TD>
<TD>".$row['education']."</TD>
<TD>".$row['institution']."</TD>
\n";
echo "</TR>\n";
}
//now let's close the table and be done with it
echo "</TABLE>\n";
mysql_free_result($result);
?>

 

Ive only done the first one, and this assumes your field name is startdate, if its not, you'll need to modify the link.

 

Edit: i see i was beaten to it, but ill still post this as it might give you some more ideas.

[yoda69]

Thanks for your replies.

I tried using the two methods and eventually decided to go on the second one, it fits more the look of my website.

However, I'm getting "query failed" every time i'm using it. I tried to make sure all the elements match. I don't seem to find the problem. maybe you could help me locate it in the code.

p.s. I don't know if its important but i'm using php 4

 

Here's the code:

 

    <?PHP

 

 

$db = mysql_connect("localhost","root","pass") or die("Problem connecting");

mysql_select_db("education") or die("Problem selecting database");

 

//set a direction for the search. If none is set, we'll choose ASC

if(!isset($_GET['direction'])){

$direction = 'ASC';

}else{

$direction = $_GET['direction'];

}

//set the newdirection as the opposite to the old

if($direction=="ASC"){

$newdirection= "DESC";

}else{

$newdirection = "ASC";

}

if(isset($_GET['orderby'])){

$query = "SELECT * FROM teachings ORDER BY '$_GET[orderby]' $direction";

}else{

$query = "SELECT * FROM teachings";

}

 

$result = mysql_query($query) or die ("Query failed");

//let's get the number of rows in our result so we can use it in a for loop

$numofrows = mysql_num_rows($result);

 

  <p class="style1">Teachings for the <?php echo "$master";?> Discipline

  </p>

    <?PHP

echo "<TABLE class=\"maintable\" BORDER=1 frame=box width=100% CELLPADDING=0 CELLSPACING=1 >\n";

echo "<TR bgcolor=\"lightblue\">

<TD><a href='".$_SERVER["PHP_SELF"]."?orderby=start_date&direction=".$newdirection."'>Start Date</a></TD>

<TD>Title</TD>

<TD>Education</TD>

<TD>Institution</TD>

</TR>\n";

for($i = 0; $i < $numofrows; $i++) {

$row = mysql_fetch_array($result); //get a row from our result set

if($i % 2) { //this means if there is a remainder

echo "<TR bgcolor=\"#CCFFCC\">\n";

} else { //if there isn't a remainder we will do the else

echo "<TR bgcolor=\"#BFD8BC\">\n";

}

echo"

<TD>".$row['start_date']."</TD>

<TD><a href='/showall.php?primary_index={$row[primary_index]}'>".stripslashes($row[title])."[/url]</TD>

<TD>".$row['education']."</TD>

<TD>".$row['institution']."</TD>

\n";

echo "</TR>\n";

}

//now let's close the table and be done with it

echo "</TABLE>\n";

mysql_free_result($result);

?>