Re: [PHP-DB] Re: do not display dublicated entries
On 3/14/07, Niel Archer <[EMAIL PROTECTED]> 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
Re: [PHP-DB] 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: [PHP-DB] Re: do not display dublicated entries
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"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] 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-DB] 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"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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