如何编写MySQL查询以在条件下搜索两个表中的数据？ [关闭]

Question

I want to make a search bar on a website for a inbox; searching sender name and message as I type in search bar that display result with username and their profile pic and message. Just like WhatsApp search bar, where you can search any sender name and any message.

For that, I have two tables. First one name is user and second table name is inbox. Both having column like:

user - uid,name,lastname,profile_pic,address,dob.

inbox - id,msg_to,msg_from,msg,time,latestmsg,opened.

I want to search data that can be any user name and can be any word of message, from both tables. So, how should be this query design?

I had designed a query in following way:

 SELECT u.name
         , u.lastname
         , u.profile_pic
         , i.msg_from
         ,i.msg_to
         , i.msg,i.time
      from user
         , inbox i
     where 
         (
           u.uid=i.msg_from LIKE '%$search%'
        OR u.uid=i.msg_to LIKE '%$search%'
         )  
       AND
         (
           i.msg_to='$uid' LIKE '%$search%' 
        OR i.msg_from='$uid' LIKE '%$search%'
         ) 
       AND u.uid!=$uid 
       AND latestmsg=1
     GROUP 
        BY i.id DESC

But, it is not working.

By the way, the query for displaying username and message alongside profile pic (just like WhatsApp and any chatting system) is :

SELECT u.name,
       u.lastname,
       u.profile_pic,
       i.msg_from,
       i.msg_to,
       i.msg,
       i.time
FROM user u,
     inbox i
WHERE (u.uid=i.msg_from
       OR u.uid=i.msg_to)
  AND (i.msg_to='$uid'
       OR i.msg_from='$uid')
  AND u.uid!=$uid
  AND latestmsg=1
GROUP BY i.id DESC

Answer 1

At a first look

You should not use old sintax based on implicit join but use explicit sintax

SELECT distinct u.name
  ,u.lastname
  ,u.profile_pic
  ,i.msg_from
  ,i.msg_to
  ,i.msg,i.time 
from comnet_user_details u
INNER JOIN  comnet_inbox i ON 
  (u.uid=i.msg_from LIKE '%$search%' OR u.uid=i.msg_to LIKE '%$search%')  
  AND 
  (i.msg_to='$uid' LIKE '%$search%' OR i.msg_from='$uid' LIKE '%$search%') 
  AND 
  u.uid!=$uid AND latestmsg=1 
   ORDER BY i.id DESC";

You should not use group by if yoy don't use aggregation function ,, you should use DISTINCT (in most recente version of mysql the use of group by withut aggreation function produce error and in the others version produce unpredictable result )

Could be that you are looking for an ORDER BY DESC instead

You should also not use PHP var in sql, you are at risk for sqlinjecttion
you should take a look at your php db driver for prepared statement and bindig param

This a simplified version for let you see a (probaly) correct use of the column involved in join and search

SELECT u.name
         , u1.lastname lastname_from
         , u1.profile_pic profile_pic_from
         , u2.lastname lastname_to
         , u2.profile_pic profile_pic_to
         , i.msg_from
         , i.msg_to
         , i.msg 
         , i.time
FROM comnet_inbox 
INNER JOIN comnet_user_details u1 ON  u1.uid=i.msg_from
INNER JOIN comnet_user_details u2 ON u1.uid=i.msg_to
WHERE i.msg LIKE concat('%', $search,'%')
ORDER BY i.id DESC