PhpRiot
Become Zend Certified

Prepare for the ZCE exam using our quizzes (web or iPad/iPhone). More info...


When you're ready get 7.5% off your exam voucher using voucher CJQNOV23 at the Zend Store

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:

  1. Connect to database
  2. Look up search terms
  3. Close database connection
  4. Return results

To do this, we add one function to GoogleSuggestCloneJax.class.php, as well as a few member properties.

Listing 5 listing-5.php
<?php
    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:

  1. Try to connect, if this fails, just return (no results fetched)
  2. Try to select database, if this fails, just return (no results fetched)
  3. Sanitize string by removing all invalid characters and then trimming it
  4. Next, look up the results in the database and populate the array with them
  5. Finally, close the database connection

In This Article