There is a page. When entering this page, the database records information about the user's browser, its ip-address, the date and time of entry.

How to make it so that if the same user enters the page under the same ip-address, then only the time of entry was updated, but a new line was not created?

  • there is such a thing in mysql as upsert - splash58
  • one
    This question involves too much to answer unequivocally - korytoff

3 answers 3

According to the fields that uniquely identify the user and ip, you make a unique index in the table so that it would be impossible to insert two records relating to one user-ip bundle into the table. After this, insert a new record in the table as follows:

insert into tableX(ip,user_id,a,b,c,date) values('10.0.0.1',100,'x','y','z',now()) on duplicate key update date=now();

This query either inserts a new record with the data that is in the values, or, if such an entry exists, will update the one specified in the on duplicate key

A unique index on the table is created like this:

 create unique index index_name on table_name(ip, user_id);

Since you also need information about the browser, and a user from one computer can use different browsers (and by the way, sometimes the same browser gives slightly different identification lines), the table will need to ensure uniqueness in the ip, user_id, browser fields and maybe some other. If it is difficult, because the application is guided by the complex logic of determining the "that" record, you can try to update the date, if 0 lines were changed (see a function like mysql_affected_rows ), then insert it.

  • and what if several users from the same ip? .. For example, several people from the same office climb the site ... Can create a cookie and dance from it about a unique index? - cyadvert
  • @cyadvert Did not notice about the user in question, corrected the wording. - Mike
  • Thanks for answers. Now I have here is the insertion into the database mysql_query ("INSERT INTO test ( ip_address , User_agent , View_date , Page_url ) VALUES ('". $ ip. "', '". $ agent. "', '". $ date. "','". $ url. "')"); me at the end just add? on duplicate key update date = now (); - Igor Boldyrev
  • @ Igor Boldyrev Not really, it’s necessary that the table itself would not allow to insert the second record. I need to make an index like create unique index test_ind on test(ip_address, User_agent, Page_url) (if I understood correctly that you have a separate counter for each Page_url. - Mike
  • unique index created assigned in phpmyadmin. Now ip-addresses are not duplicated, but I do not understand how to update the time, date. it may work out like a condition, such as if (# $ ip == $ ip) {mysql_query ("UPDATE test (View_date) VALUES ('". $ date. "')} Sorry for not catching up right away) - Igor Boldyrev

As an option, you can make a query before writing the form: SELECT id FROM tableUser WHERE ip = '$ipuser' If this query returns you the id value, we make an Update request for this field

  • Of course you can, only then it is necessary not to forget about blocking. For if the server almost simultaneously receives two requests with one ip, then it may turn out that the first will go to the database, will not find the record, prepare to insert it, then the OS will interrupt it (it is multitask), give control to the second process, it will insert its the record, then the control will return to the first one, who thinks that there is no record and he will also try to insert it and get an error, or duplicate the record if the uniqueness conditions on the table miss it - Mike
  • Yes, you are right, I wrote as an idea of ​​checking the implementation is yours) - Shadow33

I would recommend either:

  • Make a hash from User-Agent + IP and already made INSERT INTO table (hash, ip, lastdate) VALUES (?,?, Now ()) ON DUPLICATE KEY UPDATE ...

  • It should be understood that under load more than 200 requests per second it will fall, you need to save the log to a file and then aggregate it offline