Counting entries, Current active sessions, AVG?!

[shlomikalfa]

Ummz... hopefully this is the proper place for my question(s).

 

A. Counting the number of entries on more then one table in one query if possible, if not, is there any better way then running the following queries one after the other?

SELECT count(ID) as UsrNum FROM `users`;
SELECT count(ID) as LogNum FROM `loggers`;

 

 

B. Collecting current active sessions?! [how do i do that?]

 

C. Calculating a new AVG from an already existing average plus a new number. IE:

::Already existing variables:
OldAVG = 55
TotalVars = 7
::New Variable.
NewNum = 62

 

best calculation i came up with for adding the new variable to the 'OldAVG' and calculating new.

((OldAVG*TotalVars)+NewNum)/TotalVars+1

 

Well... i have more questions but i feel bad to ask them before i look it up a bit...

[move]THANKS FOR READING MY POST AND TRYING TO HELP !!![/move]

 

P.S> sorry for posting in the wrong place [math...]

[StormTheGates]

Well for your first question you could use Unions to bind the SQL query into 1.

SELECT u.count(ID) as UsrNum, l.count(ID) as LogNum FROM `users u`, `loggers l`;

 

Atleast I think that is the right syntax

[StormTheGates]

Also in regards to counting sessions there are two ways to do it.

 

One way is to get the bulk number of sessions, here is a tutorial:

 

http://www.devarticles.com/c/a/PHP/The-Quickest-Way-To-Count-Users-Online-With-PHP/

 

Another way is to have a place on your database for each user called lastactive or something. Then check through each person on the database to see when they were last active, and if it was within the last 10 mins count it up in a variable.

 

 

[shlomikalfa]

hi there, @StormTheGates

you're query was a bit mistaken however with your guidance i've managed to form that:

SELECT count( `users`.`ID` ) AS UsrNum, count( `loggers`.`ID` ) AS LogNum
FROM `users` , `loggers` ;

which works as i needed it to. THANKS!

 

[shlomikalfa]

hi, @StormTheGates

thanks again for the link, i've used their terms and found a nice UDF for the count of sessions:

function getUsersOnline() {
   $count = 0;

   $handle = opendir(session_save_path());
   if ($handle == false) return -1;

   while (($file = readdir($handle)) != false) {
       if (ereg("^sess", $file)) $count++;
   }
   closedir($handle);

   return $count;
}

THANKS!!!

_____________________________

 

Anyone as for the AVG queries?

[DarkWater]

On the first query, you can shorten it:

 

SELECT count(u.ID) AS UsrNum, count(l.ID) AS LogNum FROM users AS u , loggers AS l ;

 

 

That's what the other guy was TRYING to do.  He used the wrong syntax.

[shlomikalfa]

oky, i have the following new queries to join (:

//	Update the details if it's a new month.
$sql = 'UPDATE `servervars` SET `CurrentMonth` = '.$NowDate.', `JoinedTillMonth` = `RegisteredUsers`, `PostedTillMonth` = `DataBaseSize`, `DLedTillMonth` = `TotalDowloaded`, `JoinedThisMonth` = 0, `PostsThisMonth` = 0, `DownloadedThisMonth` = 0 WHERE CurrentMonth != '.$NowDate;
//	Check if nothing was updated [same month]
........
//	If nothing was updated, meaning we're on same month Update what's needed.
$sql = 'UPDATE `servervars` SET `JoinedThisMonth` = RegisteredUsers-JoinedTillMonth, `PostsThisMonth` = DataBaseSize-PostedTillMonth, `DownloadedThisMonth` = TotalDowloaded-DLedTillMonth WHERE CurrentMonth = '.$NowDate;';

 

How can i combine these with an IF/ELSE in MySQL ?!

 

[move]THANKS IN ADVANCE !!!![/move]

[shlomikalfa]

no one ?

[redarrow]

come on that silly.....

 

select the current date table, to see if it the correct month if not update database

or if same month as selected table update other info...........