Auto Complete Tutorial: JavaScript, PHP and MySQL

Hello everyone!

Today’s post is about Auto Complete.

I use the jQuery library a lot for JavaScript. So in this small tutorial we’ll use JavaScript (jQuery), PHP, MySQL and CSS.

When using Auto Complete in a Search field, it becomes much more practical for the user to find what they want, because when typing a word, or a piece of it, we return content in which the typed word is part of.

Starting with JavaScript:

function showSuggestions(search) {
	if(search.length == 0) {
		// Hide suggestions
		$('#suggestions').hide();
	} else {
		$.post("http://www.xalexandre.com.br/autoSearch/ajaxSearch.php", {queryString: ""+search+""}, function(data){
			if(data.length >0) {
				$('#suggestions').show();
				$('#suggestionsList').html(data);
			}
		});
	}
}

function sendSuggestions(thisValue) {
	$('#search').val(thisValue);
	setTimeout("$('#suggestions').hide();", 200);
}

Well, summarizing the js above works as follows: in the showSuggestions function, the first if tests if the characters of the search variable equals zero, if so it hides the suggestions div. Logically, because if the user didn’t type anything, we don’t need to show the suggestions box.

If this if is false, we’ll enter the else, then we call the ajaxSearch.php script, passing the search content to queryString, then another test is made, related to ajaxSearch, to know if the result of the returned query is greater than zero, if so it will show the results in a list.

In the sendSuggestions function, we just send the returned value from the list to the search field.

Now let’s move to PHP, the file has already been mentioned: ajaxSearch.php

<?php
	// Check if a string was sent in the search.
	if(isset($_POST['queryString'])) {

		/*	You most likely have a file with the
			data that makes the connection to the database. If you have
			just call the file here inside, for example:
			require("connection.php");

			Below is an example for connection.
		*/
		$host = "localhost";
		$usr = "USERNAME";
		$pwd = "PASSWORD";
		$bd = "DATABASE";

		//DB Connection
		mysql_connect("$host","$usr","$pwd") or die
		("ERROR: Could not connect to DB.");
		mysql_select_db("$bd") or die
		("ERROR: Could not open DB.");

		$queryString = ($_POST['queryString']);

		// Check if the number of characters is greater than zero.
		if(strlen($queryString) >0) {

			/*	If the number of characters is greater than zero,
				we'll enter here, where the SQL is executed.
			*/
			$query = mysql_query("select FIELD from TABLE
			where FIELD like '%$queryString%' limit 20");
			/*	Well, regarding the SQL above, change the FIELD and
				TABLE for your case.
				Note that $queryString is passed with percentage
				(%), so if the user types ava we'll return
				"avocado, avatar", if it's in our table of course.
			*/
			if($query) {
				// If the query is executed correctly, we'll enter
				this line below, where a repetition loop is made,
				assigning values to the $result variable.
				while ($result = mysql_fetch_array($query)) {
					/*	Below we'll show the result in a list,
						again change where it says FIELD to the field
						of your table.
					*/
					echo '<li onClick="receiveSuggestions(\''.htmlentities($result[FIELD]).'\');">'.htmlentities($result[FIELD]).'</li>';
					/*
						In onClick a function: receiveSuggestions, which
						I mentioned earlier that serves to assign
						the result to the text field.
					*/
				}
			} else {
				echo 'ERROR: A problem occurred with the query.';
			}
		}
	} else {
		echo 'Direct access to this script is not allowed.';
	}
?>

Well, the code above is all commented, but if any doubt arises leave a comment.

Moving to CSS:

<style>
	.suggestions {
		position: relative;
		left: 30px;
		margin: 10px 0px 0px 0px;
		width: 200px;
		background-color: #212427;
		-moz-border-radius: 7px;
		-webkit-border-radius: 7px;
		border: 2px solid #000000;
		color: #FFFFFF;
	}

	.suggestionsList {
		margin: 0px;
		padding: 0px;
	}

	.suggestionsList li {

		margin: 0px 0px 3px 0px;
		padding: 3px;
		cursor: pointer;
	}

	.suggestionsList li:hover {
		background-color: #659CD8;
	}
</style>

And finally the HTML:

<form id="searchForm" method="post" action="">
	<input name="search" id="search" size="25" type="text" onkeyup="showSuggestions(this.value);" onblur="sendSuggestions();" />
	<div class="suggestions" id="suggestions" style="display: none;">
		<img src="http://www.xalexandre.com.br/autoSearch/upArrow.png" style="position: relative; top: -12px; left: 30px;" alt="up arrow" />
		<div class="suggestionsList" id="suggestionsList">
			&nbsp;
		</div>
	</div>
</form>

That’s all folks! An example can be found here on the site, in the upper right corner. Questions, criticism or suggestions? Leave a comment.

[]‘s