Re: Displaying groups from SELECT
| From: | Becoming Digital | Date: | Sat, 07 Jun 2003 03:55:46 +0000 |
| Subject: | Re: Displaying groups from SELECT | ||
| References: | 1 2 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-28391@lists.php.net to get a copy of this message | ||
Thanks, Gürhan, but I think I needed to explain things better. As is generally
a good idea, the categories are referenced in the product table by ID, not name.
Additionally, this is something along the lines of what I already had. I was
trying to use only one query and make PHP do the remaining work.
Here's the code I'm currently using, which I should have posted in the first
place.
<?
$query = "SELECT * FROM categories";
$result = mysql_query( $query );
$rows = mysql_num_rows( $result );
print "<ul>\n";
while ( $cats = mysql_fetch_array( $result ) )
{
print "<li>" .$cats["cat_name"] ."</li>";
$queryB = "SELECT prod_name FROM products
WHERE prod_cat=" .$cats["cat_id"];
$resultB = mysql_query( $queryB );
print "<ul>";
while ( $items = mysql_fetch_array( $resultB ) )
{
print "<li>" .$items["prod_name"] ."</li>";
}
print "</ul>";
}
print "</ul>";
?>
The more I think about it, the more it seems like I'll just have to use two
queries. I just didn't want to do so bcs the second query will run at least
four times and it seemed inefficient.
Edward Dudlik
Becoming Digital
www.becomingdigital.com
----- Original Message -----
From: "Gürhan Özen" <ergenekon@starpower.net>
To: "Becoming Digital" <subs@becomingdigital.com>
Cc: "PHP-DB" <php-db@lists.php.net>
Sent: Friday, 06 June, 2003 23:07
Subject: Re: [PHP-DB] Displaying groups from SELECT
On Fri, 2003-06-06 at 21:49, Becoming Digital wrote:
> I'm wearing the stupid hat today, so please pardon this. I know I must be
> overlooking something.
>
> I have a small catalogue with two tables (categories, products) from which I'm
> trying to display items. I'm trying to print the contents as below without
> using two queries, but I'm having a difficult time with it.
>
> cat1
> prod1
> prod2
> cat2
> prod1
> prod2
> etc.
>
> I think this came up fairly recently, but I cannot for the life of me figure
out
> what search terms would answer this question. As you can see from the message
> subject, I don't even know how to refer to my problem. Thanks a lot for all
> your help.
>
> Edward Dudlik
> Becoming Digital
> www.becomingdigital.com
>
Hi Ed,
The magic word is "DISTINCT" :)
$query="SELECT DISTINCT(category) AS cat_name FROM table_name";
$result=mysql_query($query);
print "<ul>";
while ($row=mysql_fetch_array($result)) {
print "<li>".($row["cat_name"])."";
$query1="SELECT productname FROM tablename WHERE
category=".($row["cat_name"])."";
$result1=mysql_query($query1);
while ($row1=mysql_fetch_array($result1)) {
print "<li>".($row1["productname"])."";
}
print "</ul>";
}
print "</ul>";
I hope this helps..