CODEWITHSUNDEEP
phpdatabasemysqli
Keith Drake Waggoner
Keith Drake Waggoner

Reputation: 783

Mysqli Get Information from two different tables

I have two different tables one hold comments and the other user information my first mysqli_prepare statement looks like this

    if($stmt = mysqli_prepare($mysqli, "SELECT text,username,id,likes,dislikes FROM post WHERE school = '$sname' ORDER BY id DESC LIMIT 0,10"))

and the other looks like this 

   if($stmts = mysqli_prepare($mysqli, "SELECT avatar FROM users WHERE username = '$user'"))

basically i need to combine these two statements so that I can access both sets in the same while loop which looks like this 

    while(mysqli_stmt_fetch($stmt))

any help is appreciated thank you in advance and I will vote up.

Upvotes: 1

Views: 3471

Answers (2)

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

JOIN operation on common field username

SELECT post.text,post.username,post.id,post.likes,post.dislikes FROM post 
INNER JOIN users 
ON post.username = users.username
WHERE post.school = '$sname' 
AND users.username = '$user'
ORDER BY post.id DESC LIMIT 0,10

Upvotes: 2

Jason McCreary
Jason McCreary

Reputation: 73001

To do so with SQL you will need to combine your queries using JOIN.

Making some assumptions, the following is an example:

SELECT ... FROM post JOIN users ON post.username = users.username WHERE ...

Note: An alternative would be to SELECT the data separate (as you have) and stitch the data together with PHP. Sometimes this is preferred over a complex JOIN. However, what you have seems fine.

Upvotes: 1

Related Questions