Cloning Google Suggest With Ajaxac

Creating The Database Schema And Populating It

Now we will create our MySQL database to hold the query data. The data will be purely fictional, and obviously we won’t be able to perform actual web searches, but it will be sufficient to create some autocomplete data. We have compiled this list just by performing some sample lookups on the real Google Suggest.

1. Create a new MySQL database. You will need to know the hostname, database name, username and password later on
2. Populate the database with the data from data.sql

To lookup the data, this is the basic process:

1. Retrieve the user input from the subrequest
2. Sanitize it by removing all characters except a-z, A-Z, 0-9, as well as spaces, periods and underscores.
3. Connect to MySQL database
4. Perform a ‘like’ search on the _search_term_ column, retrieving up to 10 results

Notes:

• The MySQL connection will be one of the biggest performance hits of the application, but may be difficult to get around due to the nature of PHP. Perhaps persistent connections could help some?
• We will pre-format the returned numbers. This could potentially be done by the client to save some server processing power, but in this example we will do this in PHP.
• The ‘like’ search should be adequate for our purposes. In the schema, the search_term column has been indexed and there will be only a single wildcard in the request.
• I’m not sure how Google Suggest ranks its suggestion results – probably by the popularity of the search. To simulate this, we won’t order our returned results. This will also improve speed as if it had to order, it would have to find every single matching row then order them all – this way it only needs to find any 10 matching rows.

• Next: Implementing Backend Data Lookup Functionality