How to implement the awesome Search-as-you-Type (SayT) in PHP

[theITvideos]

Hi there.

 

I am working on a PHP web form and trying to implement search functionality. It is called Search-as-you-Type.

 

For example, when we do a search in facebook, we get all the pictures and names of the people. in a drop-down like style. I believe this is implemented using some AJAX.

 

Please see the screenshot I've attached.

 

Could any one please share the code or give me some advice on how I can accomplish this.

 

Thank you!

 

All comments and feedbacks are always welcomed

 

[attachment deleted by admin]

[theITvideos]

Hi there... I found a sample code on the internet that does a good job.

 

Here it is:

http://code.google.com/p/search-as-you-type/

http://search-as-you-type.googlecode.com/files/search-as-you-type-v1.2.zip

 

And it works perfectly with a textfile that come along with the source code.

 

Please see the screenshot  I got from Google code page. It looks good.

 

Now I just need to hook this to MySql database.

 

Please advice. Thank you!

 

[attachment deleted by admin]

[seanlim]

Wiithout having seen their code, I would say the HTTP request initiated by AJAX will have to be directed to a server-script on your server. The script will then execute whatever you need to do (in this case search the database) and return the relevant information (in this case the search results) in a format readable by your client side script.

[theITvideos]

Wiithout having seen their code, I would say the HTTP request initiated by AJAX will have to be directed to a server-script on your server. The script will then execute whatever you need to do (in this case search the database) and return the relevant information (in this case the search results) in a format readable by your client side script.

 

Thanks for the reply seanlim,

 

You can see the code here:

http://search-as-you-type.googlecode.com/files/search-as-you-type-v1.2.zip

 

It searches the data smoothly in the textfile that come along with it.

 

Please see the screenshot.

 

Just need to make it search the data in mySQL server.

 

Thank you!

 

[attachment deleted by admin]

[PFMaBiSmAd]

Actually, that code (search-responder.php) was not written very well. The main code (last 17 lines of the file) should not know or care where the data is stored at (i.e. it should only be necessary to modify the code in the one function that actually searches for the matching data.)

 

To modify that file to use a database, you would -

 

1) Remove the GetData() function definition, remove the line in the main code that calls GetData(), and remove the $data parameter from the GetResults() function call and the GetResults() function definition parameter list.

 

2) Rewrite the code in the GetResults() function so that it searches your database table for entries that have the same first x characters the same as the supplied $query parameter and returns the matching results.

[theITvideos]

Actually, that code (search-responder.php) was not written very well. The main code (last 17 lines of the file) should not know or care where the data is stored at (i.e. it should only be necessary to modify the code in the one function that actually searches for the matching data.)

 

To modify that file to use a database, you would -

 

1) Remove the GetData() function definition, remove the line in the main code that calls GetData(), and remove the $data parameter from the GetResults() function call and the GetResults() function definition parameter list.

 

2) Rewrite the code in the GetResults() function so that it searches your database table for entries that have the same first x characters the same as the supplied $query parameter and returns the matching results.

 

Thanks for the reply PFMaBiSmAd,

 

I've removed the GetData() function and removed this function call from the GetResults() parameter list.

 

Now the GetResults() function still uses the variable named $data (which is returned by GetData() function).

 

Sorry brother, this function is little over my head. I am just trying to make it work.

 

I have a simple:

 

database: AddressBook

Table: myContacts

Field: [firstName],[LastName]

 

where can we embed php select query where it will search for the firstName etc.

 

I thank you so much!

[PFMaBiSmAd]

The following is the code modified (untested) to use a database instead -

 

<?php
/*
* Copyright (C) 2006 Google Inc.
* 
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
* 
*      http://www.apache.org/licenses/LICENSE-2.0
*      
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/

/*
* Search-as-you-type sample Ajax responder
*/

  // Adding a cache control so that browsers won't cache Ajax requests
  header("Cache-Control: no-cache"); 
  header("Content-Type: text/html; charset=UTF-8");

  /**
   * Get the results based on user's query.
   * @param string $query Query
   * @return array Result array
   */ 
  function GetResults($query) {
    $results = array();
    $queryLength = strlen($query);
// form the sql query string
$query = mysql_real_escape_string($query);
$sql = "SELECT name,type,content,moreDetailsUrl FROM your_table WHERE LEFT(name, $queryLength) = '$query'";
// execuite query here, using whatever method you currently use...
$result = mysql_query($sql);

    while($record = mysql_fetch_assoc($result)) {
        $result = array();
        $result['name'] = $record['name'];
        $result['type'] = $record['type'];
        $result['content'] = $record['content'];
        $result['moreDetailsUrl'] = $record['moreDetailsUrl'];
        $result['style'] = ($query == strtolower($record['name'])) ? 'expanded' : 'normal';
        $results[] = $result;
    }
    return $results;
  }

  // Get the query
  $query = strtolower(ltrim($_GET['query']));

  // Build response
  $response = array();
  $response['query'] = $query;
  $response['results'] = GetResults($query);
  if (count($response['results']) == 1) {
    $response['autocompletedQuery'] = $response['results'][0]['name'];
  }

  // Output response
  echo "searchAsYouType.handleAjaxResponse(";
  echo json_encode($response);
  echo ");";
?>

 

You would need to add a database connection and adjust the fields in the query to match your actual columns. I did not determine what would happen if the fields being output to the AJAX differed from what the code originally expected.

[PFMaBiSmAd]

Upon actually testing that code (somewhat), the $result variable from the mysql_query() and used in the mysql_fetch_assoc() needs to be renamed to something like $res

[theITvideos]

Upon actually testing that code (somewhat), the $result variable from the mysql_query() and used in the mysql_fetch_assoc() needs to be renamed to something like $res

 

Thank you for your reply. Can you please send the code that you are using for testing.

 

Thank you

[PFMaBiSmAd]

It's the code I posted with the following two lines changed to use $res instead of $result -

 

   $res = mysql_query($sql);

    while($record = mysql_fetch_assoc($res)) {

 

And with mysql_connect()/mysql_select_db() statements thrown in.

[theITvideos]

It's the code I posted with the following two lines changed to use $res instead of $result -

 

   $res = mysql_query($sql);

    while($record = mysql_fetch_assoc($res)) {

 

And with mysql_connect()/mysql_select_db() statements thrown in.

 

Ok this is the code that I am using including the database connection in the  GetResults() function:

 

function GetResults($query) {
        $results = array();
        $queryLength = strlen($query);

        // form the sql query string
        $query = mysql_real_escape_string($query);

        $username = "root";
        $password = "";
        $hostname = "localhost"; 
        $dbhandle = mysql_connect($hostname, $username, $password)
        or die("Unable to connect to MySQL");

        $selected = mysql_select_db("AddressBook",$dbhandle)
        or die("Could not select AddressBook");

        $sql = "SELECT firstName FROM myContacts WHERE LEFT(firstName, $queryLength) = '$query'";

        // execuite query here, using whatever method you currently use...
        $res = mysql_query($sql);

        while($record = mysql_fetch_assoc($res)) {
            $record = array();
            $record['firstName'] = $record['firstName'];
            $record['type'] = $record['type'];
            $record['content'] = $record['content'];
            $record['moreDetailsUrl'] = $record['moreDetailsUrl'];
            $record['style'] = ($query == strtolower($record['name'])) ? 'expanded' : 'normal';
            $record[] = $record;
        }

        return $record;
    }

 

And as I type in the textbox, I get no results.

 

Am I missing anything here in the code.

 

Please reply. Thank you!

[PFMaBiSmAd]

mysql_real_escape_string would need to be after the database connection logic since a database connection is required for it to work.

[theITvideos]

mysql_real_escape_string would need to be after the database connection logic since a database connection is required for it to work.

 

Thanks for your reply. yes i did that. And also I am debugging the code to see whats happening under the hood.

 

I noticed even with the proper DB connection, the GetResults() function always returns false.

 

 

 

 function GetResults($query) {
        $results = array();
        $queryLength = strlen($query);

        // form the sql query string

        $username = "root";
        $password = "";
        $hostname = "localhost"; 
        
        $dbhandle = mysql_connect($hostname, $username, $password)
        or die("Unable to connect to MySQL");
        
        $selected = mysql_select_db("AddressBook",$dbhandle)
        or die("Could not select AddressBook");
        
        $query = mysql_real_escape_string($query);

        $sql = "SELECT fName FROM myContacts WHERE LEFT(fName, $queryLength) = '$query'";

        // execuite query here, using whatever method you currently use...
        $res = mysql_query($sql);

        while($record = mysql_fetch_assoc($res)) {
            $record = array();
            $record['fName'] = $record['fName'];
            $record['type'] = $record['type'];
            $record['content'] = $record['content'];
            $record['moreDetailsUrl'] = $record['moreDetailsUrl'];
            $record['style'] = ($query == strtolower($record['fName'])) ? 'expanded' : 'normal';
            $record[] = $record;
        }

        return $record;
    }

 

The value of $record variable is always false. Even though the database connectivity is just fine. There are records in the table for the searched query. By the way... I tried commenting out the 3 lines:

 

            $record['type'] = $record['type'];
            $record['content'] = $record['content'];
            $record['moreDetailsUrl'] = $record['moreDetailsUrl'];

 

as i don't have the 'type' or 'content' field in the table. what other tweaking shall we do in the getResults() function.

 

Thank you

[PFMaBiSmAd]

Well you altered the both the $result and $results variables being used in the body of the while loop to $record, so you have nonsense code now.

[theITvideos]

Well you altered the both the $result and $results variables being used in the body of the while loop to $record, so you have nonsense code now.

 

So how can we fix the $result and $results variable thing.

 

Please reply.

 

Thank you!

[PFMaBiSmAd]

You cannot be serious? You put it back the way it was before you altered it.

[theITvideos]

You cannot be serious? You put it back the way it was before you altered it.

 

I am sorry bro but i guess my brains is not in the right place. This is the code I have:

 

function GetResults($query) {
        $results = array();
        $queryLength = strlen($query);

        // form the sql query string

        $username = "root";
        $password = "";
        $hostname = "localhost"; 
        
        $dbhandle = mysql_connect($hostname, $username, $password)
        or die("Unable to connect to MySQL");
        
        $selected = mysql_select_db("AddressBook",$dbhandle)
        or die("Could not select AddressBook");
        
        $query = mysql_real_escape_string($query);

        $sql = "SELECT fName FROM myContacts WHERE LEFT(fName, $queryLength) = '$query'";

        // execuite query here, using whatever method you currently use...
        $res = mysql_query($sql);

        while($record = mysql_fetch_assoc($res)) {
            $record = array();
            $record['fName'] = $record['fName'];
            $record['type'] = $record['type'];
            $record['content'] = $record['content'];
            $record['moreDetailsUrl'] = $record['moreDetailsUrl'];
            $record['style'] = ($query == strtolower($record['fName'])) ? 'expanded' : 'normal';
            $record[] = $record;
        }

        return $record;
    }

 

Here is the code. Where shall I fix the $result  and $results here.

 

Thank you so much brother!

 

[seanlim]

lol.. i think this is what you're trying to get at? haven't tested it out tho

 

function GetResults($query) {
        $results = array();
        $queryLength = strlen($query);

        // form the sql query string

        $username = "root";
        $password = "";
        $hostname = "localhost"; 
        
        $dbhandle = mysql_connect($hostname, $username, $password)
        or die("Unable to connect to MySQL");
        
        $selected = mysql_select_db("AddressBook",$dbhandle)
        or die("Could not select AddressBook");
        
        $query = mysql_real_escape_string($query);

        $sql = "SELECT fName FROM myContacts WHERE LEFT(fName, $queryLength) = '$query'";

        // execuite query here, using whatever method you currently use...
        $res = mysql_query($sql);

        while($row = mysql_fetch_assoc($res)) {
            $record = array();
            $record['fName'] = $row['fName'];
            $record['type'] = $row['type'];
            $record['content'] = $row['content'];
            $record['moreDetailsUrl'] = $row['moreDetailsUrl'];
            $record['style'] = ($query == strtolower($row['fName'])) ? 'expanded' : 'normal';
            $results[] = $record;
        }

        return $results;
    }

[theITvideos]

lol.. i think this is what you're trying to get at? haven't tested it out tho

 

function GetResults($query) {
        $results = array();
        $queryLength = strlen($query);

        // form the sql query string

        $username = "root";
        $password = "";
        $hostname = "localhost"; 
        
        $dbhandle = mysql_connect($hostname, $username, $password)
        or die("Unable to connect to MySQL");
        
        $selected = mysql_select_db("AddressBook",$dbhandle)
        or die("Could not select AddressBook");
        
        $query = mysql_real_escape_string($query);

        $sql = "SELECT fName FROM myContacts WHERE LEFT(fName, $queryLength) = '$query'";

        // execuite query here, using whatever method you currently use...
        $res = mysql_query($sql);

        while($row = mysql_fetch_assoc($res)) {
            $record = array();
            $record['fName'] = $row['fName'];
            $record['type'] = $row['type'];
            $record['content'] = $row['content'];
            $record['moreDetailsUrl'] = $row['moreDetailsUrl'];
            $record['style'] = ($query == strtolower($row['fName'])) ? 'expanded' : 'normal';
            $results[] = $record;
        }

        return $results;
    }

 

Thank you for your reply. Now I've a debugger and I checked and found out that the $record variable has fName value when it is inside the while loop  which is good but when once its values are assigned to the $results[] variable and after comes out the loop, the $results[] variable has no value for the 'fName' index.

 

Do we need to do something inside the while loop near the line:

 

$results[] = $record;

 

Please reply. Thank you.

 

 

 

 

[seanlim]

not that i can think of.

 

...
$record['moreDetailsUrl'] = $row['moreDetailsUrl'];
$record['style'] = ($query == strtolower($row['fName'])) ? 'expanded' : 'normal';

// add this line in
var_dump($record);

$results[] = $record;
}

 

does $record['fName'] display?

[theITvideos]

not that i can think of.

 

...
$record['moreDetailsUrl'] = $row['moreDetailsUrl'];
$record['style'] = ($query == strtolower($row['fName'])) ? 'expanded' : 'normal';

// add this line in
var_dump($record);

$results[] = $record;
}

 

does $record['fName'] display?

 

 

Thanks for your message. Yes I am getting the values in the $record variable.

 

Ok I am going to paste the GetResults() function definition and the function call:

 

GetResults() Function definition:

 

 function GetResults($query) {
        $results = array();
        $queryLength = strlen($query);

        // form the sql query string
//        $query1 = mysql_real_escape_string($query);

        $username = "root";
        $password = "";
        $hostname = "localhost"; 
        
        $dbhandle = mysql_connect($hostname, $username, $password)
        or die("Unable to connect to MySQL");
        
        $selected = mysql_select_db("AddressBook",$dbhandle)
        or die("Could not select AddressBook");
        
        $query = mysql_real_escape_string($query);

        $sql = "SELECT c_name FROM myContact WHERE LEFT(f_name, $queryLength) = '$query'";

        // execuite query here, using whatever method you currently use...
        $res = mysql_query($sql);

        while($record = mysql_fetch_assoc($res)) {
            $record = array();
            $record['f_name'] = $record['f_name'];
//          $record['type'] = $record['type'];
//          $record['content'] = $record['content'];
//          $record['moreDetailsUrl'] = $record['moreDetailsUrl'];
//	        $record['style'] = ($query == strtolower($record['f_name'])) ? 'expanded' : 'normal';
        $record[] = $record;
		var_dump($record);
             $results[] = $record[f_name];
        }

        return $results;
    }

 

I commented out the  $record['type'], $record['content'] and $record['moreDetailsUrl '] as I only need the first name and I 'am' getting the first name in the $record variable but when it assigns to the $results[]  variable, the f_name value is lost.

 

 

And here is the function call for GetResults():

 $response['results'] = GetResults($query);
    if (count($response['results']) == 1) {
        $response['autocompletedQuery'] = $response['results'][0]['f_name'];
    }

 

Please see the screenshot I have attached here for clear understanding.

 

Thank you.

 

Kindly reply

 

 

 

[attachment deleted by admin]

[PFMaBiSmAd]

Your code is still overwriting the variables holding the data.

 

Seanlim went to the trouble of correcting the code you messed up and posted it AND you quoted his post, which suggested that you read it and used the code in it (OR you don't understand that there is a difference between simply replying to a post and quoting a post when you write a reply.)

 

Short answer: Each line of code does matter. It does matter exactly what is written the right-hand side of an = assignment operator and it does matter exactly what is written on the left-hand side on an = assignment operator, because computers only do exactly what each line of code tells them to do. Even if you cannot produce the code you are using, you must have the ability to at least look at it and be able to understand what each line does.

[merylvingien]

If your just looking for a basic ajax search that shows the results as you type:

Javascript:

 

<script type="text/javascript">
function showResult(str)
{
if (str.length==0)
  {
  document.getElementById("livesearch").innerHTML="";
  document.getElementById("livesearch").style.border="0px";
  return;
  }
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("livesearch").innerHTML=xmlhttp.responseText;
    document.getElementById("livesearch").style.border="1px solid #A5ACB2";
    }
  }
xmlhttp.open("GET","livesearch.php?q="+str,true);
xmlhttp.send();
}
</script>

 

html form:

 

<form>
<p>Search:<br>
<input type="text" size="20" onkeyup="showResult(this.value)" />
</p>
<div id="livesearch"></div>
</form>

 

livesearch.php:

 

<?php
include ("database connection");



$q=$_GET["q"];
$q = strip_tags($q);
$q = mysql_real_escape_string($q); 

$sql="SELECT * FROM tablename WHERE `rowname` LIKE '%{$q}%' OR `rowname2` LIKE '%{$q}%' ORDER BY `rowname`";

$result = mysql_query($sql);


      
      if (mysql_num_rows($result) < 1) {
         $error[] = "No results found!";
      }else {
         $results = array(); // the result array
         $i = 1;
         while ($row = mysql_fetch_assoc($result)) {
            $results[] = "{$i}:<br />{$row['rowname']}<br /><br />";
            $i++;
         }
      }
   

function removeEmpty($var) {
   return (!empty($var)); 
}
if (empty($results)) {$output= implode("<br>", $error);} //$output= "No results found!";
elseif ($results > 0){$output = "<p>Your search term: <span id=\"high\">" . $q .  "</span> returned:</p><p>" . implode("", $results) . "</p>";
}

echo "$output";
?> 

 

You will need to enter the relevant tablenames and database info etc, but this is a working code.

[theITvideos]

Your code is still overwriting the variables holding the data.

 

Seanlim went to the trouble of correcting the code you messed up and posted it AND you quoted his post, which suggested that you read it and used the code in it (OR you don't understand that there is a difference between simply replying to a post and quoting a post when you write a reply.)

 

Short answer: Each line of code does matter. It does matter exactly what is written the right-hand side of an = assignment operator and it does matter exactly what is written on the left-hand side on an = assignment operator, because computers only do exactly what each line of code tells them to do. Even if you cannot produce the code you are using, you must have the ability to at least look at it and be able to understand what each line does.

 

[attachment deleted by admin]

[theITvideos]

If your just looking for a basic ajax search that shows the results as you type:

Javascript:

 

<script type="text/javascript">
function showResult(str)
{
if (str.length==0)
  {
  document.getElementById("livesearch").innerHTML="";
  document.getElementById("livesearch").style.border="0px";
  return;
  }
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("livesearch").innerHTML=xmlhttp.responseText;
    document.getElementById("livesearch").style.border="1px solid #A5ACB2";
    }
  }
xmlhttp.open("GET","livesearch.php?q="+str,true);
xmlhttp.send();
}
</script>

 

html form:

 

<form>
<p>Search:<br>
<input type="text" size="20" onkeyup="showResult(this.value)" />
</p>
<div id="livesearch"></div>
</form>

 

livesearch.php:

 

<?php
include ("database connection");



$q=$_GET["q"];
$q = strip_tags($q);
$q = mysql_real_escape_string($q); 

$sql="SELECT * FROM tablename WHERE `rowname` LIKE '%{$q}%' OR `rowname2` LIKE '%{$q}%' ORDER BY `rowname`";

$result = mysql_query($sql);


      
      if (mysql_num_rows($result) < 1) {
         $error[] = "No results found!";
      }else {
         $results = array(); // the result array
         $i = 1;
         while ($row = mysql_fetch_assoc($result)) {
            $results[] = "{$i}:<br />{$row['rowname']}<br /><br />";
            $i++;
         }
      }
   

function removeEmpty($var) {
   return (!empty($var)); 
}
if (empty($results)) {$output= implode("<br>", $error);} //$output= "No results found!";
elseif ($results > 0){$output = "<p>Your search term: <span id=\"high\">" . $q .  "</span> returned:</p><p>" . implode("", $results) . "</p>";
}

echo "$output";
?> 

 

You will need to enter the relevant tablenames and database info etc, but this is a working code.

 

Thank you this code, it worked for me.

 

I would like to ask you one thing... is there a way we can select the result that is displayed i.e. after the search when I click on the search result box it should put the clicked Name in my searchTextbox.

 

As I have shown in the Screenshot I've attached.

 

Please reply. All feedbacks are welcomed.

 

Thank you!

 

 

[attachment deleted by admin]