Using Hyperlinks to set Order By for columns in a table

collin

Okay, so I have a page that displays a table listing all the books that I have Read. A user can check a checkbox off next to each one. If they submit it the books are added to a different database. Anyways, what I need help with it is allowing the user to order the table by the columns that are shown here (on the same page). I want to just make the column titles hyperlinks at which point they can select a column heading, it'll reload the page sorted by that heading. Reverse order would be nice on second click, but not needed.

I look around for tutorials for this, but couldn't find any or at least not ones that were simple enough for me to understand and apply to my own. Help is greatly appreciated!

My code for the page:

<html>
<body>

<?php

global $user;

$db = mysql_connect("localhost", "collin","MYPASSWORD");

mysql_select_db("mydatabase_name",$db);


//add books to database that are checked
$query="SELECT a.book, a.rarity, a.price, a.sid FROM collector_books a LEFT JOIN collector_books_read b ON a.sid = b.sid and b.uid = $user->uid WHERE b.sid IS NULL";

$result=mysql_query($query);
$num=mysql_numrows($result);

$i=0;
while ($i < $num) {
$sid=mysql_result($result,$i,"sid");

if($_REQUEST[$sid] == "on")
mysql_query("INSERT INTO collector_books_read VALUES ($sid, $user->uid)");

$i++;
}

//display all non-read books for user to select which ones he has read
$query="SELECT a.book, a.rarity, a.price, a.sid FROM collector_books a LEFT JOIN collector_books_read b ON a.sid = b.sid and b.uid = $user->uid WHERE b.sid IS NULL ORDER BY a.book ASC";

$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
echo "<center>Book Collector Checklist</center>
";

echo '<form name="form1" method="post" action="">';
echo "<table align='center' border=1 cellpadding=4 cellspacing=0>";
echo "<tr><td>Book</td><td>Rarity</td><td>Price</td><td>Read</td></tr>";

$i=0;
while ($i < $num) {
$book=mysql_result($result,$i,"book");
$rarity=mysql_result($result,$i,"rarity");
$price=mysql_result($result,$i,"price");
$sid=mysql_result($result,$i,"sid");

$checkbox="<input type='checkbox' name='$sid'>";
echo "<tr><td>$book</td><td>$rarity</td><td>$price</td><td>$checkbox</td></tr>";

$i++;
}
echo "</table>";

echo '<center><input type="submit" name="Submit" value="Mark as Read"></center>';
echo '</form>';

?>

</body>
</html>

notaloser

this may work..

make the hyperlinks link to thispage.php?bookorder=BOOKTYPE, such as thispage.php?bookorder=novel for books that are novels, etc.

then try this


if($bookorder == "novels") {
$bookquery = "ORDER BY your restrictions"; // for novels
}elseif($bookorder == "histfict") {
$bookquery = "ORDER BY your restrictions"; // for hist fiction
}else{
$bookquery = ""; // for anything unrecognized or if the variable is not in the URL and is empty
}

$query="SELECT a.book, a.rarity, a.price, a.sid FROM collector_books a LEFT JOIN collector_books_read b ON a.sid = b.sid and b.uid = $user->uid WHERE b.sid IS NULL ".$bookquery;

that way the resrictions are added to the end if there are any, and empty if the variable is empty

and if you want to do the ordering ascending and descending, you could link to stuff like thispage.php?bookorder=BOOKTYPE&order=A (for ascending) or D (for descending), and customize the script above to fit

just an idea, theyr might be some bugs

collin

That does not work. On my site, if you link like that it will bring me to a different page completely. It needs to be some type of form that submits to itself.

notaloser

why would your server not allowed variables to be stored in the URL?

i dont know much about servers, so someone more skilled may want to inform me a little

collin

notaloser wrote:
why would your server not allowed variables to be stored in the URL?

i dont know much about servers, so someone more skilled may want to inform me a little

Gas nothing to do with my server. All I know is that it goes to a different page if I do it that way. My page by default have q?node/PAGE. so I assuming it thinks it's another place, I dunno. I know there is way to do this with forms I just don't know how.

collin

Anyone else have any ways to do it?

Houdini

If your server runs PHP then it will pass information through the URL and it will go to the page specified everything after the filename and following the ?is just key value pairs they do not affect where the page goes to. Here are two simple scripts (save them both in the same directory) to test how to pass values through the URL.
text.php

<?php 
//Just a test php page for passing a value with a URL
echo "<a href='nextpage.php?book=novel'>Go to nextpage PHP</a>";
?>

nextpage.php

<?php 
echo "You should see novel on the next line<br />";
echo $_GET['book'];
?>

Eric_T

$order = $_GET['Order'];
$order = stripslashes($order);
if($order == "") {
	$StrOrder = "novel";
}Else{
	$StrOrder = $order;
}

put this in your query  Order by $StrOrder

href='http://www.nextpage.php?Order=novel ASC'>A-Z</a> Title <a>
href='http://www.nextpage.php?Order=novel DESC'>Z-A</a>

Thats what I use.. he can see it in use here (My first PHP page)

collin

thanks guys, I actually was just finishing up my own...had found out how a very similar way you guys just said.
Using links like:

<a href='?q=node/23&order_by=book'>Book</a>

Then for determining what to order by:

if($_REQUEST['order_by']==""){
	$order_by="book";
	$_SESSION['old_page']=$order_by;
}elseif($_REQUEST['order_by']=="book" && $_SESSION['old_page']=="book"){
	$order_by="book desc";
	$_SESSION['old_page']="book desc";
}elseif($_REQUEST['order_by']=="book"){
	$order_by="book";
	$_SESSION['old_page']=$order_by;
}elseif($_REQUEST['order_by']=="rarity" && $_SESSION['old_page']=="rarity"){
	$order_by="rarity desc";
	$_SESSION['old_page']="rarity desc";
}elseif($_REQUEST['order_by'] == "rarity"){
	$order_by="rarity";
	$_SESSION['old_page']=$order_by;
}elseif($_REQUEST['order_by']=="price" && $_SESSION['old_page']=="price"){
	$order_by="price desc";
	$_SESSION['old_page']="price desc";
}elseif($_REQUEST['order_by'] == "price"){
	$order_by="price";
	$_SESSION['old_page']=$order_by;
}

Then added

ORDER BY $order_by

to the Select statement.

Thanks again though.

reisrui

Hi there,

I wanted to have the same "ordering" links in my table headers and I tried Eric T´s solution.
I understand it and I think it would work, but my script has another issue... I´m using a submit button that according to what visitors choose in a listbox it shoes the table results. So, what is hapenning now is that after I have the result set shown, I click the new header link and it doesn´t trigger the submit !!! Can I force this or maybe another way of doing it?
Here´s the code:

<?php
// conexão à base de dados sgp *************************************
include ("connect.php");

// passa para uma variável o que foi escolhido nas combos *********
[COLOR=DarkOrange]// this is the variable that gets user´s choice in the list box (it´s in another page)[/COLOR]
$colectividade = $_POST['colectividade'];

[COLOR=DarkOrange]// constrói o query[/COLOR]

if($colectividade!="")
{
if($colectividade=="todas")
{$colectividade="";}
$query = mysql_query("SELECT * FROM T21 WHERE colect like '%$colectividade%'");
}

// Se não carregou sequer no Pesquisar não aparece nada *************
[COLOR=DarkOrange]// this is where I said I was echoing the table contents only if one submits[/COLOR]
if (!isset($_POST['submit'])) {
echo "Please Submit!";
}
// Se pesquisou com o select vazio **********************************
elseif ($_POST['colectividade']=="") {
echo "Não escolheu nenhuma Colectividade!";
}

// Se seleccionou qualquer coisa, mostra os resultados **************
// ------------- CABEÇALHO -----------
else {

// apanha a ordenação
[COLOR=DarkOrange]// this is the code I added for ordering
$order = $_GET['order'];
$order = stripslashes($order);
if($order == "") {
	$StrOrder = "lugar";
	echo "EMPTY";
}Else{
	$StrOrder = $order;
	echo "ORDER FILLED";
	$query = mysql_query("SELECT * FROM T21 WHERE colect like '%$colectividade%' Order by $StrOrder");
}[/COLOR]


echo '<table class="Tabela">';
echo '<tr>';
echo '<td class="TabelaCab">Lugar</td>';
echo '<td class="TabelaCab">Associação</td>';
echo '<td class="TabelaCab[COLOR=DarkOrange]"><a href="prova_intercolect.php?order=num_colect ASC">Nº Colect</a>[/COLOR]</td>';
echo '<td class="TabelaCab">Colectividade</td>';
echo '<td class="TabelaCab">Pontos</td>';
echo "</tr>";
}


// ------------- CONTEUDO -----------
while($row = mysql_fetch_array($query)) {

$num_conc=$row['num_conc'];

print '<tr style="CURSOR: hand" onmouseover="this.style.backgroundColor=\'gray\'" onmouseout="this.style.backgroundColor=\'\'">';

// chama popup com o id de cada linha
//echo '<a href="popupT22.php?id='.$num_conc.'" target="_Blank">';
//echo '<a href="popupT21.php?id='.$num_conc.'" onclick="ShowPopup(this.href,800,600);return false;" target="_Blank">';

echo "<td nowrap>".$row{'lugar'}."</td>";
//echo "<td nowrap>".$row{'zona'}."</td>";
//echo "<td nowrap>".$row{'prova'}."</td>";
echo "<td nowrap>".$row{'associacao'}."</td>";
echo "<td nowrap>".$row{'num_colect'}."</td>";
echo "<td nowrap align='left'>".$row{'colect'}."</td>";
echo "<td nowrap>".$row{'pontos'}."</td>";
//echo "</a>";
echo "</tr>";

}
echo "</table>";