Sort by clicking column headers; links in table data

Managing a wishlist

[Ernos]

Me again. Here's what I'm trying to do this time: Manage a homemade wishlist, similar to Amazon's, only with more options to sort the columns by (Amazon only lets you sort by type, date added, and price).

So I've got a MySQL table with columns for, among other things, title, type, date added, price, rating, comments, and most importantly, URL -- I want to link to each item as it's listed on Amazon. So in the resulting table, I'd like the TD containing the title to essentially be "<A HREF=$url>$title</A>" (I know it wouldn't quite work out like that, I'm just trying to make it clear what I'm going for).

My current code, though, uses foreach and I can't figure how to alter it to make the title link to the url. Here's what I've got (snipped a little for brevity):

<?php

$default_sort = 'title';
/* I only want to sort by the following columns, not all columns: */
$allowed_order = array ('title', 'type', 'date', 'e_rating', 'price');

if (!isset ($order) ¦¦
!in_array ($order, $allowed_order)) {
$order = $default_sort;
} else {
$order = $order;
}

$dbh=mysql_connect ("localhost", "username", "password") or die ("Cannot connect to the database");
mysql_select_db ("db_name", $dbh);

$query="SELECT * FROM wishlist ORDER BY $order";
$result=mysql_query($query,$dbh) or die("MySQL Error: ".mysql_errno().": ".mysql_error());

$numrows = mysql_num_rows($result);
if ($numrows == 0) {
echo "No data to display!";
exit;
}

$row = mysql_fetch_assoc ($result);
echo "<TABLE border=1>\n";
echo "<TR>\n";
foreach ($row as $heading=>$column) {
echo "<TD><b>";
if (in_array ($heading, $allowed_order)) {
echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading\">$heading</a>";
} else {
echo $heading;
}
echo "</b></TD>\n";
}
echo "</TR>\n";

mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
echo "<TR>\n";
foreach ($row as $column) {
echo "<TD>$column</TD>\n";
}
echo "</TR>\n";
}
echo "</TABLE>\n";
?>

This works great (and it worked the first time I tried it! Woohoo!), but of course the column containing the URL is displayed as its own column in the resulting table, and I want it to be a link in the "title" column. Is there a relatively simple way of achieving this? Google, once again, helped me get this far but has dropped me off before I got to my destination. Should I use "for" instead of "foreach" and define each column like:

$title as $heading=>$column;
$price as $heading=>$column;
$type as $heading=>$column;

?

[jatar_k]

not being able to test all of this makes it extremely prone to error but what about something along these lines?

[pre]$row = mysql_fetch_assoc ($result);
echo "<TABLE border=1>\n";
echo "<TR>\n";
foreach ($row as $heading=>$column) {?><?
 [b]if ($heading!= "url") {[/b]
  echo "<TD><b>";
  if (in_array ($heading, $allowed_order)) {
   echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading\">$heading</a>";
  } else {
  echo $heading;
  }
 echo "</b></TD>\n";
 [b]}[/b]
}
echo "</TR>\n";
mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
 echo "<TR>\n";
 foreach ($row as $column) {
 [b]if ($heading!= "url") {
  if ($heading == "title") echo "<TD><A HREF=$url>$title</A></TD>\n";
  else echo "<TD>$column</TD>\n";
  }[/b]
 }
 echo "</TR>\n";
}
echo "</TABLE>\n";[/pre]

[Ernos]

That produced an odd result: In the top row of the table (the one with the headings, some of which are clickable), it just removed the cell containing "url" altogether, so the top row of the table was simply one cell shorter than all the other rows. Weird.

I played around with it a little. I tried taking out that first if ($heading!= "url") { that you suggested, and tried taking out the second one as well. That restored the top row of the table to what it was, but nothing put the links in the "title" column as I'd wanted.

One thing I tried, so see what information was being was passed:

if ($heading == "title") {
echo "<TD><A HREF=$url>$title</A></TD>\n";
}
else {
echo "<TD>$heading.$column</TD>\n";
}

For some reason, this displayed "price." in front of each cell (not the price of the item, but the word "price"). This tells me that $heading isn't the var I'm looking for, maybe? But I also tried to echo $title and $url and nothing comes up (not even an error message).

[jatar_k]

Sorry, my fault. I wasn't 100% sure what you wanted but i thought you didn't want the url col displayed.

The top then worked as I intended. The second part I should have looked a little harder at.

$heading will remain as the last value from the previous loop so really is of no value. I would nuke the foreach inside the while and build it explicitly. Something like so..

assuming the rows are as mentioned in your first post and are ordered the same

<TABLE border="1">
<tr>
<td><a href="<?=$_SERVER['PHP_SELF']?>?order=title">Title</a></td>
<td><a href="<?=$_SERVER['PHP_SELF']?>?order=type">Type</a></td>
<td><a href="<?=$_SERVER['PHP_SELF']?>?order=date">Date Added</a></td>
<td><a href="<?=$_SERVER['PHP_SELF']?>?order=price">Price</a></td>
<td><a href="<?=$_SERVER['PHP_SELF']?>?order=e_rating">Rating</a></td>
<td>Comments</td>
</tr>
<?
while ($row = mysql_fetch_assoc ($result)) {
echo "<TR>\n";
echo "<td><a href=\"" . $row['url'] . "\">" . $row['title'] . "</a></td><td>" . $row['type'] . "</td><td>" . $row['date'] . "</td><td>" . $row['price'] . "</td><td>" . $row['e_rating'] . "</td><td>" . $row['comments'] . "</td>\n";
echo "</TR>\n";
}
echo "</TABLE>\n";
?>

[Ernos]

That's exactly it, thank you! Perfect.

[Ernos]

I'm back! I figured I'd stay in this thread since the latest problem I'm having is in this same script.

I played around with the code for my wishlist so that the columns could be clicked to sort in ascending or descending order, in addition to sorting by Title, Type, Rating, etc. I got it to work, and I was all proud of myself until I realized that something really, really weird is going on. Currently I have 46 rows in the table -- 46 items on the wishlist. However, only 45 items are being displayed in the resulting table for some reason, even though I set up an echo for numrows which still says there are 46 items.

Whenever you click the column headers to sort by title, type, or whatever, the db seems to eat the first item on the list and not display it. For example, when I sort by Title in Ascending order, the first movie on the list, "AAAA Movie", is not displayed. If I click to sort the list by Rating, "AAAA Movie" shows up on the list again, but the first item in the ratings-sorted list disappears. Why on earth would this happen? I went back and tested the old code, from before I started adding Ascending and Descending to the picture, and it was doing the same exact thing, only I hadn't noticed it before.

Well, here's the code, if anyone can tell me why it's doing this I'd sure appreciate it:

<H3>Wish List</H3>
<?php
$default_sort = 'title';
$allowed_order = array 'title', 'type', 'date', 'e_rating', 'price');

if (!isset ($order) ¦¦!in_array ($order, $allowed_order)) {
$order = $default_sort;
} else {
$order = $order;
}

if (!isset ($list)) {
$list = "ASC";
} else {
$list = $list;
}

if ($list == "ASC") {
$list = "DESC";
}
elseif ($list == "DESC") {
$list = "ASC";
}
else {
$list = "ASC";
}

$dbh=mysql_connect ("localhost", "username", "password") or die ("Cannot connect to the database");
mysql_select_db ("db_name", $dbh);
$query="SELECT *, left(e_comments,100) as e_comments, FROM wishlist ORDER BY $order $list";
$result=mysql_query($query,$dbh) or die("MySQL Error: ".mysql_errno().": ".mysql_error());

$numrows = mysql_num_rows($result);
if ($numrows == 0) {
echo "No data to display!";
exit;
}
else {
echo "There are ". $numrows. " items in the database.";
}
$row = mysql_fetch_assoc ($result);
?>

<TABLE border="1" width="100%">
<tr>
<td class="fixed"><a href="<?=$_SERVER['PHP_SELF']?>?order=title&amp;list=<?=$list?>">Title</a></td>
<td><a href="<?=$_SERVER['PHP_SELF']?>?order=type&amp;list=<?=$list?>">Type</a></td>
<td><a href="<?=$_SERVER['PHP_SELF']?>?order=date&amp;list=<?=$list?>">Date Added</a></td>
<td><a href="<?=$_SERVER['PHP_SELF']?>?order=price&amp;list=<?=$list?>">Price</a></td>
<td><a href="<?=$_SERVER['PHP_SELF']?>?order=e_rating&amp;list=<?=$list?>">E-Rating</a></td>
<td>Comments</td>
</tr>

<?php
while ($row = mysql_fetch_assoc ($result)) {
echo "<TR>\n";
echo "<td class=\"fixed\"><a href=\"" . $row['url'] . "\">" . $row['title'] . "</a></td><TD>" . $row['type'] . "</td><td>" . $row['date'] . "</td><td>$" . $row['price'] . "</td><td>" . $row['e_rating'] . "</td><td>" . $row['e_comments'] . "</td>\n";
echo "</TR>\n";
}
echo "</TABLE>\n";
?>

The only thing I can think of is that the top row of the table (the row containing the headers) is somehow overwriting the row containing that first disappearing item? But since I'm still winging it with this stuff, I have no idea how to rectify it.

[Ernos]

Just to sort of pop in and show my progress, I stripped pretty much everything out of the above script to make it a completely basic query, and it's still not displaying the first item in the list.

<?php
$dbh=mysql_connect ("localhost", "username", "password") or die ("Cannot connect to the database");
mysql_select_db ("db_name", $dbh);
$query="SELECT * FROM wishlist ORDER BY title";
$result=mysql_query($query,$dbh) or die("MySQL Error: ".mysql_errno().": ".mysql_error());
$numrows = mysql_num_rows($result);
if ($numrows == 0) {
echo "No data to display!";
exit;
}
else {
echo "There are ". $numrows. " items in the database.";
}
[It still prints that there are 46 items.]
$row = mysql_fetch_assoc ($result);
while ($row = mysql_fetch_assoc ($result)) {
echo "<P>".$row['title']."<BR>";
}
?>

This prints a simple, alphabetical list of the titles on the wishlist, except for the first item, which is still myteriously left off. So instead of displaying
AABBCC Movie
BBCCDD Movie
CCDDEE Movie
DDEEFF Movie,
it's only showing
BBCCDD Movie
CCDDEE Movie
DDEEFF Movie.
This has never happened to me before, I'm really at a loss.