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_destination.

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.category LIKE $category AND
member_det.gender LIKE $gender AND member_dest.destination LIKE $dest
Hope thats clear enough. Any help would be greatly appreciated.

Don

[Andy Hassall]

On 5 Jan 2005 11:18:58 -0800, "Don" <in**@keyportmills.com> wrote:

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_destination.

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
What relates the two tables together?
where member_det.category LIKE $category
Other than some quotes you're basically there, assuming you've escaped
$category appropriately.
AND
member_det.gender LIKE $gender AND member_dest.destination LIKE $dest

Similar to above.

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Don]

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_details.member_id AS det_id from member_hol_dest,
member_hol_details WHERE member_hol_dest.dest LIKE "'.$dest.'" AND
member_hol_details.gender LIKE "'.$gender.'" AND
member_hol_details.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]

Don wrote:

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_details.member_id AS det_id from member_hol_dest,
member_hol_details WHERE member_hol_dest.dest LIKE "'.$dest.'" AND
member_hol_details.gender LIKE "'.$gender.'" AND
member_hol_details.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

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_details and member_hol_dest here. Then, using an
explicit (INNER) JOIN:

SELECT member_hol_dest.member_id AS dest_id,
member_hol_details.member_id AS det_id
FROM member_hol_dest
INNER JOIN member_hol_details ON
member_hol_details.member_id=member_hol_dest.membe r_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]

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