Save input field to the database

Navigation
1

How to store the last 100 searches made to a site (PHP / MYSQL) divided by country (eg store the last 100 searches for Macedonian books). Search is a simple simple field: 

<input type="text" id="search" name="search" placeholder="Search" />

What is the best way to "collect" this information that does not compromise the performance (I think that each new search has to be inserted in the bank is too expensive), and the best way to structure this information in tables in the bank? and how to create a limitation to 100 searches per country and work similarly to a queue (keeping the 100 searches always up to date)? This list should be accessible to all users and updated in real time as do the application level as well.

Country Table: 

CREATE TABLE pais(
id INTEGER NOT NULL AUTO_INCREMENT,
codigo CHAR(2) NOT NULL UNIQUE,
nome VARCHAR(70) NOT NULL,
PRIMARY KEY(id));

Note: This code is the ISO country code.

    
asked by anonymous 29.04.2015 / 14:30

1 answer

2

The best way I do not know is, but I have some ideas

SOLUTION 1

  • Create a TRIGGER in the AFTER INSERT of your table, this trigger calls a FUNCTION .
  • Create FUNCTION by recursively doing the following operations:
    • Make a query that returns how many records the table has, read about COUNT ()
    • Make a condition in this query if it is greater than 100, then remove the first record, read about MIN ()
    • Make the condition stop, to exit recursion when the query result is less than or equal to 100
    Solution 2

    Delete old records directly (without recursion) 

    DELETE FROM pais WHERE id NOT IN (SELECT * FROM  (SELECT id FROM pais ORDER BY id DESC LIMIT 100) alias);
      

    solution 2 - reference: link

        
    29.04.2015 / 15:44
    Regular Expression Problems Using Timezones with Moment plugin