0

I have a android app Search functionality with backend PHP & mysql.

Currently on user keypress I am sending request to server fetching the data. (Live Suggestion).

With every keypress I am inserting the user input to the server.

Problem Is at end of day, to see the statistics of what users searched for is very difficult because of redundant data like follows.

User Searching for: Apple Mobile Sends the request to server on every key press as follows

  1. A
  2. Ap
  3. App
  4. Appl
  5. Apple

Question is: How to remove records from mysql [1 to 4] and keep only [5]

Note: Keep the same live search (On Key Press) functionality in Mobile App.

enter image description here

Other Notes: App is using REST API sending data through HTTP POST At a time 1000's of users may search the app. [Last previous record may not always the part of same query]

I solved this problem by sending a Unique key of 12 Character length along with search query, at end of day i am cleaning the the queries by keeping the most recent record for each key

abdul rashid
  • 730
  • 9
  • 21
  • 1
    Solution1: It is really difficult to find the records with actual string e.g apple , but you can delete the records at the end of the day which having the length less than 3 of characters. By this you may get less redundant data. Solution2: With this keypress check is user has press the **enter** key then only send the request to server to search records against the keyword. For enter key keycode is 13. – Alankar More Jan 22 '16 at 09:20
  • you can use onblur event to store record in database...if user left from search field then get value of textbox and store in database – Maksud Mansuri Jan 22 '16 at 09:24
  • you can add a timestamp to every keypress in the db and get the latest skipping all the rest -- http://stackoverflow.com/questions/11912221/mysql-select-by-newest-timestamp -- or use the same method to delete all except the last one – Tasos Jan 22 '16 at 09:42
  • @AlankarMore For Solution 1, I will loose 10% of searches with 3 characters.But Good Suggestion. For solution 2: I am Using Mobile App and Its Live search. We want user to see the live suggestion. – abdul rashid Jan 22 '16 at 09:46

1 Answers1

0

It may help you by saving the redundant data in the database. Suppose initially you don't have any records in the database for the live searches.

Let's assume end user is searching for the term 'Apple 6s'. Now let's say he had typed the keyword 'A' now according to your current logic you are requesting backend server to save this term in the database.

Let's say your user search term saving table name is "user_searches" as of now. So, by default this table is empty. When the user types the keyword 'A' record will insert in this table with the id (primary key) 1.

On next attempt when user types the keyword as 'p' again the request will send to the server with the string 'Ap.' you are not saving it as a new record instead, you will update the previous record which is already been saved with the string 'A'. so the new value of record id 1 is 'Ap'

You need to update the record id 1 unless and until space is present in the string. So once you have received the space in the string you have to insert the new or in our case second record in the database with id 2. Hence, according to this logic you will have the records in the database as follow if user is typing a string as 'Apple 6s'

id search_terms 1 Apple 2 6s

So whenever you insert the record in the database you would have to send the latest id of the record in the response.

You have to consider that if user A is searching the term "Apple" then initially this user will have the record id as 1 and simultaneously if user B is also searching the term "Nokia" then it will have the record id 2 for updating. So you have to keep track of the search terms according to the device id. For that you can add one more column in the user_searches table as "deviceId" so you can easily keep track of individual device search terms.

Hope may this will help you.

Alankar More
  • 1,107
  • 2
  • 8
  • 26