Cloning Google Suggest With Ajaxac
Implementing Backend Data Lookup Functionality
Now we add the functionality to lookup a search term for suggestions in the database. This basically consists of:
- Connect to database
- Look up search terms
- Close database connection
- Return results
To do this, we add one function to GoogleSuggestCloneJax.class.php, as well as a few member properties.
require_once('AjaxACApplication.class.php'); class GoogleSuggestCloneJax extends AjaxACApplication { var $db_hostname = 'localhost'; var $db_username = 'ajaxac'; var $db_password = ''; var $db_database = 'ajaxac'; var $db_table = 'google_suggest_clone_data'; // ... other code ... // function getSuggestions($prefix, &$arr) { $conn = @mysql_connect($this->db_hostname, $this->db_username, $this->db_password); if (!$conn) return; if (!@mysql_select_db($this->db_database, $conn)) { mysql_close($conn); return; } // firstly clean up the data $prefix = ltrim(preg_replace('/[^a-z0-9_. ]/', '', strtolower($prefix))); $prefix = preg_replace('/\s+/', ' ', $prefix); if (strlen($prefix) > 0) { $query = sprintf("select search_term, num_results from %s where search_term like '%s%%' limit %d", $this->db_table, mysql_real_escape_string($prefix), $this->suggestion_limit); $result = mysql_query($query); while ($row = mysql_fetch_row($result)) { if ($row[1] == 0) $row[1] = ''; else $row[1] = number_format($row[1]) . ' result' . ($row[1] != 1 ? 's' : ''); $arr[] = $row; } } mysql_close($conn); } }
So at this point, you need to enter your database details in the four variables provided, and the database table you created in the fifth (you probably won’t need to change this, unless you modified data.sql).
Rather than actually returning an array of results, we are passing a reference to an array when calling this function. This function will simply populate the array. This is primarily done as a performance enhancement, as it’s a cheaper operation to pass the array reference rather than return a copy of all the array data at the end of the function.
So this function goes something like this:
- Try to connect, if this fails, just return (no results fetched)
- Try to select database, if this fails, just return (no results fetched)
- Sanitize string by removing all invalid characters and then trimming it
- Next, look up the results in the database and populate the array with them
- Finally, close the database connection