单个查询以在多个表中搜索并生成1个结果

Question

I'm trying to create a power search using AJAX, that looks for results in 3 different tables and returns the answer in a single table. I have accomplished this by adding 3 calls to my database (one per table), but I feel that this is not optimal.

Users will be typing in a search field and I send what they type to a database using AJAX. This example is as simple as possible to make it easier to be understood.

Example: User Input: test.

AJAX call:

SELECT 'User' AS table, f_name AS `name`, city FROM people WHERE f_name LIKE '%test%';
SELECT 'Company' AS table, `name`, city FROM companies WHERE `name` LIKE '%test%';
SELECT 'Store' AS table, `name`, city FROM stores WHERE `name` LIKE '%test%';

I am not explaining how I generate the table form the ajax call, because that part is solved. What I currently do in PHP is:

$sql = SELECT .... FROM people ....;
$res = mysqli...
while($row = mysqli_fetch...) {
   echo "<tr><td>{$row[table]}</td>...."

$sql = SELECT .... FROM companies ....;
$res = mysqli...
while($row = mysqli_fetch...) {
   echo "<tr><td>{$row[table]}</td>...."

$sql = SELECT .... FROM stores ....;
$res = mysqli...
while($row = mysqli_fetch...) {
   echo "<tr><td>{$row[table]}</td>...."

Result:

+--------------+-------+------+
| table        | name  | city |
+--------------+-------+------+
| User         | test1 | 13   |
| User         | test2 | 25   |
| Company      | testc | 13   |
| Store        | stest | 33   |
+--------------+-------+------+

Is there an easier way to achieve this, using fewer HTTP requests? Perhaps using a view?

Answer 1

I suppose this could simply be a comment but I can't comment so: why not use UNION?

SELECT 'User' AS table, f_name AS `name`, city FROM people WHERE f_name LIKE '%test%'
UNION
SELECT 'Company' AS table, `name`, city FROM companies WHERE `name` LIKE '%test%'
UNION
SELECT 'Store' AS table, `name`, city FROM stores WHERE `name` LIKE '%test%'

That would give you exactly your result but with just one query-string.