471,354 Members | 2,129 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,354 software developers and data experts.

Select Distinct Statement Help

I have a question about the following PHP script - I got it off a web
site tutorial on how to count users logged into your site - my
question is the $PHP_SELF variable - it writes the name of the web
page to the 'file' field in the table - I don't understand why it is
doing that - I mean, isn't the SELECT DISTINCT statement only pulling
those records from that one web page? I guess I just don't follow what
it is doing with that SELECT DISTINCT statement???

$timeoutseconds = 300;
$timestamp = time();
$timeout = $timestamp-$timeoutseconds;

$php_SQL = "INSERT INTO online SET timestamp='$timestamp',
ip='$REMOTE_ADDR', file='$PHP_SELF'";
$php_resultID = mysql_query($php_SQL, $php_linkID);

$php_SQL = "DELETE FROM online WHERE timestamp < $timeout";
$php_resultID = mysql_query($php_SQL, $php_linkID);

$php_SQL = "SELECT DISTINCT ip FROM online WHERE file = '$PHP_SELF'";
$php_resultID = mysql_query($php_SQL, $php_linkID);
$php_users_online = mysql_num_rows($php_resultID);

if ($php_users_online == 1)
{
print "<BR>$php_users_online user is currently online";
}
else
{
print "<BR>$php_users_online users are currently online";
}

Jul 16 '05 #1
5 6114

On 18-Aug-2003, Ralph Freshour <ra***@primemail.com> wrote:
I have a question about the following PHP script - I got it off a web
site tutorial on how to count users logged into your site - my
question is the $PHP_SELF variable - it writes the name of the web
page to the 'file' field in the table - I don't understand why it is
doing that - I mean, isn't the SELECT DISTINCT statement only pulling
those records from that one web page? I guess I just don't follow what
it is doing with that SELECT DISTINCT statement???
(snip) $php_SQL = "INSERT INTO online SET timestamp='$timestamp',
ip='$REMOTE_ADDR', file='$PHP_SELF'";
$php_resultID = mysql_query($php_SQL, $php_linkID);

$php_SQL = "DELETE FROM online WHERE timestamp < $timeout";
$php_resultID = mysql_query($php_SQL, $php_linkID); $php_SQL = "SELECT DISTINCT ip FROM online WHERE file = '$PHP_SELF'";
$php_resultID = mysql_query($php_SQL, $php_linkID);
$php_users_online = mysql_num_rows($php_resultID);

(snip)

The script is using $PHP_SELF so that it will work on multiple pages using
the same table. The 'DISTINCT ip' phrase means that SQL will reduce multiple
rows containing the same id to one row. In this case, that means that each
user will only be counted once even though they might have visited the page
many times.

The script inserts the ip address, timestamp, and page ($PHP_SELF) into the
table. It then deletes from the table any rows older than the timeout value.
Next it retrieves the rows for this page from the table while ensuring that
each ip is only represented by one row. Finally, it gets a count of the
number of rows retrieved which is the number of users currently 'online.'


--
Tom Thackrey
www.creative-light.com
Jul 16 '05 #2
Ralph Freshour wrote:
I mean, isn't the SELECT DISTINCT statement only pulling
those records from that one web page? I guess I just don't follow what
it is doing with that SELECT DISTINCT statement???


The WHERE clause makes sure that only records for the current page are
pulled, the "DISTINCT ip" makes sure that each IP adress recorded for
that page is retrieved only once, even if multiple records for that IP
and page exist.

Jochen

--
/**
* @author Jochen Buennagel <zang at buennagel dot com>
* @see http://www.sourceforge.net/projects/zang
*/

Jul 16 '05 #3
That's what I'm referring to in my question, woudn't one want to
'pull' the records for *any* of the web site pages vs just that one
page? Isn't the count incorrect in that it's just count for that one
web page instead of the whole site? I'm thinking to get an accurate
count of who's visiting the site (and not just the specific page) I
need to drop the WHERE clause???
On Wed, 20 Aug 2003 09:27:53 +0200, Jochen Buennagel
<za**@buennagel.com> wrote:
Ralph Freshour wrote:
I mean, isn't the SELECT DISTINCT statement only pulling
those records from that one web page? I guess I just don't follow what
it is doing with that SELECT DISTINCT statement???


The WHERE clause makes sure that only records for the current page are
pulled, the "DISTINCT ip" makes sure that each IP adress recorded for
that page is retrieved only once, even if multiple records for that IP
and page exist.

Jochen


Jul 16 '05 #4
On Tue, 19 Aug 2003 13:55:29 GMT, Ralph Freshour <ra***@primemail.com>
wrote:
On Wed, 20 Aug 2003 09:27:53 +0200, Jochen Buennagel
<za**@buennagel.com> wrote:
The WHERE clause makes sure that only records for the current page are
pulled, the "DISTINCT ip" makes sure that each IP adress recorded for
that page is retrieved only once, even if multiple records for that IP
and page exist.

Jochen
That's what I'm referring to in my question, woudn't one want to
'pull' the records for *any* of the web site pages vs just that one
page? Isn't the count incorrect in that it's just count for that one
web page instead of the whole site? I'm thinking to get an accurate
count of who's visiting the site (and not just the specific page) I
need to drop the WHERE clause???


Sadly, you can't really get an accurate count of people visiting the
site by using IP address. At work, we use a proxy, so we'd all appear
to have the same IP address. At home I dial up to my ISP and could
have a different IP address each time i visit your site.

--
David (please modify address to david@ before replying!)
Jul 16 '05 #5
I see that - then what I will have to do is make use of their login
name - that's unique - and I'll drop the IP address - so then I'll
have an accurate count of users who are logged in which is what I'm
after.

Thanks everyone for the help...!!!
On Tue, 19 Aug 2003 16:10:03 +0100, David Mackenzie
<dc*@tarbrax.freeserve.co.uk> wrote:
On Tue, 19 Aug 2003 13:55:29 GMT, Ralph Freshour <ra***@primemail.com>
wrote:
On Wed, 20 Aug 2003 09:27:53 +0200, Jochen Buennagel
<za**@buennagel.com> wrote:
The WHERE clause makes sure that only records for the current page are
pulled, the "DISTINCT ip" makes sure that each IP adress recorded for
that page is retrieved only once, even if multiple records for that IP
and page exist.

Jochen

That's what I'm referring to in my question, woudn't one want to
'pull' the records for *any* of the web site pages vs just that one
page? Isn't the count incorrect in that it's just count for that one
web page instead of the whole site? I'm thinking to get an accurate
count of who's visiting the site (and not just the specific page) I
need to drop the WHERE clause???


Sadly, you can't really get an accurate count of people visiting the
site by using IP address. At work, we use a proxy, so we'd all appear
to have the same IP address. At home I dial up to my ISP and could
have a different IP address each time i visit your site.


Jul 16 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

14 posts views Thread by Craig Hoskin | last post: by
3 posts views Thread by Tcs | last post: by
9 posts views Thread by Kelvin | last post: by
2 posts views Thread by ray well | last post: by
8 posts views Thread by dig314 | last post: by
5 posts views Thread by Daniel Wetzler | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.