Posted by alex.kemsley on 11/16/06 20:27

Captin Paralytic,

Thanks for your input,
I am using Mysql
Your were right that prices should have been dealers.
I can construct the html form with no trouble and access the submitted
values?
I have the database already constructed and can access it.
The main trouble i am having is creating a nested repeat region that
will display the three cheepest products and their corisponding dealer
infomation without listing each product more than once in php

Many thanks

Once again

Alex

Captain Paralytic wrote:
> 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]