PHP SQL query in while loop JOIN

Question

I'm making a basic notification system for a practice project. I have several tables, two of which look something like the following:

> Table 1: "Users"
> 
userid | username  | Firstname | Lastname  
1      | Daffy     |   Daffy   | Duck
2      | Flinstone |   Fred    | Flinstone 
3      | dduck     |   Donald  | Duck
> 
> Table 2: "Notifications"
> 
 Notification_id | from_user_id | to_user_id | SeenOrUnseen | type    
     1           |   1          |     2      |       1      |  fRequest               
>    2           |   1          |     2      |       0      |  comment               
>    3           |   1          |     2      |       1      |  comment               
>    4           |   3          |     1      |       1      |  fRequest               
>    5           |   2          |     3      |       1      |  fRequest               
>    6           |   2          |     3      |       0      |  comment               
>    7           |   3          |     2      |       0      |  comment

I then need data from both these tables, and would normally JOIN the tables on the user_id and from_user_id before sending of an sql query. However, a join seems to return multiple values because in the second table there are multiple instances of from_user_id. Instead, i'm querying the database, returning the data in a while loop, and within that while loop sending out another query to the database for a different tables' information:

include('../../db_login.php');
$con = mysqli_connect("$host", "$username", "$password", "$db_name");
$tbl_name = "Profile";
$tplname = "profile_template.php";
$tplname2 = "public_profile_template.php";
$myid = $_SESSION['identification'];
//CHECK CONNECTION
if(mysqli_connect_errno($con))  {
echo "failed to connect" . mysql_connect_error();
}else {
$result = mysqli_query($con, "SELECT * FROM Notifications WHERE to_user_id='$myid'");
$count = mysqli_num_rows($result);
if($count == 0){

    $style = "display:none;";
} else {

echo "<ul class='notifications'>";
    while($row = mysqli_fetch_array($result)){
    $from_user_id = $row['from_user_id'];
    $to_user_id = $row['to_user_id'];
    $seen = $row['seen'];
    $nature = $row['nature'];
        $result2 = mysqli_query($con, "SELECT * FROM users WHERE id='$from_user_id'");
        $count2 = mysqli_num_rows($result2);
        if($count2 != 0){
        while($row2 = mysqli_fetch_array($result2)){
        $fromFirstname = $row2['Firstname'];
        $fromLastname = $row2['Lastname'];

        }
    if($nature == 'fRequest'){
    echo "<li> You have received a friend request from" . $fromFirstname . " " . $fromLastname . "</li>";
    }
    }

    }   
    echo "</ul>";
}


mysqli_close($con);
}
                echo "<div id='NoNotification'></div>";
                echo "<div id='Notification' style='" . $style . "'></div>";
                ?>

Is there a better way of doing this?

Thanks for any help!

Answer 1

You can do something like this:

SELECT n.*, u.*
FROM Notifications n 
JOIN users u ON n.from_user_id=u.id
WHERE n.to_user_id=$myid
ORDER BY n.id, u.id

You will get all the data you need. The notification data and the user data. It is better to define which fields you want to retrieve in stead of * so you got a better view of what your using and your not sending data which you don't use.

Als you are using an integer (id) as a string, you can just put the $myid there. Also beaware this is not a safe query for MySQL injection. For more info.