How do I...

Mattous

Hi all I've been teaching myself Php for the past couple of days, my site has been running using phpnuke and phpbb for a few months now, and I decided I would be a bit brave and try some customisation. And it's been a success so far! I've tweaked some of my blocks in phpnuke and I'm having fun. But I have a problem...

What I want to do is display what users are in my forum on my phpnuke site, I know that I can do this because they are both running in the same db getting them both to interact isn't a problem. But the problem is with the sessions table in phpbb, I want to be able to display what users are online buy using the SELECT SUM mysql query and this isn't the problem, thats the bit I know how to do.

What I need to know is how to filter out users with more than one entry in the sessions table. i.e. having one user with 3 sessions in the table would show that there were 3 users online when there is really only 1. And I also need to know how to filter it so that it only displays sessions where the user is online. There is a filed in the table that is set to 1 when the user is online but 0 when there not.

I hope u understand what I'm saying, and I hope that you can help me, I've looked through the documentation on php.net and just got confused. Please help me 😕

deo

This will work...

For example...

$query = "SELECT DISTINCT(username) FROM session";

That query will only pull back one instance of each username regardless of how many time it appears.

Deo

Mattous

Thanks for the help!

I've got it working now, well kinda. I've got block to display when a user is online in the forum now, but it will only dispaly 1 user! so if there are 2 or three users in there it only shows up with one of them?? how do I fix this one?

Here is the code I'm using to make it work at the moment

$phpbbusers = mysql_fetch_array(mysql_query("SELECT DISTINCT session_user_id FROM $prefix1"._sessions." WHERE session_logged_in='1' AND session_time >= ".( time() - 300 ) . ""));
$phpbbusername = mysql_fetch_array(mysql_query("SELECT username FROM $prefix1"._users." WHERE user_id='$phpbbusers[session_user_id]' "));
$content .= "<center>Users in Forum<br><a href=\"http://www.its-all-g00d.co.uk/phpbb2/profile.php?mode=viewprofile&u=$phpbbusers[session_user_id]\">$phpbbusername[username]</a></center>";

Thanks in advance

Inexact

Have you tried something like:

SELECT DISTINCT(COUNT(USERS.USERID)) FROM USERS WHERE ......etc....

Mattous

Thanks! but it didn't work, I tryed this

$phpbbusers = mysql_fetch_array(mysql_query("SELECT DISTINCT (COUNT(session_user_id)) FROM $prefix1"._sessions." WHERE session_logged_in='1' AND session_time >= ".( time() - 300 ) . ""));

but it stopped the username from displaying in the block?!? am I doing something wrong? do I have to change whats in the '$content' thing? Sorry, I'm knew to all this, and easily confused by it all...

Inexact

I misunderstood what you were looking for. The COUNT(column) function only returns the number of records matching the specified conditions.

Mattous

So what do I do??

Inexact

Assuming that you are intending to list all the user names for each logged in user, the problem is that you are only displaying the first one, not iterating through the whole array.

$phpbbusername is an array, and $phpbbusername[username] is one element in the array. You need to create a control structure to loop through each one, for example:

This:
$fruit = array(apples, oranges, grapes);
foreach($fruit as $i){
echo $i;}

would give you:
apples oranges grapes

deo

You must loop through your record set... This will work...


$dblink = mysql_connect("mysqlhost", "username", "password");
mysql_select_db("whatever db your using", $dblink);

$query  = "SELECT DISTINCT session_user_id ";
$query .= "FROM $prefix1"._sessions." ";
$query .= "WHERE session_logged_in='1' ";
$query .= "AND session_time >= ".( time() - 300 );

$DBresult = mysql_query($query, $dblink);

// get each row and display it
while($row = mysql_fetch_array($dbresult))
   {
      echo $row[0];
   }

If you want to pull back more info from that SELECT statement just add it in like this...


$dblink = mysql_connect("mysqlhost", "username", "password");
mysql_select_db("whatever db your using", $dblink);

$query  = "SELECT DISTINCT session_user_id, user_name ";
$query .= "FROM $prefix1"._sessions." ";
$query .= "WHERE session_logged_in='1' ";
$query .= "AND session_time >= ".( time() - 300 );

$DBresult = mysql_query($query, $dblink);

// get each row and display it
while($row = mysql_fetch_array($dbresult))
   {
      echo $row[0]."<br";
      echo $row[1]."<br";
   }

$row[0] will be the first part of the select statement and $row[1] will be the second part for each row looped through. You can select as many data elements that are available within the table and loop through them in this manner.

Deo