mysql - 在两个不同的列中搜索相同的数据

Question

嘿这是我的数据库

 users table                               pages table

|user_firstname|                           |page_firstname |
-----------------------------              --------------------------------
|  john    |                               |  matt    | 
|  james   |                               |  quentin | 
|  harry   |                               |  roland  |   
|  oliver  |                               |  thomas  | 

这是我对搜索应用程序的查询

SELECT user_id,   
       user_firstname, 
       user_lastname,
       user_profile_picture 
FROM users 
WHERE user_firstname LIKE :user_firstname
OR user_lastname LIKE :user_lastname

它适用于用户表。但是我也想从页面列中搜索。例如，当我按字母 M 时，它会找到 jaMes，但我也想要 Matt 和 thomas。问题是，如果我键入 Q，它应该只能从页面中找到 Quentin，如果我键入 J，它应该只能从用户中找到。如何在此查询中包含页面表？

更新我试过这个查询

SELECT user_id, 
       user_firstname, 
       user_lastname, 
       user_profile_picture 
FROM users 
WHERE user_firstname LIKE :user_firstname 
OR user_lastname LIKE :user_lastname 
UNION ALL SELECT page_id, 
                 page_firstname,  
                 page_lastname,  
                 page_profile_picture 
FROM pages 
WHERE page_firstname LIKE :page_firstname 
OR page_lastname LIKE :page_lastname

我的结果是这样的:

array (size=4)
  'user_id' => int 5
  'user_firstname' => string 'Roland' (length=3)
  'user_lastname' => string 'lastname' (length=7)
  'user_profile_picture' => string '5_1399841223_536fe1c70ea2b_user_profile.jpg' (length=43)

即使 roland 在 PAGES 表中，我得到的结果也与在 users 表中一样。我想将它们彼此分开

Answer 1

你可以这样做:

SELECT user_id,
 user_firstname,
 user_lastname,
 user_profile_picture, 'users' as 'type'
FROM users 
WHERE user_firstname LIKE :user_firstname
OR user_lastname LIKE :user_lastname 
UNION ALL SELECT page_id, 
 page_firstname, 
 page_lastname, 
 page_profile_picture ,'pages' as 'type'
FROM pages 
WHERE page_firstname LIKE :page_firstname 
OR page_lastname LIKE :page_lastname