Duplicate results with INNER JOIN

Question

Duplicate results with INNER JOIN

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I come with a problem with PHP and MYSQL. I tell you: I'm creating as a followers system, and I had it finished and I made it work, but the code was not the best we could say, because ... well, this was the code I had before:

$followers = "SELECT * FROM seguidores WHERE userSeguido = '".$userName."'";
$resultFollowers = $conexion->query($followers);

if ($resultFollowers->num_rows > 0) {
   while ($dataFollowers = $resultFollowers->fetch_assoc()) {
    $followedname = $dataFollowers['userSeguido'];
    $followername = $dataFollowers['userSeguidor'];

    $dataseguidores = "SELECT * FROM usuarios WHERE username = '".$followername."'";
    $resultdataseguidores = $conexion->query($dataseguidores);
    while ($dataFollowers_real = $resultdataseguidores->fetch_assoc()) {
        $usernameSeg = $dataFollowers_real['username'];
        $userprofilephotoSeg = $dataFollowers_real['userprofilephoto'];

        echo "<a href=\"$completeURL/user/$usernameSeg/bio\">
                <div class=\"follower\" style=\"background: url($completeURL/resize.php?r=$userprofilephotoSeg&w=200) center center / cover;\"></div>
            </a>";
    }
}
} else{
    echo "<p class=\"no-follow\">$userName no tiene seguidores</p>";
}

It works, yes, but you see that piece of code, and I made another one that also works, but it duplicates the results.

Capture with the first code:

Capture with the second code:

And well, I do not know why it happens. I made the code wrong, but I forgot to tell you that I'm almost new to this from MYSQL and PHP.

I hope you can help me! If you need anything else, just tell me.

The code that does not work:

$seguidores = "SELECT * FROM 
            seguidores S 
        INNER JOIN 
            usuarios U ON S.userSeguido = '$userName'
        WHERE U.username != '$userName'";
$result = $conexion->query($seguidores);

if ($result->num_rows > 0) {
    while ($data = $result->fetch_assoc()) {
        $seguidor_imagen = $data['userprofilephoto'];
        $seguidor_nombre = $data['username'];

        echo "<a href=\"$completeURL/user/$seguidor_nombre/bio\">
                <div class=\"follower\" style=\"background: url($completeURL/resize.php?r=$seguidor_imagen&w=200) center center / cover;\"></div>
            </a>";
    }
} else{
    echo "<p class=\"no-follow\">Nada por acá...</p>";
}

Structure of tables:

users:

userID (PK user ID)
username (username)
userprofilephoto (User Profile Picture)

followers:

IDuserfollow (user ID that was followed)
IDuserseguidor (user ID that followed)
userFollowed (Name of the user that was followed)
userSeguidor (Username that followed)

mysql php

asked by Axel Benitez 13.10.2016 в 04:52

source

2 answers

1

I would need the descriptions of the tables, but when together with join (in this case, the "inner" remains ...) you usually do it with the PK or some other key or index, and not with values such as name or phone . The followed user should have a reference to the main users table using the pk of the users table. With that it would be very easy (and much faster) to reference both data.

In the join, use indexes. In the where clause you can then search by name.

If possible, use USING instead of ON in the join, it will help you with duplicates.

If you do not want to spend time improving your sql, use "SELECT DISTINCT" to get rid of the duplicates.

It would then be JOIN i using the ids, more or less like this:

Select * from usuarios join seguidores on userID = idseguidores where userseguido = '$username'

If you have the userid of the logged user, use it in the where clause, it is better (by far) than using names.

answered by 13.10.2016 в 05:12

Difference between Public Virtual and List Django: Error during template rendering

score 0 · Accepted Answer

First of all I think you should eliminate some fields from the tables that can be redundant. You have a user table that contains all users and then a followers table with the relationships between them.

It is not necessary to have the user's name again. Also if the user changes their name is a problem since you would have to update it in all the followers and that is not an optimal thing.

usuarios:
    userID (ID del usuario PK)
    username (Nombre del usuario)
    userprofilephoto (Foto de perfil del usuario)

seguidores:
    IDuserseguido (ID del usuario seguido )
    IDuserseguidor (ID del usuario que esta siguiendo)

IDuserfollowed and IDuserfollower should form a PK so that a user can not follow the same user twice.

It is also recommended in the SQL to use only the fields you want to obtain and not use "*" to bring them all. If you make the query lighter, obtaining only what is required is more efficient.

With "*" you are getting all the fields from both tables, but you only use two from one of the two tables.

Having said that. If what you want is to get all the names and profile pictures of the users that a certain user follows, the ideal would be to know the ID of that user since it is his PK and make a query like:

SELECT 
    U.username as username,
    U.userprofilephoto as userprofilephoto 
FROM 
    seguidores S INNER JOIN usuarios U ON U.userID = S.IDuserseguido 
WHERE 
    S.IDuserseguidor = $idUsuario

If we do not have the user ID, we can either do the SQL before and put it or compose it in the previous SQL.

SELECT 
    U.username as username,
    U.userprofilephoto as userprofilephoto 
FROM 
    seguidores S INNER JOIN usuarios U ON U.userID = S.IDuserseguido 
WHERE 
    S.IDuserseguidor IN (SELECT userID FROM usuarios WHERE username='$username')