John107 John107 - 4 days ago 4
MySQL Question

PHP MySQL Select * from 2 different tables and display the merged data

I have two tables one called 'user' and the other called 'user_info'.

user;

userid | username
------------------------------
1 | mary
2 | john
3 | liam


user_info;

userid | desc
----------------------------------------------------
1 | hello, my name is mary i am 26
2 | message me if you need any help
3 | please leave me alone


I want to display the corrosponding description from the 'user_info' table with the information from the 'user' table based on the userID. So that it will appear like:

Name: John
UserID: 2
Description: message me if you need any help


Here is my code;

//Query the database
$resultSet = $mysqli->query("SELECT * FROM user,user_info");

//Count the returned rows
if ($resultSet->num_rows != 0) {


//Turn the results into an ArrayAccess
while($rows = $resultSet->fetch_assoc())
{
$uid = $rows['UserID'];
$username = $rows['username'];
$desc = $rows['desc'];

echo "<p>Name: $username </p>
<p>User ID: $uid </p>
<p>Description: $desc </p>
<hr>";
}


Currently it returns as many different combinations as it can; like so:

Name:Mary
UserID:1
Description:hello, my name is mary i am 26
--------------------------------------------
Name:John
UserID:1
Description:message me if you need any help
--------------------------------------------
Name:Liam
UserID:1
Description:please leave me alone
--------------------------------------------
Name:Mary
UserID:2
Description:hello, my name is mary i am 26
--------------------------------------------
Name:John
UserID:2
Description:message me if you need any help
--------------------------------------------
ETC...


How can I make it so that it only returns the correct corresponding data based on the userID (only once) so that it displays all the users with their correct username, id and description?

I wasn't sure how else to explain this, if anyone would like me to further elaborate I have no problem trying again,

Thank you!

Answer

Update your request

$resultSet = $mysqli->query("SELECT * FROM user 
    JOIN user_info ON user_info.userid = user.userid");