CODEWITHSUNDEEP
phpmysqlloopswhile-loop
user3350282
user3350282

Reputation: 13

Fetching data from different table in while loop

I have two tables that generates an messageboard output. The first table uses a while loop to show all answers in a given topic from table posts. This works fine.

Then I have aniother table named thumbsup which collects when a user hits the "thumbs up" icon to a post in the messageboard. I only want the spesific user to be able to press the "thumbs up" button once.

I am trying to fetch data from table thumbsup to determine if the user has pressed the button or not for each post in the posts table.

I have tried the following sql statement:

SELECT topic, user FROM thumbsup WHERE topic = ".$posts_row['id']." AND user = ".$_SESSION['username']."

the table thumbsup looks like this:

id | topic | user
1  | 1     | bill
2  | 3     | rachel
3  | 5     | bill
4  | 5     | rachel
5  | 7     | rachel

my problem here is that I can't get the sql to sort by both topic and username. I can get it to sort by topic, but then it only outputs the first entry for the given post; in this case it would output "bill" on post number 5.

I think I maybe need a new while loop inside the existing while loop, but I haven't got that to work either.

Help much appreciated!

EDIT:

Ok, so I have this query to fetch the posts, AND to check if the user has a thumbsup record:

"SELECT posts.topic, posts.id, posts.content, posts.date, posts.user, posts.moderation, thumbsup.topic, thumbsup.user FROM posts LEFT OUTER JOIN thumbsup ON posts.id = thumbsup.topic AND thumbsup.user = ".$_SESSION['username']." WHERE posts.topic = " . $mysqli->real_escape_string($_GET['id']).""

EDIT2 - SOLUTION:

I finally got it right, much thanks to Kickstart.

Here is my final sql query:

SELECT posts.topic, posts.id, posts.content, posts.date, (posts.user) AS users, posts.moderation, thumbsup.topic, thumbsup.user FROM posts LEFT OUTER JOIN thumbsup ON posts.id = AA_forum_thumbsup.topic AND thumbsup.user = '".$_SESSION['username']."' WHERE posts.topic = " . $mysqli->real_escape_string($_GET['id'])."

As Kickstart mentioned, I needed extra quotes around the username as it is a string, and I also had to define the useres from the posts table as "users". Else the result would only output data on the posts where the username like $_SESSION['username'].

Thank you very much, and I hope this is useful for others as well!

Upvotes: 1

Views: 970

Answers (2)

Kickstart
Kickstart

Reputation: 21513

Using a LEFT JOIN, something like this:-

SELECT posts.id, thumbsup.user 
FROM posts
LEFT OUTER JOIN thumbsup 
ON posts.id = thumbsup.topic
AND thumbsup.user = '".$_SESSION['username']."'

This will bring back all the posts (in this case just the ids of them) and user field will contain the username if $_SESSION['username'] has a thumbsup record for this post. If they don't then the user field returned will be NULL

Upvotes: 1

Supriya Pansare
Supriya Pansare

Reputation: 559

I think you are login to your application,if yes then only take record for login user and then sort it by topic. now u will get array with values as topic.Here you can check using inarray() .If the value is persent in inarray then it means the user had already voted.

Upvotes: 0

Related Questions