vinod vinod - 1 year ago 72
PHP Question

Query for searching multiple user inputs in HTML text field for multiple tables

We have a HTML search page, having multiple text fields to search.

The user can input as many values as he want in text field and on submit, the query should return appropriate results.

We have different tables as

  • candidate

  • candidate_contact

  • company

  • etc.


User enters like candidate should have java skills also lives in California but should not have experience less than 2 years.

These records can be in same or different tables (like skills and country in same table and exp in another table)

Its like including and excluding search result.

$query = array();
if (!empty($_POST['keyword_s_dec']))
$query[] = "candidate.cand_desc = '".mysql_real_escape_string($_POST['keyword_s_dec'])."'";
$join.="JOIN candidate_contact ON candidate.cand_desc=candidate_contact.cand_id";
//$join.="select * from candidate join candidate_contact ON candidate.cand_number=candidate_contact.cand_id WHERE candidate.cand_desc='".$_POST['keyword_s_dec']."'";
if (!empty($_POT['keyword_s_location']))
$query[] = "candidate_contact.cand_location = '".mysql_real_escape_string($_POST['keyword_s_location;'])."'";///edit
$join.=" AND JOIN candidate_contact ON candidate.cand_number=candidate_contact.cand_id";

//$condition = implode(' AND ', $query);
$condition = implode(' AND ', $query);
$sql = "SELECT * FROM candidate".$join.' where '.$condition;

where - candidate is my main table and candidate_contact is another table.
where -cand_desc is database column in candidate & keyword_s-dec is text field id.
where- cand_location is my database column in candidate_contact and keywors_s_location is text field id.

This code is being slightly guided by one of stack overflow member and we though this should be edit to get more precised to the problem.

Answer Source
SELECT candidate.*,candidate_contact.feild_name1,candidate_contact.feild_name2
FROM candidate 
LEFT JOIN candidate_contact  
ON candidate.cand_number=candidate_contact.cand_id where $condition

Note:- add the table name with .(dot)in the where clause to define which feild name you want to do the comparision as shown in selection field area of the query and can continue joinning different tables in the same way as above before the where clause.

Hope this will solve your problem