PDA

View Full Version : Sort headings via hyperlinks

smarlar
07-20-2006, 09:01 PM
I am new to PHP and have tried several options on sorting data via the headings without luck. I have pasted the original code, which works wonderful displaying the data now, I want to sort each column by click on the heading name thus changing the "ODER BY TL" to each type. Have tried a variable "ODER BY $col" with out luck. Can someone tell me what I am doing wrong?


<?php
include ('logo_header.php');
require_once('settings.inc');

echo "<a class=titler>Trailers Available</a>&nbsp;as of ".date("F d, Y");
?>
<BR><BR>
<table border=0 width=800 align=center bgcolor=#000000 cellpadding=0 cellspacing=0><tr ><td>
<table border=0 width=100% align=center cellpadding=3 cellspacing=1><tr bgcolor=cdcdcd align=center >
<td><b>Ref. No.</b> </td>
<td><b> Date </b> </td>
<td><b> Year </b></a> </td>
<td><b> Type </b></td>
<td><b> Quantity </b></td>
<td><b> Length </b></td>
<td><b> Suspension </b></td>
<td><b> Price </b></td>
<td><b> Location </b></td>
<td><b> Status </b></td>
<td width=100><b> Picture </b></td></tr>

<?php
$link = mysql_connect($hostname, $login, $password);
if (!$link) {
die('Could not connect: ' . mysql_error());
}

mysql_select_db($dbname);

$wh=" ";
if (isset($_GET['TL']) AND $_GET['TL']<>"NA") $wh=$wh." AND TL='".$_GET['TL']."' ";
if (isset($_GET['TLY']) AND $_GET['TLY']<>"") $wh=$wh." AND year='".$_GET['TLY']."' ";
if (isset($_GET['TLL']) AND $_GET['TLL']<>"NA") $wh=$wh." AND TrailerLength='".$_GET['TLL']."' ";
if (isset($_GET['susp']) AND $_GET['susp']<>"") $wh=$wh." AND TrailerSuspension='".$_GET['susp']."' ";
//echo $wh;
$result = mysql_query("SELECT * from trailertype WHERE SELL_STATUS='0' ".$wh.' AND status="Sell" ORDER BY TL') or die("Query failed : " . mysql_error());

while ($row = mysql_fetch_assoc($result))
{
// <td width=70>".strftime("%m-%d-%Y",strtotime("$row[Date]"))."</td>
echo "<tr bgcolor=white>
<td width=50>TL$row[ID]</td>
<td width=70>".$row['aYear']." ".date("F",mktime(0,0,0,$row['aMonth'],1,2005))."</td>
<td width=50> $row[Year] </td>
<td> ";
if ($row['TL']=='other') echo $row['TLO']; else echo $row['TL'];
echo "</td>
<td width=50> $row[Quantity] </td>
<td width=50>";
if ($row['TrailerLengthOther']<>"" ) echo $row['TrailerLengthOther']."'";
else {
echo $row['TrailerLength']."'";
}

echo "</td>
<td width=70>".ucfirst($row['TrailerSuspension'])."</td>
<td>$".number_format(($row['SellingPrice']+800),',')."</td>
<td>".$states["$row[Location]"]."</td>
<td>".$status["$row[SELL_STATUS]"]."</td>
<td> <img src=$row[imgdata] height=100px width=100px ></td>
</tr>";
}
?>

The script checks for all trailers that are a status of '0' and Staus= "Sell" this part works fine!
Fumigator
07-21-2006, 01:37 AM
I can't find a <form> tag in the code you pasted. How are you getting back to PHP after a user clicks on a column heading?

p.s. use the PHP code tags when posting code here for readability.
newmand2
07-21-2006, 10:22 AM
Incidentally you can use <th> instead of <td><b> here. <th> means 'table header' and thus you can easily style it using CSS.

Surely you need a link with something like trailers.php?TL=yes which you click on the text in the <td><b> bit. That is where you get the $_GET variables... Right?
smarlar
07-21-2006, 02:56 PM
Sorry I posted the working copy and not what I have changed. the link wiht year has been my only attempt thus far. This might help clairify what I am trying to do.

Thanks


<?php
include ('../logo_header.php');
require_once('../settings.inc');

echo "<a class=titler>Trailers Available</a>&nbsp;as of ".date("F d, Y");

?>
<BR><BR>
<table border=0 width=800 align=center bgcolor=#000000 cellpadding=0 cellspacing=0><tr ><td>
<table border=0 width=100% align=center cellpadding=3 cellspacing=1><tr bgcolor=cdcdcd align=center >
<td><b>Ref. No.</b> </td>
<td><b> Date </b> </td>
td><a herf="trailersA.php?col=year"><b> Year </b></a></td>
<td><b> Type </b></td>
<td><b> Quantity </b></td>
<td><b> Length </b></td>
<td><b> Suspension </b></td>
<td><b> Price </b></td>
<td><b> Location </b></td>
<td><b> Status </b></td>
<td width=100><b> Picture </b></td></tr>

<?php
$link = mysql_connect($hostname, $login, $password);
if (!$link) {
die('Could not connect: ' . mysql_error());
}

mysql_select_db($dbname);

$wh=" ";
if (isset($_GET['TL']) AND $_GET['TL']<>"NA") $wh=$wh." AND TL='".$_GET['TL']."' ";
if (isset($_GET['TLY']) AND $_GET['TLY']<>"") $wh=$wh." AND year='".$_GET['TLY']."' ";
if (isset($_GET['TLL']) AND $_GET['TLL']<>"NA") $wh=$wh." AND TrailerLength='".$_GET['TLL']."' ";
if (isset($_GET['susp']) AND $_GET['susp']<>"") $wh=$wh." AND TrailerSuspension='".$_GET['susp']."' ";
//echo $wh;
$col=TL;
$result = mysql_query("SELECT * from trailertype WHERE SELL_STATUS='0' ".$wh.' AND status="Sell" ORDER BY $col') or die("Query failed : " . mysql_error());

while ($row = mysql_fetch_assoc($result))
{
// <td width=70>".strftime("%m-%d-%Y",strtotime("$row[Date]"))."</td>
echo "<tr bgcolor=white>
<td width=50>TL$row[ID]</td>
<td width=70>".$row['aYear']." ".date("F",mktime(0,0,0,$row['aMonth'],1,2005))."</td>
<td width=50> $row[Year] </td>
<td> ";
if ($row['TL']=='other') echo $row['TLO']; else echo $row['TL'];
echo "</td>
<td width=50> $row[Quantity] </td>
<td width=50>";
if ($row['TrailerLengthOther']<>"" ) echo $row['TrailerLengthOther']."'";
else {
echo $row['TrailerLength']."'";
}

echo "</td>
<td width=70>".ucfirst($row['TrailerSuspension'])."</td>
<td>$".number_format(($row['SellingPrice']+800),',')."</td>
<td>".$states["$row[Location]"]."</td>
<td>".$status["$row[SELL_STATUS]"]."</td>
<td> <img src=$row[imgdata] height=100px width=100px ></td>
</tr>";
}
?>


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


<center><br>
<form name=UpdateAccess action="../trailersA.php" method=post>
<input type=button name="Update" value="Print" class=TxtBut onClick="window.print()">&nbsp;&nbsp;&nbsp;
<input type=submit name="Update" value="Home" class=TxtBut>
</form>
<br></center>

<?php include ('../logo_footer.php');?>
arnyinc
07-21-2006, 03:09 PM
Change:

$col=TL;

To:

$col=$_GET['col'];

That should then sort by year.
smarlar
07-21-2006, 03:30 PM
and If I wanted it to default to TL and allow them to click on a header to sort then I would add this?

$col=($_GET['col'] AND $_GET['col']<>"TL")

What you sent allow the code to execute but it does not display any information therefore I need to default the $col to some variable to see data then I can echo $col to see if it is populating correctly. I don't think my syntax is correct thought. I'm thinking there should be an if there?
arnyinc
07-21-2006, 08:18 PM
if ($_GET['col']=="") //could probably be: if !(isset($_GET['col']))
$col="TL";
else
$col=$_GET['col'];


Basically you check to see if the GET variable named "col" has a value.

It's a pleasure replying to someone who has the right ideas and an understanding of the issues. You just need a small code example to translate your ideas into code.
smarlar
07-21-2006, 10:43 PM
I still missing something and can't put my finger on it. I do understand the if (isset($_GET['col'])) concept, but still is not getting the variable to show below. I have remarked out the echo comment as it returns nothing yet! By all acounts this should work fine. It the simple things that just drive me batty!

Nothing displays yet resort unless I replace the $col' with TL', then it works fine but won't resort, also I don't think I have my hyperlink correct yet either.

if (isset($_GET['col']))$col="TL";
else $col=$_GET['col'];

//echo $col;

$result = mysql_query("SELECT * from trailertype WHERE SELL_STATUS='0' ".$wh.' AND status="Sell" ORDER BY $col') or die("Query failed : " . mysql_error());

The over all code I am working on is a smiple click the header thus resorting the data ORDER BY. It still must have the default to ORDER BY TL'

<?php
include ('../logo_header.php');
require_once('../settings.inc');

echo "<a class=titler>Trailers Available</a>&nbsp;as of ".date("F d, Y");

?>
<BR><BR>
<table border=0 width=800 align=center bgcolor=#000000 cellpadding=0 cellspacing=0><tr ><td>
<table border=0 width=100% align=center cellpadding=3 cellspacing=1><tr bgcolor=cdcdcd align=center >
<td><b>Ref. No.</b> </td>
<td><b> Date </b> </td>
<td><a href="trailersA.php?$col='Year'"><b> Year </b></a></td>
<td><b> Type </b></td>
<td><b> Quantity </b></td>
<td><b> Length </b></td>
<td><b> Suspension </b></td>
<td><b> Price </b></td>
<td><b> Location </b></td>
<td><b> Status </b></td>
<td width=100><b> Picture </b></td></tr>

<?php
$link = mysql_connect($hostname, $login, $password);
if (!$link) {
die('Could not connect: ' . mysql_error());
}

mysql_select_db($dbname);

$wh=" ";
if (isset($_GET['TL']) AND $_GET['TL']<>"NA") $wh=$wh." AND TL='".$_GET['TL']."' ";
if (isset($_GET['TLY']) AND $_GET['TLY']<>"") $wh=$wh." AND year='".$_GET['TLY']."' ";
if (isset($_GET['TLL']) AND $_GET['TLL']<>"NA") $wh=$wh." AND TrailerLength='".$_GET['TLL']."' ";
if (isset($_GET['susp']) AND $_GET['susp']<>"") $wh=$wh." AND TrailerSuspension='".$_GET['susp']."' ";
//echo $wh;

if (isset($_GET['col']))$col="TL";
else $col=$_GET['col'];

//echo $col;

$result = mysql_query("SELECT * from trailertype WHERE SELL_STATUS='0' ".$wh.' AND status="Sell" ORDER BY $col') or die("Query failed : " . mysql_error());

while ($row = mysql_fetch_assoc($result))
{
// <td width=70>".strftime("%m-%d-%Y",strtotime("$row[Date]"))."</td>
echo "<tr bgcolor=white>
<td width=50>TL$row[ID]</td>
<td width=70>".$row['aYear']." ".date("F",mktime(0,0,0,$row['aMonth'],1,2005))."</td>
<td width=50> $row[Year] </td>
<td> ";
if ($row['TL']=='other') echo $row['TLO']; else echo $row['TL'];
echo "</td>
<td width=50> $row[Quantity] </td>
<td width=50>";
if ($row['TrailerLengthOther']<>"" ) echo $row['TrailerLengthOther']."'";
else {
echo $row['TrailerLength']."'";
}

echo "</td>
<td width=70>".ucfirst($row['TrailerSuspension'])."</td>
<td>$".number_format(($row['SellingPrice']+800),',')."</td>
<td>".$states["$row[Location]"]."</td>
<td>".$status["$row[SELL_STATUS]"]."</td>
<td> <img src=$row[imgdata] height=100px width=100px ></td>
</tr>";
}
?>
Fumigator
07-22-2006, 12:08 AM
Your code may work, but your string manipulation isn't clear at all. But I'm pretty sure your query doesn't have the right types of quotes in the right places. You're using double quotes on status = "sell", but double quotes is what is enclosing the entire query.

Try breaking the query string out and echoing it to see what's actually being queried:

$query = sprintf("
SELECT *
FROM trailertype
WHERE SELL_STATUS='0'
%s
AND status='Sell'
ORDER BY '%s'",
$wh, $col);

echo $query;

$result = mysql_query($query) or die("Query failed : " . mysql_error());
smarlar
07-24-2006, 08:55 PM
It’s pure Genius at work… Now explain to me how I set different rows to sort, as the example below is not correct. It never changes the $col variable.

<td><b>Ref. No.</b> </td>
<td><b> Date </b> </td>
<td><a href="trailersA.php?$col='Year'"><b> Year </b></a></td>
<td><b> Type </b></td>
<td><b> Quantity </b></td>
<td><b> Length </b></td>
<td><b> Suspension </b></td>
<td><b> Price </b></td>
<td><b> Location </b></td>
<td><b> Status </b></td>
<td width=100><b> Picture </b></td></tr>
Fumigator
07-25-2006, 03:15 AM
No dollar sign, no quotes around Year.

<a href="trailersA.php?col=Year">


This will put the value "Year" in the $_GET['col'] field when the page reloads.
smarlar
07-25-2006, 03:10 PM
That works, thank you again, but for some reason it is calling four records, sorting them then calling four more and sorting them.

Year is the third field that I am testing.

Output

TL84 2006 September 2006 BellyDump 10 28' Spring $10,800 Texas Available
TL85 2006 September 2006 BellyDump 10 NA' $10,800 Texas Available
TL86 2006 September 2006 BellyDump 10 45' Spring $10,800 Texas Available
TL87 2006 September 2006 BellyDump 10 NA' $10,800 Texas Available
TL56 2006 January 1998 DryVan 12 28' Spring $8,550 Texas Available
TL57 2006 February 1996 DryVan 10 53' Spring $6,850 Texas Available
TL58 2006 February 2001 DryVan 20 53' Air $14,300 Multiple States Available
TL59 2006 February 2003 DryVan 75 53' Air $16,500 Multiple States Available
TL60 2006 February 2000 DryVan 25 53' Air $13,200 Multiple States Available

The code that calls the pages sorts the entire table fine, Am I crasy?

if (isset($_GET['col']))$col="TL";
else $col=$_GET['col'];

echo "col";

$query = sprintf("SELECT * from trailertype WHERE SELL_STATUS='0' %s AND status='Sell' ORDER BY '%s'", $wh, $col);
echo $query;
$result= mysql_query($query) or die("Query failed : " . mysql_error());

while ($row = mysql_fetch_assoc($result))
{
Fumigator
07-26-2006, 12:49 AM
It's sorting by TL, because that's what you're telling it to do every time no matter what column you click.

if (isset($_GET['col']))$col="TL";
else $col=$_GET['col'];
smarlar
07-26-2006, 12:27 PM
Thanks, I will play with that today. I will start with this, as the default needs to be TL unless someone clicks on a column.

if (isset($_GET['col']));
else $col="TL";
smarlar
07-29-2006, 02:38 PM
My last post worked and now it all functional. Thanks to everyone who helped me. Fumigator your the bomb! (I do have other issues in another post if you are up to it.) http://www.codingforums.com/showthread.php?t=91603

if (isset($_GET['col']));
else $col="TL";