displaying categories in php from a MySQL table

Anon

Hey-up,

I have a list of web links in a big MySQL table and each is categorised into things like 'food', 'toys', 'books', etc...

At the moment my php table displays these links and lists them according to these categories:

$results = mysql_query("SELECT * FROM shopping ORDER by category");

What I'd like to be able to do is split the table up so it's easier to read... is there anyway of saying, "please write a header for the category type, then list all the relevant links, then have a blank row to break the table up, then repeat for the next category"?

or if anyone has an better ideas on how to do this?...

thanks very much! - dunstan

p.s. how good is this stuff, it's SO useful! :o)

Anon

maybe a loop. first something like

select a.cat_id
from categories a

then, for each cat_id, $x:

select a.interesting_col1, a.interesting_col2
from shopping a
where a.cat_id=$x
order by a.interesting_col1

print...

loop for all cat_id.

Anon

Hi Arthur,

Thanks for the help but I'm not that hot on this stuff yet, so I don't fully get how to write what you've suggested...

If my current dynamic table code looks like this, how do I alter it to accomodate what you've suggested?:

<?
$count=0;
while($row = mysql_fetch_array($results)) {
// Handle BGColor
if ($count % 2 == 0) $bgcolor ='#CECFFF';
else $bgcolor = '#9999FF';
$count++;
echo "<TR bgcolor='$bgcolor'><TD><A HREF=\"".$row["url"]."\" target='blank'>".$row["name"]."</A></TD><TD>".$row["descript"]."</TD><TD>".$row["category"]."</TD></TR>";
}
echo "</table>";
?>

thanks in advance for any other help you can give me,

cheers - dunstan

Anon

//assuming you have a reference table
//called CATEGORIES for your categories
$sql1 = "select a.cat_id from categories a";
//... execute SQL1 & cache results in $catresults
// then, for each cat, execute a 2nd query against SHOPPING table
$sql2 = "select a.col1,a.col2 from shopping a where a.cat_id=%s order by a.col1";
while ($catrow = mysql_fetch_array($catresults)) {
//repeat query for each category
$sql = sprintf($sql2,$catrow["cat_id"]);
//...execute SQL2 & cache results in $shopresults
// print out $shopresults
print "<tr ...><td>".$shopresults["col1"]."</td>...</tr>"
}

Anon

Oh.
Why do I need to do all of that?
I don't have a reference table, and I don't get why I need one?

why can't you just say "print all the rows that have the same entry in the 'category' column, then print a blank row, then loop for the next set of 'category' column entries?...

I'm not very well versed in php and mysql, but logically that seems right to me?

:o/

Anon

So your 'category' column is a readable text name for the
category, not some cryptic ID? In that case, your
query is perfectly fine, you just need to remember
what category you've been working on, i.e.

<b>$prev_category = '';</b>
$results = mysql_query("SELECT * FROM shopping ORDER by category");
if (! $results)
  die('query error');

echo "<table ...>";
while (list($category, $data1, ..., $datan) = mysql_fetch_row($results))
  {
  <b>if ($category != $prev_category)</b>
    {
    if ($prev_category != '') // no blank row first time!
     echo "<tr><td colspan=nn> </td></tr>";
    echo "<tr><td colspan=nn>$category</td></tr>";
    $prev_category = $category;
    }
// now create the row containing the detail...

Anon

that's pretty good. my guess is that Kirk's way is best since it only hits database once.

Anon

Hi again,

I'm ever so sorry, but alan and I still can't work out how all that fits together with the current code - we must be a bit useless :o/

Could someone be ever so kind and edit my code so it includes the scripting Kirk suggested?

Thankyou - dunstan

Code starts here:

<?
$dbuser = 'root';
$dbhost = 'localhost';
$dbpass = 'darkstar';
$dbname = 'links';
$dbtable = 'shopping';
//------ DATABASE CONNECTION --------//
$mysql_link = mysql_connect($dbhost,$dbuser,$dbpass);
$column = mysql_list_fields($dbname,$dbtable,$mysql_link);
$sql = "SELECT id, name, descript, url, category FROM $dbtable ORDER BY category";
$result = mysql_db_query($dbname,$sql);
?>

<?
$results = mysql_query("SELECT * FROM shopping ORDER by category");
?>

<table>
<tr>
<th>Site name</th>
<th>Description</th>
<th>Category</th>
</tr>

<?
while($row = mysql_fetch_array($results)) {

echo "<TR><TD><A HREF=\"".$row["url"]."\" target='blank'>".$row["name"]."</A></TD><TD>".$row["descript"]."</TD><TD>".$row["category"]."</TD></TR>";
}
echo "</table>";
?>

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

Anon

Sorry to be a pain guys, but I've got the thing partly working (it doesn't give an error any more, but just displays a list of IDs now) but am desperate to sort the whole thing out... Is there anything wrong with what I've done:

<table>

<?
$prev_category = '';
$results = mysql_query("SELECT * FROM shopping ORDER by category");
if (! $results)
{
die('query error');
}
echo "<table border=1>";
while (list($category, ###WHAT ELSE GOES IN HERE?###) = mysql_fetch_row($results))
{
if ($category != $prev_category)
{
echo "<tr><td colspan=3></td></tr>";
echo "<tr><td colspan=3>$category</td></tr>";
$prev_category = $category;
}
echo "<TR><TD><A HREF=\"".$row["url"]."\" target='blank'>".$row["name"]."</A></TD><TD>".$row["descript"]."</TD><TD>".$row["category"]."</TD></TR>";
}
echo "</table>";
?>

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