Fetching data from different table in while loop

Question

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']).""

This results in none posts in the while loop, but if I remove the username, it shows only the posts again. Is there something wrong in my query?

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!

what you actually want? couldn't understand give an example. what you want for topic no 5 — Satish Sharma
– Satish Sharma, Commented Feb 25, 2014 at 9:02
it would output the first thumbsup of username on a certain topic. You don't need a while loop if you want to know only if this person clicked thumbs up on that post — CodeBird
– CodeBird, Commented Feb 25, 2014 at 9:08
join the two tables and use sort in your query (sort multiple columns with comma separated) — Viswanath Polaki
– Viswanath Polaki, Commented Feb 25, 2014 at 9:12
I want to be able to determine if the user has already voted for the post or not. If the user has voted, then he will not be able to vote for that spesific post again. Just like a forum "thumbs up" or Facebooks "Like" button. — user3350282
– user3350282, Commented Feb 25, 2014 at 9:19

Kickstart · Accepted Answer · 2014-02-25 12:22:34Z

1

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

edited Feb 25, 2014 at 12:22

answered Feb 25, 2014 at 10:36

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Supriya Pansare · Accepted Answer · 2014-02-25 09:25:50Z

0

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.

answered Feb 25, 2014 at 9:25

Supriya Pansare

5791 gold badge4 silver badges11 bronze badges

Collectives™ on Stack Overflow

Fetching data from different table in while loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related