Re: [PHP-DB] Joining Tables for Search

2004-07-26 Thread Pablo M. Rivas 
Hello Harry,
you can use left join:
http://dev.mysql.com/doc/mysql/en/JOIN.html

 $query = 
  SELECT members.id, firstname, familyname, middlename, fathername,
  region3.region3, region4, members.country, country.countryname,
 MATCH(firstname, middlename, familyname, fathername) AGAINST ('$keywords' IN 
BOOLEAN MODE) AS score
FROM members left join country on members.country =  country.id
left join region3 on members.region3 = region3.id
   WHERE MATCH(firstname, middlename, familyname, fathername) AGAINST ('$keywords' 
IN BOOLEAN MODE)
ORDER BY score DESC;

good luck.

HG Hi,

HG I want to run a query on a table while joining it with other tables.

HG Table 1- members
HG id - firstname - middlename - - country - region3

HG Fields country and region3 hold the integer codes for country and region3 is state

HG Table 2- country
HG id - countryname
HG Countryname is where the actual Country name is stored and id is the country code

HG Similarly Table 3 - region3
HG id - region3 where region3 is the state name and id is the region code.

HG 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.
HG But I need to join the table 23 with 1 so I can extract names of country or 
region3 rather than integer values. 

HG How would I modify the query below?

HG $keywords are the search words.

HG $query =  
HGSELECT id, firstname, familyname, middlename, fathername, region3, region4, 
country,
HG   MATCH(firstname, middlename, familyname, fathername) AGAINST 
('$keywords' IN BOOLEAN MODE) AS score  
HG   FROM members 
HGWHERE MATCH(firstname, middlename, familyname, fathername) AGAINST 
('$keywords' IN BOOLEAN MODE) ORDER BY score DESC; 

HG Thanks
HG Harmeet




-- 
Best regards,
 Pablo

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Joining Tables for Search

2004-07-26 Thread Harry G 
Thanks Pablo, that worked.

Pablo M. Rivas [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
 Hello Harry,
 you can use left join:
 http://dev.mysql.com/doc/mysql/en/JOIN.html

  $query = 
   SELECT members.id, firstname, familyname, middlename, fathername,
   region3.region3, region4, members.country, country.countryname,
  MATCH(firstname, middlename, familyname, fathername) AGAINST
('$keywords' IN BOOLEAN MODE) AS score
 FROM members left join country on members.country =  country.id
 left join region3 on members.region3 = region3.id
WHERE MATCH(firstname, middlename, familyname, fathername) AGAINST
('$keywords' IN BOOLEAN MODE)
 ORDER BY score DESC;

 good luck.

 HG Hi,

 HG I want to run a query on a table while joining it with other tables.

 HG Table 1- members
 HG id - firstname - middlename - - country - region3

 HG Fields country and region3 hold the integer codes for country and
region3 is state

 HG Table 2- country
 HG id - countryname
 HG Countryname is where the actual Country name is stored and id is the
country code

 HG Similarly Table 3 - region3
 HG id - region3 where region3 is the state name and id is the region
code.

 HG 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.
 HG But I need to join the table 23 with 1 so I can extract names of
country or region3 rather than integer values.

 HG How would I modify the query below?

 HG $keywords are the search words.

 HG $query = 
 HGSELECT id, firstname, familyname, middlename, fathername,
region3, region4, country,
 HG   MATCH(firstname, middlename, familyname, fathername) AGAINST
('$keywords' IN BOOLEAN MODE) AS score
 HG   FROM members
 HGWHERE MATCH(firstname, middlename, familyname, fathername)
AGAINST ('$keywords' IN BOOLEAN MODE) ORDER BY score DESC;

 HG Thanks
 HG Harmeet




 -- 
 Best regards,
  Pablo

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Joining Tables for Search

2004-07-25 Thread Harry G 
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 23 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