Searching through multiple mysql databases

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Searching through multiple mysql databases

Post 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."'
        )
        ");
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Searching through multiple mysql databases

Post 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
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Re: Searching through multiple mysql databases

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Searching through multiple mysql databases

Post 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
 
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Searching through multiple mysql databases

Post 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.
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Re: Searching through multiple mysql databases

Post by Mr Tech »

Perfect :) Really appreciate your help on this one!
Post Reply