multiple table searching (complex!!!)

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

Re: multiple table searching (complex!!!)

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!

Re: multiple table searching (complex!!!)

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!

Re: multiple table searching (complex!!!)

alex.kemsley@hottubs2buy.co.uk wrote:
> 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
First thing to say is "Please don't top post". This forum uses bottom
or interleaved posting to improve readability.

The SQL that you posted is not MySQL as far as I am aware, you will
need to use an ORDER BY and a LIMIT 3 on my sample SQL. The SQL that
you posted looked more like MSSQL.

Now, the first thing I did when I saw your latest message was type
"nested repeat region" into google and found on the first page:

http://www.finalwebsites.com/snippets.php?id=30

which is a script showing how to use PHP/MySQL to obtain a nested
repeat region. I am sure that you have already done such a simple thing
as googling what you wanted and so have seen this sample script. So
could you explain in what way this script does not meet your needs so
that we may advise you on what else you may need to do?

Re: multiple table searching (complex!!!)

Captain Paralytic wrote:

> The SQL that you posted is not MySQL as far as I am aware, you will
> need to use an ORDER BY and a LIMIT 3 on my sample SQL. The SQL that
> you posted looked more like MSSQL.

And the used variables was visualbasic variables (%vbasic, $php), so it looks
he tries to convert an old vbasic/mssql script into php/mysql, could be
usefull to run those scripts with asp2php.



//Aho