Search code examples
phpmysqlsearch

MySQL search in multiple columns

Title sounds pretty simple... the main problem is from which direction I should query and how to output the results.

Here's a Screenshot with a empty search:

enter image description here

The clients should always be visible (matching the search query). So here a few examples:

  • Search for project2 should output client2 with project2
  • Search for client2 should output client2 with all projects
  • Search for client3 should output client3

I query for projects and join in the clients like this:

SELECT *
FROM projects AS a
LEFT JOIN clients AS b ON a.client_id = b.id
WHERE a.name LIKE '%$searchString%' OR b.name LIKE '%$searchString%'

After that I query for clients excluding these that are fetched in the query above. That's the only way (which I know) to get the clients that don't have any projects.

Is there a statement that can fetch these results in one query? This seems not very elegant to me. Also if I search client2 project1 the system fails.

EDIT: Thanks for the answers so far. I got a lot with full joins that are not possible in MySQL. I changed that in the title and tags of the question... sorry for that. I didn't know anything about full joins and that they are not possible in MySQL.

Solution

  • Here is a good visual representation of the different join types in SQL.

    Make sure you are using prepared statements, and not directly putting the searchString into the query, as this is how SQL injection attacks occur.

    SELECT
  *
FROM
  clients b LEFT OUTER JOIN
  projects AS a ON a.client_id = b.id
WHERE
  a.name LIKE '%$searchString%' OR
  b.name LIKE '%$searchString%'