Save input field to database

Asked

Viewed 640 times

1

How to store the last 100 searches made to a website (PHP/MYSQL) divided by countries (e.g., store the last 100 searches for books in Macedonia). Search is a simple field:

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

What is the best way to "collect" this information that does not compromise performance (I think that every new research has to be included 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 in a similar way to a queue (always keeping up to date with 100 searches)? This list should be accessible to all users and updated in real time how to do the application level too.

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.

php mysql sql

  • You want to store id_pais => palavra_pesquisada?

  • yes would be like a table where one column is the country id and the other the searched word, the problem that as are many searches I would like a more efficient way to do the insertion in the bank

  • You can always store it in a log and at the end of the day, at a dead time, insert it in the bank. Or it needs to be updated on time?

  • 1

    @Jorgeb. should be updated in real time.

1 answer

2


The best way I don’t is, but I have some ideas

SOLUTION 1

  1. Create a TRIGGER in the AFTER INSERT from your table, this Rigger makes a call to a FUNCTION.
  2. Create the FUNCTION recursively doing the following operations:
  • Make a query that returns how many records the table has, read on COUNT()
  • Make a condition in this query if it is greater than 100, then remove the first record, read on MIN()
  • Make the stop condition, to quit recursion when the query result is less than or equal to 100

Solution 2

delete old records directly (no recursivity)

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

solution 2 - reference: http://www.olamundo.blog.br/blog/? p=63

  • ,@Sneeps Ninja, as this data will have to be updated in real time this can not be costly? besides this approach there is how to do this at the level of application type queue in PHP? (by the way very good your solution)

Browser other questions tagged php mysql sql

You are not signed in. Login or sign up in order to post.