Select Distinct Statement Help

[Ralph Freshour]

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='$tim estamp',
ip='$REMOTE_ADD R', file='$PHP_SELF '";
$php_resultID = mysql_query($ph p_SQL, $php_linkID);

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

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

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

[Tom Thackrey]

Re: Select Distinct Statement Help


On 18-Aug-2003, Ralph Freshour <ralph@primemai l.com> wrote:
[color=blue]
> 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???
>[/color]
(snip)[color=blue]
> $php_SQL = "INSERT INTO online SET timestamp='$tim estamp',
> ip='$REMOTE_ADD R', file='$PHP_SELF '";
> $php_resultID = mysql_query($ph p_SQL, $php_linkID);
>
> $php_SQL = "DELETE FROM online WHERE timestamp < $timeout";
> $php_resultID = mysql_query($ph p_SQL, $php_linkID);[/color]
[color=blue]
> $php_SQL = "SELECT DISTINCT ip FROM online WHERE file = '$PHP_SELF'";
> $php_resultID = mysql_query($ph p_SQL, $php_linkID);
> $php_users_onli ne = mysql_num_rows( $php_resultID);[/color]
(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

https://www.creative-light.com

[Jochen Buennagel]

Re: Select Distinct Statement Help

Ralph Freshour wrote:
[color=blue]
> 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???[/color]

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
*/

[Ralph Freshour]

Re: Select Distinct Statement Help

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
<zang@buennagel .com> wrote:
[color=blue]
>Ralph Freshour wrote:
>[color=green]
>> 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???[/color]
>
>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[/color]

[David Mackenzie]

Re: Select Distinct Statement Help

On Tue, 19 Aug 2003 13:55:29 GMT, Ralph Freshour <ralph@primemai l.com>
wrote:
[color=blue]
>On Wed, 20 Aug 2003 09:27:53 +0200, Jochen Buennagel
><zang@buennage l.com> wrote:
>[color=green]
>>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[/color][/color]
[color=blue]
>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???[/color]

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!)

[Ralph Freshour]

Re: Select Distinct Statement Help

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
<dcm@tarbrax.fr eeserve.co.uk> wrote:
[color=blue]
>On Tue, 19 Aug 2003 13:55:29 GMT, Ralph Freshour <ralph@primemai l.com>
>wrote:
>[color=green]
>>On Wed, 20 Aug 2003 09:27:53 +0200, Jochen Buennagel
>><zang@buennag el.com> wrote:
>>[color=darkred]
>>>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[/color][/color]
>[color=green]
>>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???[/color]
>
>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.[/color]