RomanGP RomanGP -5 years ago 121
PHP Question

Search in two tables and combine results with PDO

I have two tables named companies and jobs. They are related - jobs table has a foreign key company_id.

company table columns are: companyname, city, province etc.

jobs table columns: title, department, description etc

I want to search both tables in those columns and if the keyword has a match in jobs table get the related data from the company table, and if the match is in the company table, I want to get all the jobs related to the company.

As far as I know in order to search in both tables I have to use UNION, this works:

$sql= "(SELECT title, department FROM jobs WHERE title LIKE ? )
(SELECT companyname,city FROM companies WHERE companyname LIKE ?)";

I dont know how to get related data from the other table depending on the match. Is it possible with just one call using JOIN?


Answer Source

Use JOIN and OR condition:

SELECT jobs.title, jobs.department,
FROM jobs 
WHERE (jobs.title LIKE ? OR companies.companyname LIKE ?)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download