PHP - Problem with return search INNER JOIN with GROUP BY!

Question

PHP - Problem with return search INNER JOIN with GROUP BY!

Navigation

#1 by (3 votes)
#2 by (-1 votes)

3

So, I have 3 table:

user:
    | ID | NOME | SOBRENOME |
    | 01 | Igor | Ferreira  |
    | 02 | João | Henrique  |
    | 03 | Rose | Beltão    |

following:
    | ID | user1_id | user2_id |
    | 01 |    01    |    02    |  User 1 > Segue User 2
    | 02 |    02    |    01    |     -- User 2 > Segue User 1
    | 03 |    01    |    03    |  User 1 > Segue User 3
    | 04 |    03    |    01    |     -- User 3 > Segue User 1
    | 05 |    03    |    02    |  User 3 > Segue User 2
    | 06 |    02    |    03    |     -- User 2 > Segue User 3

chat:
    | ID | user1 | user2 |      mensagem      |       data       |
    | 01 |   02  |   03  | ola Rose.          | 30/07/2015 08:25 |  De João > Para Rose
    | 02 |   03  |   02  | Oi João, como tas? | 30/07/2015 08:28 |  De Rose > Para João
    | 03 |   02  |   03  | Estou bem, e você? | 30/07/2015 08:29 |  De João > Para Rose
    | 04 |   01  |   02  | Rose você esta ai? | 30/07/2015 09:11 |  De Igor > Para Rose

In a div I'm listing the users who both follow and who brings the most recent message according to the chat table:

$sqln = mysqli_query($conn,"SELECT
                                u.id,
                                u.foto,
                                u.username,
                                u.nome_us,
                                u.sobrenome_us,
                                u.tell
                            FROM users u
                                RIGHT JOIN following f
                                    ON f.user1_id = '$id'
                                INNER JOIN chat c
                                    ON ((c.id_de = '$id' and c.id_para = f.user2_id) or (c.id_de = f.user2_id and c.id_para = '$id'))
                            WHERE
                                u.id = f.user2_id
                            GROUP BY 
                                u.id
                            ORDER BY
                                c.data DESC, c.hora DESC");

But if I by GROUP BY u.id , where it takes all the messages of a user and plays in a single box: butitdoesnotsortaccordingtothelatestmemosinthechattable.

NowifItakeoutGROUPBYu.iditactuallylistaccordingtothelatestones,butitthrowsallthemessageswithoutwantingtoknowofrepetitions:

How can I solve it ?? Do you really list the most recent table in the chat table and not repeat with a single div ?

php mysql

asked by anonymous 30.07.2015 / 20:20

2 answers

-1

You have to do a select on the outside grouping by user and a select on inside doing the sorting. This way:

SELECT * FROM 
(SELECT 
  u.id AS idUsers,
  u.foto,
  u.username,
  u.nome_us,
  u.sobrenome_us,
  u.tell 
FROM
  users u 
  RIGHT JOIN following f 
    ON f.user1_id = '$id' 
  INNER JOIN chat c 
    ON (
      (
        c.id_de = '$id' 
        AND c.id_para = f.user2_id
      ) 
      OR (
        c.id_de = f.user2_id 
        AND c.id_para = '$id'
      )
    ) 
WHERE u.id = f.user2_id 
ORDER BY c.data DESC,
  c.hora DESC) AS mensagens
GROUP BY idUsers

30.07.2015 / 20:40

Do delete via php Load xml file into Gridview?

score 3 · Accepted Answer

My query might be a little complex, but that's what I got without being familiar with the system and modeling.

SET @user = 2;

SELECT * FROM (
  SELECT
    z.id, 
    u.nome_us,
    -- u.foto,
    -- u.username,
    u.sobrenome_us,
    -- u.tell,
    (SELECT 'data' FROM chat 
     WHERE 
       (id_de = @user AND id_para = z.id) OR
       (id_para = @user AND id_de = z.id)
     ORDER BY 'data' DESC LIMIT 1
     ) AS 'data'
  FROM (
    SELECT DISTINCT x.id FROM (
        SELECT id_de AS id, 'data'
        FROM chat WHERE id_para = @user LIMIT 20

        UNION DISTINCT

        SELECT DISTINCT id_para AS id, 'data'
        FROM chat WHERE id_de = @user LIMIT 20
    ) AS x
  ) AS z
    INNER JOIN users u ON z.id = u.id
) AS j
  ORDER BY j.'data' DESC;

The query only returns the user data and the date of the last message, if you also want the last message, you can do the same way the date was searched, using a subquery .

Note: Note that I used the SQL variable in query @user , change to PHP.