I am working on a "collective posts" page, which is where all of your friends posts would show up. Im not talking about $_POST
but posts like status updates on facebook. My code works some what, but the only problem is that it is returning more than three results, im not sure why though. I have looked around but I have not had any luck.
The Code:
$my_id = $_SESSION['user_id'];
$frnd_query = mysql_query("SELECT user_one, user_two FROM friends WHERE user_one='$my_id' OR user_two='$my_id'");
while($run_frnd = mysql_fetch_array($frnd_query)) {
$user_one = $run_frnd['user_one'];
$user_two = $run_frnd['user_two'];
if($user_one == $my_id){
$user = $user_two;
} else {
$user = $user_one;
}
$res=mysql_query("SELECT * FROM posts WHERE user_id=$user ORDER BY timestamp DESC LIMIT 0, 3");
while($row=mysql_fetch_assoc($res)) {
?>
<div class="span4">
<h3><a href="<?php echo username_from_user_id($user); ?>"><?php echo first_name_from_user_id($user), ' ', last_name_from_user_id($user) ?></a></h3>
<h8><?php echo date('F j, Y', strtotime($row['timestamp'])) ,' at ', date('h:i A', strtotime($row['timestamp'])); ?></h8>
<p><?php echo $row['content']; ?></p>
<p><a class="btn" href="#">View Reactions »</a></p>
</div><!--/span-->
<?php
}
}//While Run_frnd
?>
I've tried multiple things, but no luck. Also, when it comes to the new PDO what should I do?
This query:
mysql_query("SELECT * FROM posts WHERE user_id=$user ORDER BY timestamp DESC LIMIT 0, 3")
Is being run within this while
loop:
while($run_frnd = mysql_fetch_array($frnd_query)) {
And so you're going to get up to 3 results per friend rather than up to 3 total.
You can get around it by doing:
$res=mysql_query("SELECT * FROM posts WHERE user_id=$user ORDER BY timestamp DESC LIMIT 0, 3");
while($row=mysql_fetch_assoc($res)) {
$i = !isset($i) ? 1 : $i + 1;
?>
<div class="span4">
<h3><a href="<?php echo username_from_user_id($user); ?>"><?php echo first_name_from_user_id($user), ' ', last_name_from_user_id($user) ?></a></h3>
<h8><?php echo date('F j, Y', strtotime($row['timestamp'])) ,' at ', date('h:i A', strtotime($row['timestamp'])); ?></h8>
<p><?php echo $row['content']; ?></p>
<p><a class="btn" href="#">View Reactions »</a></p>
</div><!--/span-->
<?php
if (3 == $i)
{
break 2;
}
}
SELECT *
FROM posts p
JOIN (SELECT user_one AS user_id
FROM friends
WHERE user_two = '$my_id'
UNION DISTINCT
SELECT user_two AS user_id
FROM friends
WHERE user_one = '$my_id') u
USING (user_id)
ORDER BY timestamp DESC
LIMIT 3
See my FIDDLE