RE: [PHP] Last visitors
On Tuesday, April 19, 2005 18:46, Ryan A wrote: Hey, Thanks for replying, I tried using the test example of Petar Nedyalkov, but when i try to create the following: CREATE TABLE `profile_log` ( `profile_id` int(10) default NULL, `user_id` int(10) default NULL, `last_login` timestamp NOT NULL ) ENGINE=MyISAM CHARSET=utf8 I get an error on both ENGINE=MyISAM and CHARSET=utf8 What are they for really? can I omit them? or will that effect the program later on? Thanks, Ryan You can leave these off. Changing ENGINE to TYPE will fix that error, and you can leave off the charset. I believe utf8 was added in 4.1. If you leave off charset=xxx it will just default to whatever the server's default-character-set is set to, probably latin1. On 4/19/2005 4:42:08 PM, Tom Crimmins ([EMAIL PROTECTED]) wrote: On Tuesday, April 19, 2005 09:09, John Nichel wrote: Petar Nedyalkov wrote: snip You can store only 10 records for each user by using the following logic: mysql show create table profile_log\G *** 1. row *** Table: profile_log Create Table: CREATE TABLE `profile_log` ( `profile_id` int(10) default NULL, `user_id` int(10) default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) When you create a profile you fill 10 blank records with timestamp -00-00 00:00:00. Then if a user sees a profile: PSEUDO SQL: $SQL = UPDATE profile_log SET user_id=.$userId. WHERE profile_id=. $profileId. ORDER BY timestamp ASC LIMIT 1 -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Last visitors
clip 1 Thanks for replying, I tried using the test example of Petar Nedyalkov, but when i try to create the following: CREATE TABLE `profile_log` ( `profile_id` int(10) default NULL, `user_id` int(10) default NULL, `last_login` timestamp NOT NULL ) ENGINE=MyISAM CHARSET=utf8 I get an error on both ENGINE=MyISAM and CHARSET=utf8 What are they for really? can I omit them? or will that effect the program later on? /clip 1 You can leave these off. Changing ENGINE to TYPE will fix that error, and you can leave off the charset. I believe utf8 was added in 4.1. If you leave off charset=xxx it will just default to whatever the server's default-character-set is set to, probably latin1. Hey, Thanks for replying. Theres something wrong coz its not working as expected... I followed instructions and created a table like this: CREATE TABLE test_last_visitors ( profile_id int(10) default NULL, user_id int(10) default NULL, ttimestamp timestamp(14) NOT NULL) TYPE=MyISAM; then I ran this 5 times: (logic being: test with 5, if it works go with 10 or more) :-) insert into `test_last_visitors` values(1,1,now()); then from my php script (test_last_visitors.php) I ran this test SQL: $SQL = UPDATE test_last_visitors SET profile_id=.$profile_id., user_id=user_id+1, ttimestamp=now() WHERE profile_id=1 ORDER BY ttimestamp ASC LIMIT 1; The first 5 times it works perfectly, then after that it updates only the latest record over and over again :-( I tried playing with the ASC and DESC, MIN and MAXno joy. Any idea whats wrong? Thanks, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Last visitors
On Wednesday, April 20, 2005 09:57, Ryan A wrote: clip 1 Thanks for replying, I tried using the test example of Petar Nedyalkov, but when i try to create the following: CREATE TABLE `profile_log` ( `profile_id` int(10) default NULL, `user_id` int(10) default NULL, `last_login` timestamp NOT NULL ) ENGINE=MyISAM CHARSET=utf8 I get an error on both ENGINE=MyISAM and CHARSET=utf8 What are they for really? can I omit them? or will that effect the program later on? /clip 1 You can leave these off. Changing ENGINE to TYPE will fix that error, and you can leave off the charset. I believe utf8 was added in 4.1. If you leave off charset=xxx it will just default to whatever the server's default-character-set is set to, probably latin1. Hey, Thanks for replying. Theres something wrong coz its not working as expected... I followed instructions and created a table like this: CREATE TABLE test_last_visitors ( profile_id int(10) default NULL, user_id int(10) default NULL, ttimestamp timestamp(14) NOT NULL) TYPE=MyISAM; then I ran this 5 times: (logic being: test with 5, if it works go with 10 or more) :-) insert into `test_last_visitors` values(1,1,now()); then from my php script (test_last_visitors.php) I ran this test SQL: $SQL = UPDATE test_last_visitors SET profile_id=.$profile_id., user_id=user_id+1, ttimestamp=now() WHERE profile_id=1 ORDER BY ttimestamp ASC LIMIT 1; The first 5 times it works perfectly, then after that it updates only the latest record over and over again :-( I tried playing with the ASC and DESC, MIN and MAXno joy. Any idea whats wrong? Thanks, Ryan The following works for me. UPDATE test_last_visitors SET user_id=user_id+1 WHERE profile_id=1 ORDER BY ttimestamp LIMIT 1; You don't have to explicitly update the timestamp column. It will do this automatically. This will rotate through the five rows. This may have failed in your test because if you executed this in a script and all of the sql statements occurred in the same second, you would probably see the behavior that you described. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Last visitors
Theres something wrong coz its not working as expected... I followed instructions and created a table like this: CREATE TABLE test_last_visitors ( profile_id int(10) default NULL, user_id int(10) default NULL, ttimestamp timestamp(14) NOT NULL) TYPE=MyISAM; then I ran this 5 times: (logic being: test with 5, if it works go with 10 or more) :-) insert into `test_last_visitors` values(1,1,now()); then from my php script (test_last_visitors.php) I ran this test SQL: $SQL = UPDATE test_last_visitors SET profile_id=.$profile_id., user_id=user_id+1, ttimestamp=now() WHERE profile_id=1 ORDER BY ttimestamp ASC LIMIT 1; The first 5 times it works perfectly, then after that it updates only the latest record over and over again :-( I tried playing with the ASC and DESC, MIN and MAXno joy. Any idea whats wrong? /* The following works for me. UPDATE test_last_visitors SET user_id=user_id+1 WHERE profile_id=1 ORDER BY ttimestamp LIMIT 1; You don't have to explicitly update the timestamp column. It will do this automatically. This will rotate through the five rows. This may have failed in your test because if you executed this in a script and all of the sql statements occurred in the same second, you would probably see the behavior that you described. */ Keep running the same UPDATE query a few times and see that it only updates the top record, it leaves all the others even though the others timestamps are lower... atleast thats whats happening to me :-( Thanks, -Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors
Ryan A wrote: snip then from my php script (test_last_visitors.php) I ran this test SQL: $SQL = UPDATE test_last_visitors SET profile_id=.$profile_id., user_id=user_id+1, ttimestamp=now() WHERE profile_id=1 ORDER BY ttimestamp ASC LIMIT 1; Why are you setting the profile_id equal to a value when you also have that in your WHERE clause? I may have missed something here, but I though the purpose of this was to track the last ten visitors to a certain page, and if this is the case, why increment the user_id when updating the row? Shouldn't the query be more like... UPDATE `test_last_visitors` SET `user_id`=$user_id, `ttimestamp`=now() WHERE `profile_id`=$profile_id ORDER BY `ttimestamp` ASC LIMIT 1 -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors
Hey, Why are you setting the profile_id equal to a value when you also have that in your WHERE clause? I may have missed something here, but I though the purpose of this was to track the last ten visitors to a certain page, and if this is the case, why increment the user_id when updating the row? Shouldn't the query be more like... I was just playing with the SQLinstead of entering new user_id's for each person visiting i just tried increment it... UPDATE `test_last_visitors` SET `user_id`=$user_id, `ttimestamp`=now() WHERE `profile_id`=$profile_id ORDER BY `ttimestamp` ASC LIMIT 1 I ran the above in phpmyadmin like so: UPDATE `test_last_visitors` SET `user_id`=7, `ttimestamp`=now() WHERE `profile_id`=2 ORDER BY `ttimestamp` ASC LIMIT 1 its only updateing the top first record :-( I'm just about to throw in the towel... Thanks, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Last visitors
Hey, Am a bit puzzled as to how to do this, I am modifying a profiles/dating site, the site works like this: if you are a guest you can see only limited details of a profile, if you have logged in, you see many more details. On the profile page there should be last 10 visitors, it will not register the guests, but if someone had logged in and visited your profile, it should show their usernameif there are 10 enteries in the db and when the 11th person comes...then the first person who came should be bumped off and the 11th person takes his place and so on I hope i have explained it well, I dont think many of you even needed that long explanation as you might have already seen this on many sites and knew what i was talking about by the second line, but better more infomation than less right? Any ideas, links,code or classes on how i can implement this? Thanks, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors
On 4/19/05, Ryan A [EMAIL PROTECTED] wrote: Hey, Am a bit puzzled as to how to do this, I am modifying a profiles/dating site, the site works like this: if you are a guest you can see only limited details of a profile, if you have logged in, you see many more details. On the profile page there should be last 10 visitors, it will not register the guests, but if someone had logged in and visited your profile, it should show their usernameif there are 10 enteries in the db and when the 11th person comes...then the first person who came should be bumped off and the 11th person takes his place and so on I hope i have explained it well, I dont think many of you even needed that long explanation as you might have already seen this on many sites and knew what i was talking about by the second line, but better more infomation than less right? Any ideas, links,code or classes on how i can implement this? Thanks, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Instead of having a seperate table to see the latest people logged on, isn't it better to query an existing user table based on the last login time? SELECT username from users ORDER BY login_time DESC LIMIT 10? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors
clip the site works like this: if you are a guest you can see only limited details of a profile, if you have logged in, you see many more details. On the profile page there should be last 10 visitors, it will not register the guests, but if someone had logged in and visited your profile, it should show their usernameif there are 10 enteries in the db and when the 11th person comes...then the first person who came should be bumped off and the 11th person takes his place and so on /clip Instead of having a seperate table to see the latest people logged on, isn't it better to query an existing user table based on the last login time? SELECT username from users ORDER BY login_time DESC LIMIT 10? I am not checking for the last people logged in, I want to see the last people who have viewed the profile... each profile will have its own last 10 people visited Thanks, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors
On Tuesday 19 April 2005 15:57, Ryan A wrote: Hey, Am a bit puzzled as to how to do this, I am modifying a profiles/dating site, the site works like this: if you are a guest you can see only limited details of a profile, if you have logged in, you see many more details. On the profile page there should be last 10 visitors, it will not register the guests, but if someone had logged in and visited your profile, it should show their usernameif there are 10 enteries in the db and when the 11th person comes...then the first person who came should be bumped off and the 11th person takes his place and so on I hope i have explained it well, I dont think many of you even needed that long explanation as you might have already seen this on many sites and knew what i was talking about by the second line, but better more infomation than less right? Just store the login time of each identified user and you can filter/sort by it. Any ideas, links,code or classes on how i can implement this? Thanks, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005 -- Cyberly yours, Petar Nedyalkov Devoted Orbitel Fan :-) PGP ID: 7AE45436 PGP Public Key: http://bu.orbitel.bg/pgp/bu.asc PGP Fingerprint: 7923 8D52 B145 02E8 6F63 8BDA 2D3F 7C0B 7AE4 5436 pgpVS4cLnOE2t.pgp Description: PGP signature
[PHP] Last visitors
On 4/19/05, Ryan A [EMAIL PROTECTED] wrote: clip the site works like this: if you are a guest you can see only limited details of a profile, if you have logged in, you see many more details. On the profile page there should be last 10 visitors, it will not register the guests, but if someone had logged in and visited your profile, it should show their usernameif there are 10 enteries in the db and when the 11th person comes...then the first person who came should be bumped off and the 11th person takes his place and so on /clip Instead of having a seperate table to see the latest people logged on, isn't it better to query an existing user table based on the last login time? SELECT username from users ORDER BY login_time DESC LIMIT 10? I am not checking for the last people logged in, I want to see the last people who have viewed the profile... each profile will have its own last 10 people visited Thanks, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005 ah! i see. sorry for misunderstanding you. perhaps on the script for the profiles page, the logic should be like: profile for user: adam if the browsing user is logged in: (for example the browsing user is ben) insert into the database that ben has seen adam's profile end if and your table in the database can be: user visitor time_of_visit adam ben 1290122141 (unix timestamp) then you can query it on the database for each user. SELECT * FROM profile_visits WHERE user='adam' ORDER by time_of_visit DESC LIMIT 10 As for cleaning up... that's the bit that I can't figure out myself :(. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors
I am not checking for the last people logged in, I want to see the last people who have viewed the profile... each profile will have its own last 10 people visited /* ah! i see. sorry for misunderstanding you. perhaps on the script for the profiles page, the logic should be like: profile for user: adam if the browsing user is logged in: (for example the browsing user is ben) insert into the database that ben has seen adam's profile end if and your table in the database can be: user visitor time_of_visit adam ben 1290122141 (unix timestamp) then you can query it on the database for each user. SELECT * FROM profile_visits WHERE user='adam' ORDER by time_of_visit DESC LIMIT 10 As for cleaning up... that's the bit that I can't figure out myself :(. */ Hey, Thanks for replying. ah! i see. sorry for misunderstanding you. No problem, I guess i didnt explain it well enough, and you dont have to help me but you are trying, so thank you. As for cleaning up... that's the bit that I can't figure out myself :(. exactly, I came to the same part as youbut then i would have a LOT of wasted records without the cleanup... just cant figure it out. Thanks, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
FW: [PHP] Last visitors
Couple of options: a CRON job/scheduled job which invokes a SQL command deleting all but the 10 most recent records, or do the same every time you insert a record. As to exactly how you decide which are the most recent 10 records to do this deletion, that is a little more complex. Something like DELETE FROM profile_visits WHERE user='billy' AND time_of_visit NOT IN ( Select time_of_visit FROM profile_visits WHERE user='billy' ORDER by time_of_visit DESC LIMIT 10 ) Should give you an idea. Mark -Original Message- From: Ryan A [mailto:[EMAIL PROTECTED] Sent: 19 April 2005 14:27 To: [EMAIL PROTECTED] Cc: php Subject: Re: [PHP] Last visitors I am not checking for the last people logged in, I want to see the last people who have viewed the profile... each profile will have its own last 10 people visited /* ah! i see. sorry for misunderstanding you. perhaps on the script for the profiles page, the logic should be like: profile for user: adam if the browsing user is logged in: (for example the browsing user is ben) insert into the database that ben has seen adam's profile end if and your table in the database can be: user visitor time_of_visit adam ben 1290122141 (unix timestamp) then you can query it on the database for each user. SELECT * FROM profile_visits WHERE user='adam' ORDER by time_of_visit DESC LIMIT 10 As for cleaning up... that's the bit that I can't figure out myself :(. */ Hey, Thanks for replying. ah! i see. sorry for misunderstanding you. No problem, I guess i didnt explain it well enough, and you dont have to help me but you are trying, so thank you. As for cleaning up... that's the bit that I can't figure out myself :(. exactly, I came to the same part as youbut then i would have a LOT of wasted records without the cleanup... just cant figure it out. Thanks, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Gamma Global : Suppliers of HPCompaq, IBM, Acer, EPI, APC, Cyclades, D-Link, Cisco, Sun Microsystems, 3Com GAMMA GLOBAL (UK) LTD IS A RECOGNISED 'INVESTOR IN PEOPLE' AND AN 'ISO 9001 2000' REGISTERED COMPANY ** CONFIDENTIALITY NOTICE: This Email is confidential and may also be privileged. If you are not the intended recipient, please notify the sender IMMEDIATELY; you should not copy the email or use it for any purpose or disclose its contents to any other person. GENERAL STATEMENT: Any statements made, or intentions expressed in this communication may not necessarily reflect the view of Gamma Global (UK) Ltd. Be advised that no content herein may be held binding upon Gamma Global (UK) Ltd or any associated company unless confirmed by the issuance of a formal contractual document or Purchase Order, subject to our Terms and Conditions available from http://www.gammaglobal.com EOE ** ** -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors
On Tuesday 19 April 2005 14:27, Ryan A typed: SELECT * FROM profile_visits WHERE user='adam' ORDER by time_of_visit DESC LIMIT 10 As for cleaning up... that's the bit that I can't figure out myself :(. */ Hey, Thanks for replying. ah! i see. sorry for misunderstanding you. No problem, I guess i didnt explain it well enough, and you dont have to help me but you are trying, so thank you. As for cleaning up... that's the bit that I can't figure out myself :(. exactly, I came to the same part as youbut then i would have a LOT of wasted records without the cleanup... just cant figure it out. Create a cron job that gets a list of profiles, and selects the # of records associated with that profile. If the number is 10, identify the 10th record and delete records older than that record. Once a week, optimize your tables to reclaim the space. -- My mind not only wanders, it sometimes leaves completely. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Last visitors
Hey, As to exactly how you decide which are the most recent 10 records to do this deletion, that is a little more complex. Yep, for sure Something like DELETE FROM profile_visits WHERE user='billy' AND time_of_visit NOT IN ( Select time_of_visit FROM profile_visits WHERE user='billy' ORDER by time_of_visit DESC LIMIT 10 ) Should give you an idea. Yep, gets me thinking in a few other directions... I am also toying with the idea of having a txt file for each profile (instead of using the DB) and fetch/record/delete/update the last 10 there... what do you think? easier? Thanks, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors
On 4/19/05, Ryan A [EMAIL PROTECTED] wrote:
I am not checking for the last people logged in, I want to see the last
people who have viewed the profile...
each profile will have its own last 10 people visited
/*
ah! i see. sorry for misunderstanding you.
perhaps on the script for the profiles page, the logic should be like:
profile for user: adam
if the browsing user is logged in: (for example the browsing user is ben)
insert into the database that ben has seen adam's profile
end if
and your table in the database can be:
user visitor time_of_visit
adam ben 1290122141 (unix timestamp)
then you can query it on the database for each user.
SELECT * FROM profile_visits WHERE user='adam' ORDER by time_of_visit
DESC LIMIT 10
As for cleaning up... that's the bit that I can't figure out myself :(.
*/
Hey,
Thanks for replying.
ah! i see. sorry for misunderstanding you.
No problem, I guess i didnt explain it well enough, and you dont have to
help me but you are trying, so thank you.
As for cleaning up... that's the bit that I can't figure out myself :(.
exactly, I came to the same part as youbut then i would have a LOT of
wasted records without the cleanup... just cant figure it out.
Thanks,
Ryan
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005
Well... I had a thought...
you can do a count query
SELECT user, count(visitor) as visitor_count FROM profile_visit GROUP BY user;
then you have a recordset like...
user count(visitor)
adam 9
ben 12
grab the recordset in php
while($object= mysql_fetch_object($result))
{
if($object-visitor_count 10)
{
//SELECT the least recent visit_time
$query = SELECT visit_time FROM profile_visit WHERE user='
.$object-user. ' LIMIT 9,10;
$result = mysql_query($query);
list($visit_time) = mysql_fetch_row($result);
//DELETE anything that is less recent than $visit_time, the
visit time for anything before the 10th most recent visit.
$query = DELETE FROM profile_visit WHERE user='
.$object-user. AND visit_time '$visit_time';
}
}
it's probably not very streamlined, but it should work.
Try posting on devshed as well. They are very quick and good with this
kind of stuff
forums.devshed.com
HTH
Ken
execute this in cron, once a day or something like that...
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Last visitors
Hey, Couple of options: a CRON job/scheduled job which invokes a SQL command deleting all but the 10 most recent records, **I dont think this would be very effective, as there might be thousands of profiles and each gets their last 10 visitors, some would be more popular than others or do the same every time you insert a record. **I think this is the better option. correct me if I am wrong though...I'm learning from this. Cheers, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors
what if you have something like 1000 users on your system. you'd have 1000 txt files! :( the DB seems to be a better idea as it's probably more expandable... you can do other queries to it and stuff... whereas 1000 txt files will be a pain in the butt to keep track of. On 4/19/05, Ryan A [EMAIL PROTECTED] wrote: Hey, As to exactly how you decide which are the most recent 10 records to do this deletion, that is a little more complex. Yep, for sure Something like DELETE FROM profile_visits WHERE user='billy' AND time_of_visit NOT IN ( Select time_of_visit FROM profile_visits WHERE user='billy' ORDER by time_of_visit DESC LIMIT 10 ) Should give you an idea. Yep, gets me thinking in a few other directions... I am also toying with the idea of having a txt file for each profile (instead of using the DB) and fetch/record/delete/update the last 10 there... what do you think? easier? Thanks, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors
what if you have something like 1000 users on your system. you'd have 1000 txt files! :( the DB seems to be a better idea as it's probably more expandable... you can do other queries to it and stuff... whereas 1000 txt files will be a pain in the butt to keep track of. Too true :-( -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors
On Tuesday 19 April 2005 16:27, Ryan A wrote: I am not checking for the last people logged in, I want to see the last people who have viewed the profile... each profile will have its own last 10 people visited /* ah! i see. sorry for misunderstanding you. perhaps on the script for the profiles page, the logic should be like: profile for user: adam if the browsing user is logged in: (for example the browsing user is ben) insert into the database that ben has seen adam's profile end if and your table in the database can be: user visitor time_of_visit adam ben 1290122141 (unix timestamp) then you can query it on the database for each user. SELECT * FROM profile_visits WHERE user='adam' ORDER by time_of_visit DESC LIMIT 10 As for cleaning up... that's the bit that I can't figure out myself :(. */ Hey, Thanks for replying. ah! i see. sorry for misunderstanding you. No problem, I guess i didnt explain it well enough, and you dont have to help me but you are trying, so thank you. As for cleaning up... that's the bit that I can't figure out myself :(. exactly, I came to the same part as youbut then i would have a LOT of wasted records without the cleanup... just cant figure it out. You can store only 10 records for each user by using the following logic: mysql show create table profile_log\G *** 1. row *** Table: profile_log Create Table: CREATE TABLE `profile_log` ( `profile_id` int(10) default NULL, `user_id` int(10) default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) When you create a profile you fill 10 blank records with timestamp -00-00 00:00:00. Then if a user sees a profile: PSEUDO SQL: $SQL = UPDATE profile_log SET user_id=.$userId. WHERE profile_id=. $profileId. ORDER BY timestamp ASC LIMIT 1; This way you: 1. automatically get the new timestamp 2. have no more than 10 records per user. Thanks, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005 -- Cyberly yours, Petar Nedyalkov Devoted Orbitel Fan :-) PGP ID: 7AE45436 PGP Public Key: http://bu.orbitel.bg/pgp/bu.asc PGP Fingerprint: 7923 8D52 B145 02E8 6F63 8BDA 2D3F 7C0B 7AE4 5436 pgpHCU1PRwk7f.pgp Description: PGP signature
Re: [PHP] Last visitors (SOLVED?)
Hey!
I think I solved this:
select 11 latest visitors
count to see if it returned 11 records,
if (count == 11){
get the oldest (of the 11) visitors time
delete everything from that record and older than that
}
else{}
Pros: max 2 queries
If i am missing anything or you see any problem in my logic, please point it
out.
Thanks,
Ryan
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors (SOLVED?)
On Tuesday 19 April 2005 17:03, Ryan A wrote:
Hey!
I think I solved this:
select 11 latest visitors
count to see if it returned 11 records,
if (count == 11){
get the oldest (of the 11) visitors time
delete everything from that record and older than that
}
else{}
Pros: max 2 queries
Cons:
0. there are 3 queries: select, insert/update, delete
1. you're using more than 1 query
2. your logic is tough ;-)
3. you've mixed the php and mysql logic together.
Check my reply.
If i am missing anything or you see any problem in my logic, please point
it out.
Thanks,
Ryan
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005
--
Cyberly yours,
Petar Nedyalkov
Devoted Orbitel Fan :-)
PGP ID: 7AE45436
PGP Public Key: http://bu.orbitel.bg/pgp/bu.asc
PGP Fingerprint: 7923 8D52 B145 02E8 6F63 8BDA 2D3F 7C0B 7AE4 5436
pgp3WRVppS0aV.pgp
Description: PGP signature
Re: [PHP] Last visitors
Petar Nedyalkov wrote: snip You can store only 10 records for each user by using the following logic: mysql show create table profile_log\G *** 1. row *** Table: profile_log Create Table: CREATE TABLE `profile_log` ( `profile_id` int(10) default NULL, `user_id` int(10) default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) When you create a profile you fill 10 blank records with timestamp -00-00 00:00:00. Then if a user sees a profile: PSEUDO SQL: $SQL = UPDATE profile_log SET user_id=.$userId. WHERE profile_id=. $profileId. ORDER BY timestamp ASC LIMIT 1; This way you: 1. automatically get the new timestamp 2. have no more than 10 records per user. What version of MySQL does that work in? I tried pasting that create table directly into MySQL and get an error... #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISA Query used : CREATE TABLE `profile_log` ( `profile_id` int( 10 ) default NULL , `user_id` int( 10 ) default NULL , `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE = MYISAM DEFAULT CHARSET = utf8 I can't find a reference to this on MySQL's site...would you have a link to the documentation? Thanks. -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors
On Tuesday 19 April 2005 17:08, John Nichel wrote: Petar Nedyalkov wrote: snip You can store only 10 records for each user by using the following logic: mysql show create table profile_log\G *** 1. row *** Table: profile_log Create Table: CREATE TABLE `profile_log` ( `profile_id` int(10) default NULL, `user_id` int(10) default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) When you create a profile you fill 10 blank records with timestamp -00-00 00:00:00. Then if a user sees a profile: PSEUDO SQL: $SQL = UPDATE profile_log SET user_id=.$userId. WHERE profile_id=. $profileId. ORDER BY timestamp ASC LIMIT 1; This way you: 1. automatically get the new timestamp 2. have no more than 10 records per user. What version of MySQL does that work in? I tried pasting that create table directly into MySQL and get an error... MySQL 4.1.8 #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISA Query used : CREATE TABLE `profile_log` ( `profile_id` int( 10 ) default NULL , `user_id` int( 10 ) default NULL , `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE = MYISAM DEFAULT CHARSET = utf8 I can't find a reference to this on MySQL's site...would you have a link to the documentation? Thanks. -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- Cyberly yours, Petar Nedyalkov Devoted Orbitel Fan :-) PGP ID: 7AE45436 PGP Public Key: http://bu.orbitel.bg/pgp/bu.asc PGP Fingerprint: 7923 8D52 B145 02E8 6F63 8BDA 2D3F 7C0B 7AE4 5436 pgpyeprQHJ4Sy.pgp Description: PGP signature
Re: [PHP] Last visitors
Petar Nedyalkov wrote: snip MySQL 4.1.8 snip Damn, too bad. We're still using 4.0.x and aren't going to be upgrading anytime in the near future. Thanks for the info on that though...it's an interesting feature, and one more thing I can add to the list of reasons why we need to upgrade. -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors (SOLVED?)
just do a select limit 11
display up to 11... if less then 11, you might want to display them anyways.
you should be able to deal with up to a million + records, so select
will stay quick, esp if you make the right fields indexed.
you can take care of purging via crons...
you might find you want the history in the end...
-m
Ryan A wrote:
Hey!
I think I solved this:
select 11 latest visitors
count to see if it returned 11 records,
if (count == 11){
get the oldest (of the 11) visitors time
delete everything from that record and older than that
}
else{}
Pros: max 2 queries
If i am missing anything or you see any problem in my logic, please point it
out.
Thanks,
Ryan
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors (SOLVED?)
Hey, Thanks for replying. Check my reply. I did, the problem is the client is on a box with mySql 3.23.x or 4.0.x, he is deciding to upgrade to a dedicated box but then the host he is looking at says they will charge him if they are to upgrade to mysql 4.1 (hence i cant even use sub-selects) (Sorry I didnt mention the MySql version before...didnt think it was important till now) Cons: 0. there are 3 queries: select, insert/update, delete True 1. you're using more than 1 query Giving the fact that i am on a low version of MySqlI dont see much choice here 2. your logic is tough ;-) Thats pretty cryptic...care to explain please? 3. you've mixed the php and mysql logic together. again...what other choices do i have given the low version of MySql Cheers, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors
On Tuesday, April 19, 2005 09:09, John Nichel wrote: Petar Nedyalkov wrote: snip You can store only 10 records for each user by using the following logic: mysql show create table profile_log\G *** 1. row *** Table: profile_log Create Table: CREATE TABLE `profile_log` ( `profile_id` int(10) default NULL, `user_id` int(10) default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) When you create a profile you fill 10 blank records with timestamp -00-00 00:00:00. Then if a user sees a profile: PSEUDO SQL: $SQL = UPDATE profile_log SET user_id=.$userId. WHERE profile_id=. $profileId. ORDER BY timestamp ASC LIMIT 1; This way you: 1. automatically get the new timestamp 2. have no more than 10 records per user. What version of MySQL does that work in? I tried pasting that create table directly into MySQL and get an error... #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISA Query used : CREATE TABLE `profile_log` ( `profile_id` int( 10 ) default NULL , `user_id` int( 10 ) default NULL , `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE = MYISAM DEFAULT CHARSET = utf8 I can't find a reference to this on MySQL's site...would you have a link to the documentation? Thanks. Try: CREATE TABLE `profile_log` ( `profile_id` int(10) default NULL, `user_id` int(10) default NULL, `timestamp` timestamp NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 You do not need to specify the default clause on the timestamp column since this is already the default behavior, and as you has seen this isn't valid before 4.1 anyway. Also I wouldn't use the name 'timestamp' for a column name. It is generally not a good idea to use reserved words for column names. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors
On Tuesday 19 April 2005 17:42, Tom Crimmins wrote: On Tuesday, April 19, 2005 09:09, John Nichel wrote: Petar Nedyalkov wrote: snip You can store only 10 records for each user by using the following logic: mysql show create table profile_log\G *** 1. row *** Table: profile_log Create Table: CREATE TABLE `profile_log` ( `profile_id` int(10) default NULL, `user_id` int(10) default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) When you create a profile you fill 10 blank records with timestamp -00-00 00:00:00. Then if a user sees a profile: PSEUDO SQL: $SQL = UPDATE profile_log SET user_id=.$userId. WHERE profile_id=. $profileId. ORDER BY timestamp ASC LIMIT 1; This way you: 1. automatically get the new timestamp 2. have no more than 10 records per user. What version of MySQL does that work in? I tried pasting that create table directly into MySQL and get an error... #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISA Query used : CREATE TABLE `profile_log` ( `profile_id` int( 10 ) default NULL , `user_id` int( 10 ) default NULL , `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE = MYISAM DEFAULT CHARSET = utf8 I can't find a reference to this on MySQL's site...would you have a link to the documentation? Thanks. Try: CREATE TABLE `profile_log` ( `profile_id` int(10) default NULL, `user_id` int(10) default NULL, `timestamp` timestamp NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 You do not need to specify the default clause on the timestamp column since this is already the default behavior, and as you has seen this isn't valid before 4.1 anyway. Also I wouldn't use the name 'timestamp' for a column name. I just demonstrated a concept. The guy has to do this the best way he can ;-) It's not a do-my-work-while-i'm-taking-a-rest mailing-list ;-) It is generally not a good idea to use reserved words for column names. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- Cyberly yours, Petar Nedyalkov Devoted Orbitel Fan :-) PGP ID: 7AE45436 PGP Public Key: http://bu.orbitel.bg/pgp/bu.asc PGP Fingerprint: 7923 8D52 B145 02E8 6F63 8BDA 2D3F 7C0B 7AE4 5436 pgpny01c5V0WE.pgp Description: PGP signature
Re: [PHP] Last visitors (SOLVED?)
I did, the problem is the client is on a box with mySql 3.23.x or 4.0.x, he is deciding to upgrade to a dedicated box but then the host he is looking at says they will charge him if they are to upgrade to mysql 4.1 (hence i cant even use sub-selects) (Sorry I didnt mention the MySql version before...didnt think it was important till now) The MySQL version should not matter for what he was saying (you don't need subqueries). I think he was saying that every time you insert a user, also insert 10 records into the users profile_viewed_log. with an empty timestamp. Then, every time someone views the profile, update the profile_viewed_log, instead of inserting into it. (which can be done with a simple query...) $SQL = UPDATE profile_log SET user_id=.$userId., date_entered='1113931530' WHERE profile_id=. $profileId. ORDER BY date_entered ASC LIMIT 1; (the date i have is a unix timestamp, but you can use whatever format you wish). I havn't tested that query, but it should work with no problem. Basically, it just updates the first record with the smallest timestamp. Then, when you want to show the user the views for his/her profile, you just exclude the one's with an empty timestamp. Your other option, which also would work well, would be to do the cron job, with a limit value on the query. IMHO this is better than 3 queries every time someone views a profile. Also, you can set the cron job to run at 3AM, when there are not likely to be many users. Hope this helps some. -Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors
Hey, Thanks for replying, I tried using the test example of Petar Nedyalkov, but when i try to create the following: CREATE TABLE `profile_log` ( `profile_id` int(10) default NULL, `user_id` int(10) default NULL, `last_login` timestamp NOT NULL ) ENGINE=MyISAM CHARSET=utf8 I get an error on both ENGINE=MyISAM and CHARSET=utf8 What are they for really? can I omit them? or will that effect the program later on? Thanks, Ryan On 4/19/2005 4:42:08 PM, Tom Crimmins ([EMAIL PROTECTED]) wrote: On Tuesday, April 19, 2005 09:09, John Nichel wrote: Petar Nedyalkov wrote: snip You can store only 10 records for each user by using the following logic: mysql show create table profile_log\G *** 1. row *** Table: profile_log Create Table: CREATE TABLE `profile_log` ( `profile_id` int(10) default NULL, `user_id` int(10) default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) When you create a profile you fill 10 blank records with timestamp -00-00 00:00:00. Then if a user sees a profile: PSEUDO SQL: $SQL = UPDATE profile_log SET user_id=.$userId. WHERE profile_id=. $profileId. ORDER BY timestamp ASC LIMIT 1 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 4/18/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors
On Tue, April 19, 2005 4:46 pm, Ryan A said: Hey, Thanks for replying, I tried using the test example of Petar Nedyalkov, but when i try to create the following: CREATE TABLE `profile_log` ( `profile_id` int(10) default NULL, `user_id` int(10) default NULL, `last_login` timestamp NOT NULL ) ENGINE=MyISAM CHARSET=utf8 ENGINE= is MySQL 4.x for TYPE= in MySQL 3.x I dunno about the CHARSET stuff. But then, this is a *MySQL* question, not PHP! Try here: http://mysql.com/ -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Last visitors
On Tue, April 19, 2005 5:57 am, Ryan A said: On the profile page there should be last 10 visitors, it will not register the guests, but if someone had logged in and visited your profile, it should show their usernameif there are 10 enteries in the db and when the 11th person comes...then the first person who came should be bumped off and the 11th person takes his place and so on One possibility not yet discussed... I *think* MySQL has an 'array' data type. You could then have an array of size 10 on each record. Then, if MySQL has some kind of array_push and array_slice, you'd be able to do: update users set profile_viewers = array_slice(array_push($this_visitor, profile_viewers), 1, 10) where user_id = $this_profile_id Hopefully, you can even do something like: select * from users as viewers, users as this_one where this_one.user_id = $this_profile_id and in_array(viewers.user_id, this_one.profile_viewers) I have NO IDEA if MySQL actually has all this functionality, but if it does, it may be more clear and/or efficient than a 10-to-1 table... -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
