MYSQL RIGHT JOIN and PHP

Question

I have three tables:

users (primary key: id_users)
centers (primary key: id_centers)
usersInCenters (primary key: id_usersInCenters, foreign keys: id_users and id_centers)

With the third table I connect the users with the centers, because 1 user can attend to different centers.

And with this SELECT I get the result that I want:

 SELECT *
 FROM `userInCenter`
 RIGHT JOIN `user` ON `user`.`id_user` = `userInCenter`.`id_user`
 ";

With this query I get:

So, this is actually pretty good, and almost achieved what I want.

The only thing, is that my query creates two rows if one user has two centers.

But, what I want, is to display one row and show the centers in the same field: 8,7. Like this.

Is this possible? Or do I have to change something?

FIDDLE: http://sqlfiddle.com/#!9/0fed10/2

PHP:

        <?php 
    while ($row=mysqli_fetch_object($listaDeUsuarios)){

        $id_usuario=$row->id_usuario;
        $nombreUsuario=$row->nombreUsuario;
        $apellidoUsuario=$row->apellidoUsuario;
        $emailUsuario=$row->emailUsuario;
        $ciudadUsuario=$row->ciudadUsuario;
        $createdAt=$row->createdAt;
        $id_centroDeportivo=$row->id_centroDeportivo;
         ?>

Thanks in advance Alejandro.

use this: "GROUP BY Centros" or "GROUP BY Centros ORDER BY Centros" — Raskul, Feb 20 '21 at 19:19
Does this answer your question? [How to use GROUP BY to concatenate strings in MySQL?](https://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql) — Marleen, Feb 20 '21 at 19:23
@Stanzi1791 i think the answer is there, but i can´t make it work: Warning: mysqli_fetch_object() expects parameter 1 to be mysqli_result, bool given in — Alejandro Arias Aguilar, Feb 20 '21 at 19:41
Could you add the php and sql code you're using, and the error it's giving to the original post? — Marleen, Feb 20 '21 at 19:44
@Stanzi1791 done! thanks the PHP is a simple mysqli_fetch_object — Alejandro Arias Aguilar, Feb 20 '21 at 19:55
@Stanzi1791 thats exacly what i want to achieve! but its not working in my site, just updated with php — Alejandro Arias Aguilar, Feb 20 '21 at 20:11
Is `$listaDeUsarios` the result of a call to mysql_query? If that value is false there is likely an error in your query. — Marleen, Feb 20 '21 at 20:32
@Stanzi1791 yes, $listaDeUsuarios = $usuarios -> listarUsuarios(); it´s just how i call mysqli. is there any other code rather than mysqli_fetch_object that will work with group by? — Alejandro Arias Aguilar, Feb 20 '21 at 20:56
mysqli_fetch_object should work with group by. I've posted my php code as an answer below, it works with the database you provided in the fiddle. — Marleen, Feb 20 '21 at 21:45
While undoubtedly 'complete', I'm less convinced that your fiddle is 'minimal'. — Strawberry, Feb 21 '21 at 01:08

Marleen · Accepted Answer · 2021-02-20T21:56:52.227

<?php
$con = mysqli_connect("localhost", "root", "", "gymtable_test");

//Retrieving the contents of the table
$res = mysqli_query($con, "SELECT ucd.id_usuarioCentroDeportivo, ucd.id_usuario, GROUP_CONCAT(ucd.id_centroDeportivo SEPARATOR ',') AS id_centroDeportivo, ucd.rolUsuarioCentro, ucd.createdAt, u.*
FROM usuarioCentroDeportivo ucd
RIGHT JOIN usuario u ON u.id_usuario = ucd.id_usuario
GROUP BY ucd.id_usuario");

//Fetching all the rows as objects
while ($row=mysqli_fetch_object($res)){
    $id_usuario=$row->id_usuario;
    $nombreUsuario=$row->nombreUsuario;
    $apellidoUsuario=$row->apellidoUsuario;
    $emailUsuario=$row->emailUsuario;
    $ciudadUsuario=$row->ciudadUsuario;
    $createdAt=$row->createdAt;
    $id_centroDeportivo=$row->id_centroDeportivo;
    // do something with the variables
}
//Closing the statement
mysqli_free_result($res);

//Closing the connection
mysqli_close($con);

GROUP BY groups together rows with the same user id (ucd.id_usuario), GROUP_CONCAT then displays all the centers for each user id separated with a comma.

nothing happens , whatever i print or echo inside the while is stopped — Alejandro Arias Aguilar, Feb 20 '21 at 22:43
@Strawberry i use it so 1 user can attend to different centers — Alejandro Arias Aguilar, Feb 21 '21 at 07:27

MYSQL RIGHT JOIN and PHP

1 Answers1