Correct way to query records

[jgd12345]

Hi I was wonder what is the correct way of selecting the number of comments from a post and selecting who made the original post (this is for a news script):

news_posts structure
- id
- uid

$query = mysql_query("SELECT * FROM news_posts ORDER BY dateline DESC");

while($post = mysql_fetch_array($query)) {
// Selecting the member username
$memberquery = mysql_query("SELECT username FROM news_members WHERE id='$post[uid]'");
$username = mysql_result($memberquery, 0);

// Selecting the number of comments
$commentquery = mysql_query("SELECT COUNT(*) FROM news_comments WHERE pid='$post[id]'");
$comments = mysql_result($commentquery, 0);

...
}

or

news_posts structure:
- id
- username // This is the username not the uid
- comments // This is a counter for number of comments and is updated when a new post is made

$query = mysql_query("SELECT * FROM news_posts ORDER BY dateline DESC");

while($post = mysql_fetch_array($query)) {
// Selecting the member username
$username = post['username'];

// Selecting the number of comments
$comments = $post['comments'];

...
}

The second one reduces alot of queries if you're selecting alot of posts but also adds somewhat un needed fields to the table. If you could help I'd be very greatful. I'd also appreciate any information why one would be better than the other. Thanks

 

[bastienk]

Use an INNER JOIN to join the three tables together...something along the lines of: (not tested)

$sql= "select n.*, m.username, c.comments from
       news_posts n,
       news_members m,
       news_comments c
       where
       n.uid = c.id and
       n.uid = m.id 
       order by dateline desc";

 

[jgd12345]

So what exactly does that query do. The comments bit should produce a number where the n.id=c.nid (number of comments where the nid in the comments table is equal to the id in the posts table). But that looks like it is selecting the field comments which does not exist.

The member bit looks more promising but how would I print the value of the member username inside the loop.

Thirdly how would I change n.* to select specific records, ie would it be like n.id, n.subject, ...,

Cheers for your help already.

 

[bastienk]

The query taies all three table together on the ids (uid for the new_posts table and id for each of the other two tables.

It takes all the fields from the new_posts table and the username and comments from the other two table respectively.

Should you wish to limit the columns from new_posts table, replace the n.* with n.id, n.subject...

the query will need to be played with to match the current structure and design of you db as well as the requirement for the script. ie an innerjoin will not necessairly take all records from the tables as it only takes them where the fields are equal...