Mysql select question

**frizzle** · Jul 17 '05, 11:48 AM

Re: Mysql select question

anyone?

**frizzle** · Jul 17 '05, 11:49 AM

Re: Mysql select question

*please* anyone?

**Philip Ronan** · Jul 17 '05, 11:49 AM

Re: Mysql select question

frizzle wrote:
[color=blue]
> *please* anyone?
>[/color]

The answer is "probably".

I really can't say much more based on the information you provided. Perhaps
you should ask whoever wrote the mySQL backend.

--
phil [dot] ronan @ virgin [dot] net

http://vzone.virgin.net/phil.ronan/

**Mevlut** · Jul 17 '05, 11:49 AM

Re: Mysql select question

phpfrizzle@hotm ail.com wrote:
[color=blue]
> Hi there,
>
> I have a site with products on it.
> The site has a mysql backend.
>
> All products belong to certain series (table series).
> There can be up to 4 different products (table products)
> (categories 1-4) in 1 series.
>
> Each product has a defined 'series ID' which tells us
> what series the product belongs to.
>
> If i'm viewing all products with cat=1, how do i know if
> there is a cat=2 and/or cat=3 and/or cat=4 with the same
> series id. Can this be done in 1 query?
>
> Thanks!
>[/color]

left join

**frizzle** · Jul 17 '05, 11:49 AM

Re: Mysql select question

I tried to write/am writing the mySQL backend....
So for now it''s still quite flexible..

Any suggestions?

**frizzle** · Jul 17 '05, 11:50 AM

Re: Mysql select question

By the way, also tha database is only loaded with test-data for sofar,
so if anyone out there could help me ....

**Tim** · Jul 17 '05, 11:50 AM

Re: Mysql select question

[color=blue][color=green]
>>By the way, also tha database is only loaded with test-data for sofar
>>so if anyone out there could help me ...[/color][/color]

I would take a look at the left join and the inner join options.
Depending on the coding method, version of mysql, the desired workin
method, and the exact use will determine which one you want to use

--
Ti
-----------------------------------------------------------------------
Tim's Profile: http://www.hjyllc.com/forums/member.php?userid=
View this thread: http://www.hjyllc.com/forums/showthread.php?t=1669

**frizzle** · Jul 17 '05, 11:50 AM

Re: Mysql select question

Allrighty Tim, i'll have a look at that.
Thanks for the effort!

**Andy Hassall** · Jul 17 '05, 11:51 AM

Re: Mysql select question

On 28 Feb 2005 13:36:40 -0800, phpfrizzle@hotm ail.com wrote:
[color=blue]
>I have a site with products on it.
>The site has a mysql backend.
>
>All products belong to certain series (table series).
>There can be up to 4 different products (table products)
>(categories 1-4) in 1 series.
>
>Each product has a defined 'series ID' which tells us
>what series the product belongs to.
>
>If i'm viewing all products with cat=1, how do i know if
>there is a cat=2 and/or cat=3 and/or cat=4 with the same
>series id. Can this be done in 1 query?[/color]

Yes, probably. If you post a minimal runnable set of SQL statements to create
your tables and set up a few rows of representative sample data, so that it's
just a copy&paste job to set it up locally, you'll have a much better chance of
getting a better answer.

Sounds like a 4-way (left) outer join.

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

**frizzle** · Jul 17 '05, 11:51 AM

Re: Mysql select question

I'm sorry Andy,

i don't quite get what you mean.
As you have noticed i am not that skilled yet in mySQL/php.
Please explain a little bit more, that would be great..

Thanks!

**Andy Hassall** · Jul 17 '05, 11:51 AM

Re: Mysql select question

On 3 Mar 2005 13:34:27 -0800, "frizzle" <phpfrizzle@hot mail.com> wrote:
[color=blue]
>I'm sorry Andy,
>
>i don't quite get what you mean.
>As you have noticed i am not that skilled yet in mySQL/php.
>Please explain a little bit more, that would be great..[/color]

Post the definitions of your tables, and I can try and post SQL to do what you
want. What are your column names? What data do you have? If you post some
concrete details of your tables then I won't have to make up tables _and_ make
up the SQL - if you give a bit of help, it's easier to give help back, if you
see what I mean.

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

**frizzle** · Jul 17 '05, 11:51 AM

Re: Mysql select question

Ok, now i understand:

My site is about furniture:
Products can be the following:
- footstools (cat=1),
- chairs (cat=2),
- sofas (cat=3),
- diningchairs (cat=4).

There are two tables involved:
1st: Series
Series-table contents:
id (unique id)
active (can be '1' or '0', only active series
can be viewed)
name (name of the series)

2nd: Models
Models-table contents:
id (unique id)
active (can be '1' or '0', only active items
in active series can be viewed)
series_id (what series does the item belong to)
cat_id (is it a footstool, a chair, etc. ?)
size (measurements of the item)

What i need is e.g. when i'm viewing e.g. the chair (cat=2), to know if

there is a footstool, sofa etc. in the same series (with the same
series_id) with
series set active, and products set active....

Hope this clears out a few things a little bit.

The query i have so far is the following:

$getmodel = mysql_query("
SELECT s.name, m.id, m.size
FROM series s, models m
WHERE m.cat=$cat
AND m.id=$id
AND m.series_id=s.s eries_id
AND s.active=1
AND m.active=1
LIMIT 1")or die("bad query");

Thanks man!

**Andy Hassall** · Jul 17 '05, 11:52 AM

Re: Mysql select question

On 3 Mar 2005 14:13:54 -0800, "frizzle" <phpfrizzle@hot mail.com> wrote:
[color=blue]
>Ok, now i understand:
>
>My site is about furniture:
>Products can be the following:
>- footstools (cat=1),
>- chairs (cat=2),
>- sofas (cat=3),
>- diningchairs (cat=4).
>
>There are two tables involved:
>1st: Series
> Series-table contents:
> id (unique id)
> active (can be '1' or '0', only active series
> can be viewed)
> name (name of the series)
>
>2nd: Models
> Models-table contents:
> id (unique id)
> active (can be '1' or '0', only active items
> in active series can be viewed)
> series_id (what series does the item belong to)
> cat_id (is it a footstool, a chair, etc. ?)
> size (measurements of the item)
>
>What i need is e.g. when i'm viewing e.g. the chair (cat=2), to know if
>
>there is a footstool, sofa etc. in the same series (with the same
>series_id) with
>series set active, and products set active....
>
>Hope this clears out a few things a little bit.
>
>The query i have so far is the following:
>
> $getmodel = mysql_query("
> SELECT s.name, m.id, m.size
> FROM series s, models m
> WHERE m.cat=$cat
> AND m.id=$id
> AND m.series_id=s.s eries_id
> AND s.active=1
> AND m.active=1
> LIMIT 1")or die("bad query");[/color]

Hm - you may be nearly there, but need another join - i.e. first query for the
chair, then join to all the other products in the same series. Untested SQL:

SELECT s.name,
m2.id,
m2.size
FROM models m -- one row for the first product (see WHERE clause at end)
JOIN series s -- find the associated series
ON (m.series_id = s.series_id AND s.active = 1)
JOIN models m2 -- multiple rows for the other products in the series
ON (m2.series_id = s.series_id
AND m2.id != m.id
AND m2.active = 1)
WHERE m.cat = $cat
AND m.id = $id
AND m.active = 1

Is that anywhere close?

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

**frizzle** · Jul 17 '05, 11:52 AM

Re: Mysql select question

Wow, :|

First of all wow! Thanks for helping, and wow, what a query...
I'm quite new at this, and this really looks complicated.

But it was too good to be true... I cannot get it to work, and
i don't know where to look.. I tried a few things which could possibly
somehow make a little bit sense, but they didn't :(

I get the following error notice:
You have an error in your SQL syntax near 'FROM models m JOIN series s
ON (m.series_id = s.series_id AND s.'

Please help me, i really don't know where to fix this.

(btw, I found it hard to find some good join tuts also)

Mysql select question

Mysql select question

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment