Ajax Dynamic Search Tutorial & Demo

[John_Betong]

Online Demo

Essential Platform Requirements

1. PHP
2. MySql
3. Database and a table with a text field or varchar field

Tutorial Notes

Creating a new database and table seemed a lot of work just to show a demo with content that would only be used once! I opted to use:

• your very own database table content.
• with a minimum setup
• to be running in under a minute!

Special Note For the Paranoid:
Please feel free to copy an existing database with a table that has at least one searchable varchar or text field.

Demo Installation in Three Easy Steps:

** Step 1: **
- download “jb-ajax-search.zip”
- Unpack into any new localhost or online “new-directory”

** Step 2: use any editor to modify “db-results.php”
- define(‘HOST’, ‘yourHost’);
- define(‘uNAME’, ‘userName’);
- define(‘pWORD’, ‘passWord’);
- define(‘dBASE’, ‘dataBase’);
- define(‘dTABLE’, ‘tableName’);
- define(‘dCOL001’, ‘columName’); // field must be varchar or text
- define(‘dCOL003’, ‘columnName’); // field must be NULL or (varchar or text)

** Step3: browse and open “/new-directory/index.php”
- finished in less than a minute!

Live Installation in Three Easy Steps:

1. copy & paste to your web-page: <?php require “ajax-form.php”;?>
2. copy & paste to your web-page: <div id=“livesearch”></div>
3. set path to “db-results.php” in “ajax-form.php”

Suggested Improvements:

- add fields to $sql statement
- format search results
- add pagination to search results

TL;DR - Preliminary Discussion

Recently I have been searching database tables containing foreign characters and tried numerous techniques. Results revealed there is no simple solution. Two effective but slow, clunky methods were syllables and phonemes.

To make nippier searches the phonemes method involved storing an extra column in the table. Both methods involved entering text into a search edit box, clicking enter, parsing the search text into a SQL statement, searching and returning partial or empty no results.

[Ajax to the Rescue](https://en.wikipedia.org/wiki/Ajax_(programming)

Although I have never been a JavaScript fan; this was definitely a big plus for Ajax. Primarily because only a single character needs uploading to a fast server which uses PHP’s MySql used to dynamcally render the browser results.

####W3School’s Ajax Tutorial
was followed and the demos worked. Next task was to update to a server. There were some minor problems but delighted to say it also worked!

Complications arose when trying to interface with CodeIgniter. The biggest problem was the number of files and saving to their correct paths. Simplification was essential if Ajax was to be used on other domains.

Having a working online demo made debugging easier because any failed alternative approaches could easily be rolled back. After exhaustive testing and debugging the project is now online:

Files

1. index.php - displays the Live Search and also links to all source code.
2. _header.php - required() by index.php and reduces index.php file size.
3. _ajax-form.php - required() by index.php. Also contains JavaScript.
4. db-results.php - a standalone file that renders database table results also called from the Ajax routine. This file is essential and must display the database table results in order for Ajax to work correctly.
5. install.md - this file
6. jb-search.zip - zipped contents to be dowloaded and installed into "new-driectory

Have fun

[Gandalf]

That looks great John. I imagine it could be amended to use SQLite. Hmmm, a project for the New Year…

[John_Betong]

@Gandalf

I imagine it could be amended to use SQLite.

I am not familiar with SQLite however I should imagine it is similar to MySqli and requires a SQL statement which is used to find results.

Take a look at the three functions in the db-results.php. There is not a great deal of script that requires changing and think it will only take a couple of minutes.

If you manage to get SQLite working, please share the results.

[Gandalf]

@John_Betong

If you manage to get SQLite working, please share the results.

It shouldn’t take much, I’m sure. And I’ll let you know when I’ve got it done. Won’t be for a little while as I’m on holidays for a while.

[Gandalf]

Hello John

I saw in another post that you had updated your script to PDO. I started working on changing it to SQLite with PDO and haven’t got too far, so I thought I would download the new version but the download link doesn’t seem to be working.

[Mittineague]

@Gandalf

I started working on changing it to SQLite with PDO and haven’t got too far

This might help some, perhaps not as well written as it could be, but it works

function run_sql_query($this_db, $this_query, $bind_str_values_arr = []) {
	try {
		$pdo = new PDO('sqlite:' . $this_db); 
		$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		$statement = $pdo->prepare($this_query);
		If (!empty($bind_str_values_arr)) {
			for ($i = 0; $i < count($bind_str_values_arr); $i++) {
				$statement->bindValue($i+1, $bind_str_values_arr[$i]);
			}
		}
		if (!$statement) {
			echo "\nPDO::errorInfo():\n";
			print_r($pdo->errorInfo());
		}
		$statement->execute();
		$result = $statement->fetchAll(PDO::FETCH_ASSOC);	
	}
	catch(PDOException $e) {
		print 'Exception : '.$e->getMessage() . ' ' . $bind_str_values_arr[0] . "<br/>";
	}
	return $result;	
}

$this_db is a string “somepath/somefilename.sqlite”
* the filename must end with “.sqlite”
$this_query is a string with sequentially numbered placeholders. eg VALUES (?1, ?2, ?3, ?4, ?5) - if any
$bind_str_values_arr is the array of values - no keys specified - eg ["val1", "val2"]
* the number of placeholders in a query and the number of array members must match

I don’t recall why I had the echo and print_r stuff like that, last I used the code I was in troubleshooting mode.

[Gandalf]

Thanks @Mittineague although I’m not sure where this function goes, particularly as I’ve not been ab;e to download the latest version of @John_Betong’s script…

[John_Betong]

Hi @gandalf458

Hello John
I saw in another post that you had updated your script to PDO. I started working on changing it to SQLite with PDO and haven’t got too far, so I thought I would download the new version but the download link doesn’t seem to be working.

I have rectified the link, please try again.

I am on a mobile at the moment and just had a horrible thought that the Php 7 optimisations will have to be disabled for Php 5.

Is it better to code for Php 5?

[Gandalf]

Thanks squire. I’ve downloaded the new version and will take it for a spin when I get a chance. A bit of a madhouse right now…

[Mittineague]

@Gandalf

not sure where this function goes

You don’t need to write a function. I did that to keep things DRY
eg.

$create_query = "CREATE TABLE ....
$insert_query = "INSERT INTO ....
$insert_params = ["val1", .....
$update_query = "UPDATE TABLE ....
$update_params = ["foo", .....

Then it’s a simple matter of calling the function passing in the desired variables rather than rewriting the block of database code over again for each one

run_sql_query($this_db, $create_query);
run_sql_query($this_db, $insert_query, $insert_params);
run_sql_query($this_db, $update_query, $update_params);

I don’t write functions (or classes) for everything, but when I find myself writing the same or closely similar code over and over again I consider doing so and often do.

eg. note John’s “var_dump” function