Posted by alex.kemsley on 11/15/06 23:08

Dear All,

I am quite new to sql and php and jumping in at the deep end. Your help
is much apreciated.

I have two tables products and dealers.
products contains all the products product id and their spec
dealers contains the product id the dealer and the price they sell it
at.
They have a one to many relationship with there being one product sold
a multiple dealers.

I am trying to do a search of these tables from a form that producing a
single table of results.

Form is as follows:

________________________________

Shape

Max Size: H W L (cm)

Seats

Min price

Max price
__________________________________

I have drafted this sql but it may be wrong

_________________________________

SELECT * FROM products WHERE seatsfrom = %s AND dimwidth <= %s AND
dimhight <= %s AND dimlength <= %s AND type = %s AND shape = %s"

SELECT TOP 3 * FROM prices WHERE tubid = '%s' ORDER BY price
_____________________________________

>From that I am looking to produce a table of results similar to this:
----------------------------------------------------------------------------------------------------------------------
manufacuter - model1 - seats - W / L / H - type - shape -
price1lowest(dealer name) - price 2nexthigh(dealer name) - price 3 next
high (dealer name)

manufacuter - model2 - seats - W / L / H - type - shape -
price1lowest(dealer name) - price 2nexthigh(dealer name) - price 3 next
high (dealer name)

manufacuter - model3 - seats - W / L / H - type - shape -
price1lowest(dealer name) - price 2nexthigh(dealer name) - price 3 next
high (dealer name)
----------------------------------------------------------------------------------------------------------------------
where price1 increases as you go down the table.

Your help would be much apreciated to help me construct a search page
to do this as I am having some great trouble.

Best Regards,

Alex Kemsley

[Back to original message]