Help with SQL statement

[Don]

Hi folks

I wonder if someone could help me with a script I have been trying
unsuccessfully to write for a while now.

I have two tables - member_dest and member_det and have 3 search
criteria ie category, destination and gender. I have written the php
code to handle any combination of the 3 and produce $category, $dest
and $gender (some or all of which might be '%' ie wildcard. $category
and $gender can be found in member_det and $dest is from
member_destinat ion.

Now I need to select data from both tables by searching the 3 criteria
ie in english something along the lines of the following -

Select * from both tables where member_det.cate gory LIKE $category AND
member_det.gend er LIKE $gender AND member_dest.des tination LIKE $dest
Hope thats clear enough. Any help would be greatly appreciated.

Don

[Andy Hassall]

Re: Help with SQL statement

On 5 Jan 2005 11:18:58 -0800, "Don" <info@keyportmi lls.com> wrote:
[color=blue]
>I wonder if someone could help me with a script I have been trying
>unsuccessful ly to write for a while now.
>
>I have two tables - member_dest and member_det and have 3 search
>criteria ie category, destination and gender. I have written the php
>code to handle any combination of the 3 and produce $category, $dest
>and $gender (some or all of which might be '%' ie wildcard. $category
>and $gender can be found in member_det and $dest is from
>member_destina tion.
>
>Now I need to select data from both tables by searching the 3 criteria
>ie in english something along the lines of the following -
>
>Select * from both tables[/color]

What relates the two tables together?
[color=blue]
>where member_det.cate gory LIKE $category[/color]

Other than some quotes you're basically there, assuming you've escaped
$category appropriately.
[color=blue]
>AND
>member_det.gen der LIKE $gender AND member_dest.des tination LIKE $dest[/color]

Similar to above.

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

[Don]

Re: Help with SQL statement

Thanks Andy

member_id is the colum that relates both. I had the following query
working -

$query = 'SELECT member_hol_dest .member_id AS dest_id,
member_hol_deta ils.member_id AS det_id from member_hol_dest ,
member_hol_deta ils WHERE member_hol_dest .dest LIKE "'.$dest.'" AND
member_hol_deta ils.gender LIKE "'.$gender. '" AND
member_hol_deta ils.category LIKE "'.$category.'" ';


This worked but gave me an array that looked something like the
following:

=============== =============
member_id | Member_id |
=============== =============
1 | 1 |
1 | 2 |
2 | 1 |
2 | 2 |
| |
=============== ==============

What I really wanted was a list of member_id's of a those who satisfy
any or all of the search criteria.

Cheers

[News Me]

Re: Help with SQL statement

Don wrote:[color=blue]
> Thanks Andy
>
> member_id is the colum that relates both. I had the following query
> working -
>
> $query = 'SELECT member_hol_dest .member_id AS dest_id,
> member_hol_deta ils.member_id AS det_id from member_hol_dest ,
> member_hol_deta ils WHERE member_hol_dest .dest LIKE "'.$dest.'" AND
> member_hol_deta ils.gender LIKE "'.$gender. '" AND
> member_hol_deta ils.category LIKE "'.$category.'" ';
>
>
> This worked but gave me an array that looked something like the
> following:
>
> =============== =============
> member_id | Member_id |
> =============== =============
> 1 | 1 |
> 1 | 2 |
> 2 | 1 |
> 2 | 2 |
> | |
> =============== ==============
>
> What I really wanted was a list of member_id's of a those who satisfy
> any or all of the search criteria.
>
> Cheers
>[/color]

I would suggest using an explicit JOIN, that way you have a distinct
expression (the ON clause) that relates the two tables and another
distinct expression (the WHERE clause) for selection citeria. You
don't provide enough information to clearly understand the relationship
between the tables. I am assuming there is a many-to-one relationship
between member_hol_deta ils and member_hol_dest here. Then, using an
explicit (INNER) JOIN:

SELECT member_hol_dest .member_id AS dest_id,
member_hol_deta ils.member_id AS det_id
FROM member_hol_dest
INNER JOIN member_hol_deta ils ON
member_hol_deta ils.member_id=m ember_hol_dest. member_id
WHERE <condition 1>
AND/OR
<condition 2>
etc...

Hope this helps or at least doens't hurt...

NM

--
convert uppercase WORDS to single keystrokes to reply

[Don]

Re: Help with SQL statement

Excellent, I had an idea it was to do with joins but I dont have alot
of experience with them. I incorporated your script and it works
perfectly.

Thanks very much to both of you.

Don