What I did to somewhat solve the problem of over calling mysql was to
initiate the query outside the loop (SELECT prod_name query), put the
results into an array and then use the array inside the while($cats) loop.
For example,
$sql="SELECT prod_name, cat_id FROM products";
$result=mysql_query($sql, $conn);
Be sure to get the cat_id too! The go through the results and create an
associative array. I don't have my php book in front of me so I can't
write out the code verbatim. Plus it's Saturday morning.. the alcohol
hasn't left my brain from last night... :)
Then, inside your "while ( $cats = mysql_fetch_array( $result ) )" loop,
you call your array and compare each line with some equality test on
cat_id. That should do the trick.
metin
PS. there's probably an mysql query that could do what you want.. just
can't think of it right now.
On Fri, 06 Jun 2003 23:55:46 -0400 Becoming Digital wrote:
> 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: 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..
>
>
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
