Need Help with Logic and Query

[gaza165]

Hi all... i need help with the logic here.

 

I am pulling back comments from my database

 

 

* Comments Table *

comment_id
blog_id
name
email
comments
date_posted
user

 

i then have a users table

 

* Users Table *

username
password
email
last_activity

 

i use the last_activty field to update the users activity via timestamp....

 

this is so i can check what users are online and what are not....

 

My question is

 

How do i echo the comments and have their status next to it

 

e.g.

 

[ONLINE] Garry: this is a comment

[OFFLINE] Malcolm: this is another comment

 

this is what i have so far

 

<?php


include("dbconnect/dbconnect.php");

$sql = mysql_query("
SELECT * FROM blog_comments
");

$query = mysql_query("
SELECT username FROM users WHERE TIMESTAMPDIFF(MINUTE,last_activity, CURRENT_TIMESTAMP()) < 5"
);


while($row = mysql_fetch_array($sql)) {

			while($online = mysql_fetch_array($query)) {

			if(mysql_num_rows($query) == 0)

			{	
				echo "offline";		
			} else {

				if($online['username'] == $row['user']) {

					echo "online";
				}
			}

			}

	$comments = substr($row['comments'],0,25);
	$name = $row['name'];


	echo "<li><h4>".$name.": </h4>".$comments."</li>";

}


?>

[Orio]

Your logic is a bit wrong here. Instead of pulling all of the comments and all of the online users, you should check for each comment if the user is currently online:

 

<?php

include("dbconnect/dbconnect.php");

$sql = mysql_query("
SELECT * FROM blog_comments
");


while($row = mysql_fetch_array($sql))
{

$name = $row['user'];
$is_online_result = mysql_query("SELECT username FROM users WHERE username = '{$username}' AND TIMESTAMPDIFF(MINUTE,last_activity, CURRENT_TIMESTAMP()) < 5")
if(mysql_num_rows($is_online_result) == 0) //Is there a user with the name $username that was active in the last 5 minutes?
{	
	echo "offline";
} else {
	echo "online";
}

$comments = substr($row['comments'],0,25);

echo "<li><h4>".$name.": </h4>".$comments."</li>";
}

?>

 

Give it a try, tell me if it works.

 

Orio.

[gaza165]

No luck Orio

 

when i log on...

 

it says that everyone is online regardless of who is logged in or out....

 

thanks

 

Garry

[gaza165]

oh i see where you have gone wrong

 

$name = $row['user']  needed to be  $username = $row['user'];

 

Nice one thanks mate....

[Barand]

This should do it with a single query

 

SELECT bc.*, IF(u.username IS NULL, 'OFFLINE', 'ONLINE') as status

FROM blog_comments bc

LEFT JOIN users u

ON bc.user = u.username

AND TIMESTAMPDIFF(MINUTE,u.last_activity, CURRENT_TIMESTAMP()) < 5