[thelist] one-to-many query to PHP output

[Ed Shuck]

Hi John,

I have a mysql table for categories, one for shoplookup (categories to shop)
and one for shop.

A viewer in my site clicks on category search and up comes a listing (some
listings are vacant) of shops and services in our area.  They click on an
item and go to a program called test.php3 (never changed the name) and down
in this program is the following code.

//query the db
$query="select shop.name, shop.address, shop.phone, shop.email, shop.url,
shop.fax, shop.notes, shop.shop_id"
 . " from shop, category, shoplookup"
 . " where category.cat_name='$class'"
 . " and category.cat_id=shoplookup.cat_id"
 . " and shoplookup.shop_id=shop.shop_id"
 . " order by shop.name";
$result=mysql_query($query)
or die("Cannot execute query");

the three tables are listed and each of the cols touched by the query.  The
thing does not repeat unless I have a double entry.

to see it work, use www.noevalley.com and click on category search.  The
code can be visable by using view source.

if the code looks helpful and you want the page of my meanderings and
absolutely horid treatment of coding, just let me know.

The trip starts with the $class which is related to a cat_id then to a
shoplookup_id then to the shop_id and finallyu the output.

peace
----- Original Message -----
From: john corry <john at neoncowboy.com>
To: <thelist at lists.evolt.org>
Sent: Monday, February 11, 2002 5:34 PM
Subject: [thelist] one-to-many query to PHP output


> I've been through the simple version of this already...but now I have a
more
> complicated example I need help with. I just *can't* see the required
logic
> yet...
>
> I have a query that JOIN's 3 tables.
>
> If I were to loop through the returns and output the results, I would get
> something like this:
>
> Prop Name
> Prop Desc
> Pic 1
> Pic 2
> Unit Type (for as many unique Unit Types are in the Rates table...)
> Rate 1
> Rate 2
> Rate 3
> Rate 4
>
> But I'd get it all these repetitive times...to satisfy the multiple
required
> iterations through the loop...For instance, Prop Name is the same each
time
> through the loop, Pic 1 & 2 are different (until they've been assigned to
> variables, then I don't need them anymore), Unit Type varies from property
> to property, but for each Unit Type, I need to echo the 4 rates for that
> Unit Type. What a mess!!
>
>
> OK...so, I need the logic that will let me break it all down and only
format
> & echo the parts I want through each iteration of the loop.
>
> So, I need to wind up with all those fields, but only once...god, does
that
> make any sense at all?
>
> My logic is like this:
>
> start loop
> if this is the first time through the loop
> assign Prop Name and Prop Desc to variables, format and print
> else
> if this is a new pic
> if all the pics have not been assigned
> make variables for the pics, write the tags, and print
>
> if this is a new unit type
> make a variable for this unit's 4 rates, format and print
> else
> make a new <tr> for this unit type,
> make a variable for this unit's 4 rates, format and print
> end loop
>
> But...I think there are some holes in this logic...I can't see how to
> pseudocode it all. If I could just see the logic/pseudocode I know I could
> write the PHP to do it all...I'm just hung up on figuring it all out.
>
> I'm about to resort to separate queries for each table...but I'll be
> disappointed in myself if I hack this up like that.
>
> anyone?
>
> thanks,
> John Corry
>
> --
> For unsubscribe and other options, including
> the Tip Harvester and archive of thelist go to:
> http://lists.evolt.org Workers of the Web, evolt !
>