Searching through multiple mysql databases
Posted: Wed May 28, 2008 7:12 pm
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...
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."'
)
");