合并两个不同的表，不重复PHP值

I have 2 tables; the first one is user_in_event table

我有2个表;第一个是user_in_event表

which here role can be regular, waiting for ack or moderator

这里哪个角色可以是常规角色，等待ack或版主

and the second one is user table

第二个是用户表

Here the role can be regular or admin

这里的角色可以是常规角色或管理员角色

I want to take the role from user_in_event table, based on the event_id and output all users info (beside user role) and output in a single JSON

我想从user_in_event表中获得角色，基于event_id并输出所有用户信息(除了用户角色)和一个JSON中的输出。

I have tried to use LEFT JOIN

我尝试使用左连接

$query = "SELECT user_in_event.role, user_in_event.user_id, user.name, user.email, user.birthday, user.phone_number, user.address, user.image
                    FROM user LEFT JOIN user_in_event 
                    ON user_in_event.event_id = '".$event_id."' 
                    LIMIT $num_of_rows";


        $result = mysqli_query($con,$query)
         or die(mysqli_error($con));

        // print_r($query);

        while ($row = mysqli_fetch_assoc($result)) { 
            $response[] = $row; 
        }




    header('Content-Type:Application/json');
    echo json_encode($response);

but I got messed up data

但我搞砸了数据

[
  {
    "role": "moderator",
    "user_id": "2",
    "name": "ofir",
    "email": "ofir@ofr.com",
    "birthday": "08/12/2016",
    "phone_number": "123",
    "address": "yoqneam",
    "image": "http://imgur.com/a/KslOW"
  },
  {
    "role": "waiting for ack",
    "user_id": "21",
    "name": "ofir",
    "email": "ofir@ofr.com",
    "birthday": "08/12/2016",
    "phone_number": "123",
    "address": "yoqneam",
    "image": "http://imgur.com/a/KslOW"
  }
]

PS: I also tried to make 2 different queries and combine the JSON and user array_merge_recursive but I got 2 subs arrays instead of a single one

PS:我还尝试了两个不同的查询，并结合了JSON和用户array_merge_recursive，但我得到了两个subs数组，而不是一个

1 个解决方案

#1

Please try :

请尝试:

SELECT user_in_event.role, user_in_event.user_id, user.name, user.email
FROM user
LEFT JOIN user_in_event ON user_in_event.user_id = user.user_id
WHERE user_in_event.event_id =25
LIMIT 0 , 30

Here event_id is static. run this query to your database and let me know.

这里event_id是静态的。运行这个查询到您的数据库并让我知道。

#1