Help with SQL statement

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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

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.


Re: Help with SQL statement

Quoted text here. Click to load it

 What relates the two tables together?
Quoted text here. Click to load it

 Other than some quotes you're basically there, assuming you've escaped
$category appropriately.

Quoted text here. Click to load it

 Similar to above.
< Space: disk usage analysis tool

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_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

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.


Re: Help with SQL statement

Don wrote:
Quoted text here. Click to load it

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
WHERE    <condition 1>
    <condition 2>

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


convert uppercase WORDS to single keystrokes to reply

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

Thanks very much to both of you.


Site Timeline