Page 1 of 1

Searching through multiple mysql databases

Posted: Wed May 28, 2008 7:12 pm
by Mr Tech
I've got a member script I am creating... most of the member details are saved in the members database table...

However there are a few other tables that the member details are saved in such as clubs and courses... Both these tables have a member_id and a club_id and course_id column... I have about 4 of these tables in total. These tables allow me to choose multiple courses and clubs a member is apart of...

I need to make it so when someone searches the members, the script also searches those tables above matching the member_id with a member and the other id with their search...

I know you can use the table JOIN but does that only work for 2 tables or could I use it for 5? How would my code look?

Here is some sample code of what I am asking is possible... It's very rough but hopefully iy gives you an idea of what I am trying you acheive...

Code: Select all

$query = mysql_query("SELECT * FROM members AS m LEFT JOIN clubs AS c LEFT JOIN courses AS c1 ON m.id = c.member_id = c1.member_id where 
        (
            m.name rlike '".$search_keywords."' or 
            m.email rlike '".$search_keywords."' 
        ) or (
            c.club_id='".$search_club."' or
            c1.course_id='".$search_course."'
        )
        ");

Re: Searching through multiple mysql databases

Posted: Thu May 29, 2008 11:39 pm
by califdon
You can join as many tables as you want, but you need an ON clause for each JOIN clause. You can also use regular syntax without the JOINs, by establishing the relationships in the WHERE clause. Learn the syntax: http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

Re: Searching through multiple mysql databases

Posted: Sun Jun 01, 2008 9:15 pm
by Mr Tech
Thanks for your reply :) I'm still setting up my code so I haven't had a chance to test it all yet... but is this how the code should look? If not, how should format my SQL?

Code: Select all

SELECT * FROM 
members AS m 
LEFT JOIN clubs AS c 
LEFT JOIN courses AS c1 
ON m.id = c.member_id = c1.member_id
or even...

Code: Select all

SELECT * FROM 
members AS m 
LEFT JOIN clubs AS c 
LEFT JOIN courses AS c1 
ON m.id = c.member_id 
ON m.id = c1.member_id
An I'm guessing if it doesn't find any results from the joined tables, it will show results if they exist in the members table right?

Appreciate your help

Re: Searching through multiple mysql databases

Posted: Sun Jun 01, 2008 9:59 pm
by Eran
Each join statement needs to be placed with its own ON condition (alternatively, you can specify ON conditions as WHERE clauses). If you go the ON condition route, they need to be declared directly after the JOIN is declared.
Ie:

Code: Select all

 
SELECT * FROM
  members AS m
  LEFT JOIN clubs AS c ON m.id = c.member_id
  LEFT JOIN courses AS c1 ON m.id = c1.member_id
 

Re: Searching through multiple mysql databases

Posted: Sun Jun 01, 2008 10:46 pm
by califdon
Mr Tech wrote:An I'm guessing if it doesn't find any results from the joined tables, it will show results if they exist in the members table right?

Appreciate your help
pytrin explained the positioning of the ON clauses very nicely.

As to your final question, you're exactly right. That's the function of a LEFT JOIN, all the records in the "left" table (the one mentioned first) will be returned, but only those from the other table which match the ON condition.

Re: Searching through multiple mysql databases

Posted: Mon Jun 02, 2008 7:57 pm
by Mr Tech
Perfect :) Really appreciate your help on this one!