嘿,这是我的数据库
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,则仅应从用户中找到。如何在此查询中包含pages表?
更新我试过这个查询
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表中那样得到结果。我想将它们彼此分开
你可以做这样的事情:
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句