do not display dublicated entries

Hello all,

I have created a simple webtracer for my website and I save some info on
mysql database.

table = tracker

FIELDS
ip
day
month
year
session_id
browser of visitor
page that was visited


What I would like to do next, is simple to create an output of that
database with the last time that every different IP has visited my website.

the best output I have at the moment is:

$query = "SELECT ip,day,month,year FROM tracker WHERE page = 'index' ORDER
BY `tracker`.`year` DESC , `tracker`.`month` DESC , `tracker`.`day` DESC
LIMIT 0,20 ";

The problem is that I want to Keep for each different IP the fisrt entry
that is found on this output I get.

therefore I would say that the output would represent the last visit of each
different user on my website.

do you know of a php or sql trick to avoid entries that containt the same IP
entries ?

Thanks in advance for the help.
Nikos.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: do not display dublicated entries

Hi

First, unless there is a compelling reason not to, I'd suggest you use
a single date field, instead of separate year, month, day columns. It
will be much more efficient on space and indexes.

Then, based on your own query, use:
SELECT DISTINCT ip, date FROM tracker WHERE page = 'index' ORDER BY
date DESC LIMIT 0,20

Niel

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: do not display dublicated entries

use the DISTINCT(IP) (the DISTINCT keyword)

bastien


>From: "septic"
>To: php-db@lists.php.net
>Subject: [PHP-DB] do not display dublicated entries
>Date: Tue, 13 Mar 2007 23:29:58 +0200
>
>Hello all,
>
>I have created a simple webtracer for my website and I save some info on
>mysql database.
>
>table = tracker
>
>FIELDS
>ip
>day
>month
>year
>session_id
>browser of visitor
>page that was visited
>
>
>What I would like to do next, is simple to create an output of that
>database with the last time that every different IP has visited my website.
>
>the best output I have at the moment is:
>
>$query = "SELECT ip,day,month,year FROM tracker WHERE page = 'index' ORDER
>BY `tracker`.`year` DESC , `tracker`.`month` DESC , `tracker`.`day` DESC
>LIMIT 0,20 ";
>
>The problem is that I want to Keep for each different IP the fisrt entry
>that is found on this output I get.
>
>therefore I would say that the output would represent the last visit of
>each
>different user on my website.
>
>do you know of a php or sql trick to avoid entries that containt the same
>IP
>entries ?
>
>Thanks in advance for the help.
>Nikos.
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
>

____________________________________________________________ _____
Have Some Fun Out Of The Sun This March Break
http://local.live.com/?mkt=en-ca/?v=2&cid=A6D6BDB4586E357F!1 42

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: do not display dublicated entries

----- Original Message -----
From: "septic"
To:
Sent: Wednesday, March 14, 2007 4:29 AM
Subject: [PHP-DB] do not display dublicated entries


> Hello all,
>
> I have created a simple webtracer for my website and I save some info on
> mysql database.
>
> table = tracker
>
> FIELDS
> ip
> day
> month
> year
> session_id
> browser of visitor
> page that was visited

how about change the fields
ip
date (contain both day,month and year)
session_id
Browser
page


> What I would like to do next, is simple to create an output of that
> database with the last time that every different IP has visited my
website.
>
> the best output I have at the moment is:
>
> $query = "SELECT ip,day,month,year FROM tracker WHERE page = 'index'
ORDER
> BY `tracker`.`year` DESC , `tracker`.`month` DESC , `tracker`.`day` DESC
> LIMIT 0,20 ";
i think when u able to change the field.. i hope you will have easy sql

select * from tracker where page='index'
order by date DESC limit 0,10

other trick:
select * from tracker where page='index'
order by id DESC


>
> The problem is that I want to Keep for each different IP the fisrt entry
> that is found on this output I get.
>
> therefore I would say that the output would represent the last visit of
each
> different user on my website.
>
> do you know of a php or sql trick to avoid entries that containt the same
IP
> entries ?
there is no trick.. i believe is a matter of programing..
to me i using mktime() to the date (not type date)
but in the end.. i should create a date than an integer.

is you decision to follow me using integer ( by mktime() ) or using date.

> Thanks in advance for the help.
btw.. i was wondering.. you enter date but why you not enter the time.


> Nikos.
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: do not display dublicated entries

Thanks for the responce..

I have tried everything except of the one that forms the date in one field
and use the DISTINCT for ip and date.

The problem with the distinct is that even if I use 'order by' the entries I
get for all IPs are not the latest ones that exist in the database.

..

""septic"" wrote in message
news:69.12.18628.6B717F54@pb1.pair.com...
> Hello all,
>
> I have created a simple webtracer for my website and I save some info on
> mysql database.
>
> table = tracker
>
> FIELDS
> ip
> day
> month
> year
> session_id
> browser of visitor
> page that was visited
>
>
> What I would like to do next, is simple to create an output of that
> database with the last time that every different IP has visited my
> website.
>
> the best output I have at the moment is:
>
> $query = "SELECT ip,day,month,year FROM tracker WHERE page = 'index'
> ORDER BY `tracker`.`year` DESC , `tracker`.`month` DESC , `tracker`.`day`
> DESC LIMIT 0,20 ";
>
> The problem is that I want to Keep for each different IP the fisrt entry
> that is found on this output I get.
>
> therefore I would say that the output would represent the last visit of
> each different user on my website.
>
> do you know of a php or sql trick to avoid entries that containt the same
> IP entries ?
>
> Thanks in advance for the help.
> Nikos.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: do not display dublicated entries

--------------090805080209030600060005
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I thought about this some more, and my last reply wouldn't work if you
wanted data besides the IP and date. A better answer is to use a
self-join like this:

select t1.* from tracker t1
left join tracker t2 on t2.date > t1.date
and t2.ip = t1.ip
where t2.date is null

That should get you what you want.

-Micah

On 03/13/2007 11:41 PM, septic wrote:
> Thanks for the responce..
>
> I have tried everything except of the one that forms the date in one field
> and use the DISTINCT for ip and date.
>
> The problem with the distinct is that even if I use 'order by' the entries I
> get for all IPs are not the latest ones that exist in the database.
>
> .
>
> ""septic"" wrote in message
> news:69.12.18628.6B717F54@pb1.pair.com...
>
>> Hello all,
>>
>> I have created a simple webtracer for my website and I save some info on
>> mysql database.
>>
>> table = tracker
>>
>> FIELDS
>> ip
>> day
>> month
>> year
>> session_id
>> browser of visitor
>> page that was visited
>>
>>
>> What I would like to do next, is simple to create an output of that
>> database with the last time that every different IP has visited my
>> website.
>>
>> the best output I have at the moment is:
>>
>> $query = "SELECT ip,day,month,year FROM tracker WHERE page = 'index'
>> ORDER BY `tracker`.`year` DESC , `tracker`.`month` DESC , `tracker`.`day`
>> DESC LIMIT 0,20 ";
>>
>> The problem is that I want to Keep for each different IP the fisrt entry
>> that is found on this output I get.
>>
>> therefore I would say that the output would represent the last visit of
>> each different user on my website.
>>
>> do you know of a php or sql trick to avoid entries that containt the same
>> IP entries ?
>>
>> Thanks in advance for the help.
>> Nikos.
>>
>
>

--------------090805080209030600060005--

Re: Re: do not display dublicated entries

Hi

> I have tried everything except of the one that forms the date in one field
> and use the DISTINCT for ip and date.

It works for me. I use it for essentially the same job, listing most
recent visit to a location (not IP, but physical venue).

Have you tried adapting it to your needs, such as:
$query = "SELECT DISTINCT ip, day, month, year FROM tracker WHERE page = 'index' ORDER
BY `tracker`.`year` DESC , `tracker`.`month` DESC , `tracker`.`day` DESC LIMIT 0, 20";


Niel

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: do not display dublicated entries

On 3/14/07, Niel Archer wrote:
> Hi
>
> > I have tried everything except of the one that forms the date in one field
> > and use the DISTINCT for ip and date.
>
> It works for me. I use it for essentially the same job, listing most
> recent visit to a location (not IP, but physical venue).
>
> Have you tried adapting it to your needs, such as:
> $query = "SELECT DISTINCT ip, day, month, year FROM tracker WHERE page = 'index' ORDER
> BY `tracker`.`year` DESC , `tracker`.`month` DESC , `tracker`.`day` DESC LIMIT 0, 20";
>
>
> Niel
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

try grouping by IP and selecting the max date value
SELECT IP,max(date) AS latestvisit FROM table GROUP BY IP

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php