CODEWITHSUNDEEP
phpmysqlcomments
makowb
makowb

Reputation: 21

Best storage and retrieval practices for comments in PHP/MYSQL

I'm having a bit of a problem when it comes to my database and query design. Here is what I'd like to have (Honestly, a lot like stack overflow).:

The table structure is as follows:

Items
-----
iid
desc
...

Posts
-----
pid
iid
uid
date
desc
...

Comments
-----
cid
pid
uid
date
desc
...

The logic for all of this is: get all posts for item -> for each post, get all comments. Would it be best to do this all in one query? Is it best to do one query to get all posts then a separate query for each set of comments on each post?

If I do one query, then I'll potentially have a 100 row behemoth where I'll have a ton of duplicate data. If I do a separate call for each post, then I'll have far too many queries. Adivce?

Upvotes: 2

Views: 361

Answers (3)

GordyD
GordyD

Reputation: 5103

I imagine your system is going to have 3 distinctive views and I would structure it as follows:

  1. A list of items - showing the item description and the number of posts attached to that item. e.g. SELECT i.desc, ...., COUNT(p.pid) FROM items i LEFT JOIN posts p ON i.iid = p.iid GROUP BY i.iid ORDER BY i.date DESC
  2. A list of posts for a selected item - showing the posts and number of comments made for each post e.g SELECT p.desc, ...., COUNT(c.cid) FROM posts p LEFT JOIN comments c ON p.pid = c.pid WHERE p.iid = $iid GROUP BY p.pid ORDER BY p.date DESC
  3. A post page which shows the post and then comments underneath. This page would require two queries. A query to collect the post information, returning exactly 1 database row. And then a query to collect each individual comment attached to this post. E.g. SELECT p.desc, ... FROM posts p WHERE p.pid = $pid LIMIT 1 and SELECT c.desc, ... FROM comments c WHERE c.pid = $pid ORDER BY c.date DESC. You could alter the ordering here to mimic how stackOverflow orders the data by oldest, votes, etc.

I would say this is scalable model and I would always advise writing lean MySQL queries that only retrieve the information needed each displayed entity.

To mimic this page I would use the following code ...

itemRs = mysql_query("SELECT i,iid, i.desc, ... FROM items i WHERE i.iid = $iid LIMIT 1");

// all posts relating to the item
postsRs = mysql_query("SELECT p.pid, p.desc, .... FROM posts p LEFT JOIN comments c ON p.pid = c.pid WHERE p.iid = $iid ORDER BY p.date DESC");

// all comments for all posts relating to the item
commentsRs = mysql_query("SELECT c.pid, c.cid, c.desc, .... FROM comments c INNER JOIN posts p ON p.pid = c.pid INNER JOIN items i ON p.iid = i.iid WHERE p.iid = $iid ORDER BY p.date DESC");

You then would need to create a method to display this data. Perhaps encapsulate all this functionality into a DAO (Data Access Object) called Item that retrieves all this required data before calling a display() method to show the data in the format you want.

Upvotes: 2

jeni
jeni

Reputation: 440

Assigning auto id to post and comment and also item and retrieving post and comment belongs to that item id will be best, i suppose.

Upvotes: 0

Karolis
Karolis

Reputation: 9562

I would do this in two queries: one query for to get all the posts of a particular item and other query for to get all the comments of all posts of a particular item. Some ActiveRecord implementations work the same.

Upvotes: 0

Related Questions