Posted by Captain Paralytic on 11/16/06 12:34

alex.kemsley@hottubs2buy.co.uk wrote:
> 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

I don't know what flavour of SQL you are using (you haven't told us),
but whatever one you are using, I would combine those 2 queries into a
single JOINed query.

However according to what you first said "I have two tables products
and dealers.", but your queries are on "products" and "prices"???

Assumming that "prices" should be "dealers" ,the query will likely look
something like:
SELECT * FROM products, dealers WHERE seatsfrom = ? AND dimwidth <= ?
AND
dimhight <= ? AND dimlength <= ? AND type = ? AND shape = ?
AND products.tubid = dealers.tubid

However if you can tell us precisely what SQL you are using, we can be
more helpful.

Also you don't tell us what trouble you are having creating a search
page?
Is it that you don't know how to construct an HTML form?
Is it that you don't know how to use php to access submitted values?
Is it that you don't know how to use the api to your dbms?
Is it ...

Please help us to help you!

[Back to original message]