Hi,
I want to run a query on a table while joining it with other tables.
Table 1- members
id - firstname - middlename - ....- country - region3
Fields country and region3 hold the integer codes for country and region3 is state
Table 2- country
id - countryname
Countryname is where the actual Country name is stored and id is the country code
Similarly Table 3 - region3
id - region3 where region3 is the state name and id is the region code.
I perform the following query to search the names in the database but the result only gives me the integer values for Country and Region3. That's because, the code is only stored in the database. But I need to join the table 2&3 with 1 so I can extract names of country or region3 rather than integer values.
How would I modify the query below?
$keywords are the search words.
$query = "
SELECT id, firstname, familyname, middlename, fathername, region3, region4, country,
MATCH(firstname, middlename, familyname, fathername) AGAINST ('$keywords' IN BOOLEAN MODE) AS score
FROM members
WHERE MATCH(firstname, middlename, familyname, fathername) AGAINST ('$keywords' IN BOOLEAN MODE) ORDER BY score DESC";
Thanks
Harmeet
