CODEWITHSUNDEEP
phpmysql
Casper Wilkes
Casper Wilkes

Reputation: 919

fetching multiple usernames from a database

I have a comments section on a website i'd like to streamline a bit if possible so it's not as much of an impact on the database. When a user selects a post, and if it has comments associated with it, it lists the comments. when the comments list, it fetches the username from another table. I store the id for the user in the comments table, and use that id to select the record from the users table. and displays as "user" said:

lets say i have 1000 comments on a post, it will hit the users table 1000 times to grab user names. I think this is probably a bad design. i thought of a few solutions, but don't know what would be recommended in this situation.

should i just be storing the username inside the comments table?

should i store all of the usernames already called in a session array?

put all of the usernames in a file, and call from the file?

or is there another solution that i haven't thought of?

i'm kind of confused. I thought i was doing the right thing by using the IDs in the comment table, and then using it to fetch the username, but after reading about a million posts on using less impact on the database, i'm starting to question myself.

WOW, thanks for all of the useful answers. here is the table scheme, i don't know why i didn't put in in originally.

comments table for jokes:

id | author_id | joke_id | date_created | body
---+-----------+---------+--------------+-----
1  |     3     |    2    | 2011-06-12   | this is a comment

and for the users:

id | user_name | password | email | date_joined | visible
---+-----------+----------+-------+-------------+---------
 3 |   booboo  | password | email | todays_date |    1

Upvotes: 2

Views: 536

Answers (4)

Amber
Amber

Reputation: 526573

This is what JOINs are for - so that you can run a single query and efficiently get the combined information from multiple tables. E.g.:

SELECT comments.id, comments.content, users.name
FROM comments
JOIN users ON comments.user = users.id
WHERE comments.id in (1,2,3)

would look up the 3 comments with id 1, 2, and 3, plus also get the username of each commenter, and return rows that looked like this:

comments.id | comments.content  | users.name
------------+-------------------+---------
1           | "First comment."  | "Poster1"
2           | "Second comment." | "Poster2"
3           | "Third comment."  | "Poster3"

Upvotes: 3

Sietse
Sietse

Reputation: 717

SELECT * FROM comments LEFT JOIN users ON comments.posterid = users.id

Google for LEFT JOIN for more info :)

Upvotes: 0

Jess
Jess

Reputation: 8700

It sounds like you have a userID field in your comments table, but need to look up the username, correct? If so, a JOIN would be the best solution.

Something like:

SELECT *
FROM `comments`
LEFT JOIN `users` ON `users`.`id` = `comments`.`userid`
WHERE `postid`='1'

To read more on joins and their endless possibilities, read up here

Upvotes: 3

Tarek Fadel
Tarek Fadel

Reputation: 1959

Do you allow the same username to be used more than once? If not, then I would use the username field as the PK of your users table and store that in the commentstable as the FK. That'll solve your issue nicely.

If changing the PK of your users table is too much of an issue, then just store the username in the comments section since you can still use that select a single record from your users table.

Upvotes: -1

Related Questions