[SOLVED] More Simple Looping

[Pawn]

I have a lot of scenario's like this, so I want to be sure I'm doing it in an efficient way. Here's the display portion of a simple "wall", or comment interface:

 

$carrot = "SELECT * FROM wall_posts WHERE parent_id='$_GET[id]' ORDER BY post_id LIMIT 30";
$rabbit = mysql_query($carrot)
if(!$rabbit) {
    echo "Oops, bad rabbit ($carrot)! " . mysql_error(); exit;
}
if(mysql_num_rows($rabbit)==0) {
echo "Wall is empty.";
} else {
while($hat = mysql_fetch_assoc($rabbit)) {
	#ANOTHER BRICK IN THE WALL
	echo "post_id: ".$hat['post_id']." poster_id: ".$hat['poster_id']." posted: ".$hat['posted']." body: ".$hat['body'];
	echo "<br />";
}
}

But wait, what are the names of these literary giants!? Now I have to add:

 

#ANOTHER BRICK IN THE WALL
$current_poster = mysql_fetch_assoc(mysql_query("SELECT username, avatar, etc FROM users WHERE user_id='$hat[poster_id]'"));

Basically if you can see any improvement on any of that I'd love to hear it.

[Pawn]

I think I'm being unclear. Is it necessary to use that many individual queries to get the data I need?

[soak]

You need a SQL join.

 

<?php
$carrot = "SELECT *.wp, u.username, u.avatar FROM wall_posts AS wp JOIN users AS u ON u.id = wp.poster_id WHERE parent_id=". (int) $_GET['id'] ." ORDER BY post_id LIMIT 30";
$rabbit = mysql_query($carrot)
if(!$rabbit) {
    echo "Oops, bad rabbit ($carrot)! " . mysql_error(); exit;
}
if(mysql_num_rows($rabbit)==0) {
    echo "Wall is empty.";
} else {
    while($hat = mysql_fetch_assoc($rabbit)) {
        #ANOTHER BRICK IN THE WALL
        echo "post_id: ".$hat['post_id']." poster_id: ".$hat['poster_id']." posted: ".$hat['posted']." body: ".$hat['body'];
        echo "<br />";
    }
}

 

Note that I cast $_GET['id'] as an int to protect against SQL injection. Please also give your variables sensible names. It's all very cute for you but it's gonna be no fun for anyone else having to debug your page.

[Pawn]

Sorry, I was just revising some simple texts and CV's metaphor infected my code.

 

Thanks for the much preferable query. Could I incorporate COUNT to generate a "total posts" for each user? I don't know how to phrase it, given that it should ignore the WHERE parent_id= criteria. Something like:

 

$sql = "SELECT wp.*, u.username, u.country,
(SELECT COUNT(*) FROM wall_posts WHERE poster_id='u.user_id') as total_posts
FROM wall_posts AS wp JOIN core_users AS u ON u.user_id = wp.poster_id WHERE parent_id=". (int) $_GET['id'] ." ORDER BY post_id LIMIT 30"

Except that, you know, made sense/worked.

[soak]

SELECT *.wp, u.username, u.avatar, (SELECT COUNT(*) FROM wall_posts AS wpc WHERE wpc.poster_id = u.id) AS postcount FROM wall_posts AS wp JOIN users AS u ON u.id = wp.poster_id WHERE parent_id=". (int) $_GET['id'] ." ORDER BY post_id LIMIT 30

 

Should do the trick I think

[Pawn]

That works great, thanks!