jQuery Autocomplete with PHP Script - Not Working!

PHP
1

I have a list of countries and abbreviations for those countries stored in an MySQL database. What I want to happen is have jQuery pull the countries from the database and use them in an autocomplete text input in my form. Unfortunately, I can’t seem to get it to work. I tested to make sure I had the correct URL for the PHP file in the jQuery, and I was able to echo the raw json that the php file created, but as soon as I use the php file name as the source in the jQuery, it stops working. Here is my folder structure:

  • people.php (Main Page w/display and jQuery)
  • Model/
    • Autocomplete/
      • countries.php

And here are my scripts… any ideas where I might be going wrong? I was on IRC jQuery, and no one seemed to be able to figure out where I was going wrong:

jQuery on people.php


$(document).ready(function(){
    $('input[name="country"]').autocomplete({
        source: "/genesis/model/autocomplete/countries.php",
        minLength: 2
    });
});

<!-- and the HTML form field -->
<div>
    <label for="country"><span>*</span> Country</label>
    <div><input type="text" name="country" value="" /></div>
</div>

countries.php


	global $dbh;

	$countries = array();

	$stmt = $dbh->prepare('SELECT name, abbr FROM countries ORDER BY name ASC');
	if ($stmt->execute()) {
		while ($row = $stmt->fetch()) {
			$countries[] = array('label' => $row['name']);
		}
	}

	echo json_encode($countries);
2

Let’s start with an understanding of what should occur.

The php script should accept the characters typed by the person, which then provides all matching values. That’s how the autocomplete list is narrowed down as you type.
When you get that part right with the php script, the rest should fall in to place naturally.

After all, as it says on the autocomplete documentation page:

The Autocomplete plugin does not filter the results, instead the request parameter “term” gets added to the URL, which the server-side script should use for filtering the results.

So it’s up to your php script to filter the results.

3

Okay, I thought I understood what the documentation says, so I changed my PHP script to the following:


	global $dbh;

	$countries = array();
	$search = $_REQUEST['term'];

	$stmt = $dbh->prepare('SELECT name FROM countries WHERE name LIKE "%:search%"" ORDER BY name ASC');
	$stmt->bindParam(':search', $search, PDO::PARAM_STR);
	if ($stmt->execute()) {
		while ($row = $stmt->fetch()) {
			$countries[] = array('label' => $row['name']);
		}
	}

	echo json_encode($countries);

But that didn’t work either. Because the term is added before anything is typed into the form field, it is throwing me an error. I’m not sure what to do here. Any more advice?

4

Okay, this is the current rendition of the code, still not working.

people.php


	$(document).ready(function(){

		// Autocomplete countries
		$('input[name="country"]').autocomplete({
			source: "/genesis/model/autocomplete/countries.php",
			minLength: 2
		})

	});

<div>
			<label for="country"><span>*</span> Country</label>
			<div><input type="text" name="country" value="" /></div>
		</div>

countries.php


	global $dbh;

	$countries = array();
	$term = '';

	if (isset($_GET['term'])) {
		$term = trim($_GET['term']);
	}

	if (isset($term)) {
		$stmt = $dbh->prepare("SELECT name FROM countries WHERE name LIKE CONCAT('%', :term, '%') ORDER BY name ASC");
		$stmt->bindParam(':term', $term, PDO::PARAM_STR);
		if ($stmt->execute()) {
			while ($row = $stmt->fetch()) {
				$countries[] = $row['name'];
			}
		}
	}

	echo json_encode($countries);
5

I’m not entirely sure, but I think I read somewhere that you have to add the wildcard char on prior to submitting it to the statement:


$term = trim($_GET['term']) . "%"; 

if (isset($_GET['term'])) { 
        $term = trim($_GET['term']); 
    } 

    if (isset($term)) { 
        $stmt = $dbh->prepare("SELECT name FROM countries WHERE name LIKE :term ORDER BY name ASC"); 
        $stmt->bindParam(':term', $term, PDO::PARAM_STR);
6

Unfortunately, this didn’t work. Here’s where I’m at so far.

I think it has something to do with the PHP file itself, because if I just type directly into the Javascript something for source:


// Autocomplete countries
		$('input[name="country"]').autocomplete({
			source: ["Albania","Australia","Algeria"],
			minLength: 2
		})

… then it works fine. I dumped what the script echoes when it queries the database, and I get the results that I want:


["Afghanistan","Albania","Algeria","Andorra","Angola","Antigua and Barbuda","Argentina","Armenia","Aruba","Australia","Austria","Azerbaijan","Bahamas, the","Bahrain","Bangladesh","Barbados","Belarus","Belgium","Belize","Benin","Bhutan","Bolivia","Bosnia and Herzegovina","Botswana","Brazil","Brunei","Bulgaria","Burkina Faso","Burundi","Cambodia","Cameroon","Canada","Cape Verde","Central African Republic","Chad","Chile","China","Colombia","Comoros","Congo, Dem. Rep. of the","Congo, Rep. of the","Cook Islands","Costa Rica",null,"Croatia","Cuba",null,"Cyprus","Czech Republic","Denmark","Djibouti","Dominica","Dominican Republic","Ecuador","Egypt","El Salvador","Equatorial Guinea","Eritrea","Estonia","Ethiopia ","Faeroe Islands","Fiji","Finland","France","Gabon","Gambia, the","Georgia","Germany","Ghana","Greece","Greenland","Grenada","Guatemala","Guernsey","Guinea","Guinea-Bissau","Guyana","Haiti","Honduras","Hungary","Iceland","India","Indonesia","Iran","Iraq","Ireland","Israel","Italy","Jamaica","Japan","Jersey","Jordan","Kazakhstan","Kenya","Kiribati","Korea, North","Korea, South","Kosovo","Kuwait","Kyrgyzstan","Laos","Latvia","Lebanon","Lesotho","Liberia","Libya","Liechtenstein","Lithuania","Luxembourg","Macedonia","Madagascar","Malawi","Malaysia","Maldives","Mali","Malta","Man, Isle of","Marshall Islands","Mauritania","Mauritius","Mayotte","Mexico","Micronesia, Fed. States of","Moldova","Monaco","Mongolia","Montenegro","Morocco","Mozambique","Myanmar (Burma)","Namibia","Nauru","Nepal","Netherlands, the","New Zealand","Nicaragua","Niger","Nigeria","Niue","Norway","Oman","Pakistan","Palau","Palestine (Occ.Terr.)","Panama","Papua New Guinea","Paraguay","Peru","Philippines","Poland","Portugal","Qatar","Romania","Russia","Rwanda","Saint Kitts and Nevis","Saint Lucia","Saint Vincent & the Grenadines","Samoa","San Marino","Sao Tome and Principe","Saudi Arabia","Senegal","Serbia","Seychelles","Sierra Leone","Singapore","Sint Maarten","Slovakia","Slovenia","Solomon Islands","Somalia","South Africa","Southern Sudan","Spain","Sri Lanka","Sudan","Suriname","Svalbard & Jan Mayen Is.","Swaziland","Sweden","Switzerland","Syria","Taiwan","Tajikistan","Tanzania","Thailand","Timor-Leste","Togo","Tokelau","Tonga","Trinidad & Tobago","Tunisia","Turkey","Turkmenistan","Tuvalu","Uganda","Ukraine","United Arab Emirates","United Kingdom","United States of America","Uruguay","USA - Puerto Rico","Uzbekistan","Vanuatu","Venezuela","Vietnam","Yemen","Zambia","Zimbabwe"]

So you would think that when I just included the script as I did in the first post where the source goes, it would work. But it doesn’t.

It is shooting me an error that doesn’t make sense…

Fatal error: Call to a member function prepare() on a non-object in /Users/barryhjames/Sites/genesis/genesis/model/autocomplete/countries.php on line 13

This would make sense to me if I wasn’t getting any result, but I am getting a result (as shown above). This is starting to get frustrating, especially because I am able to query the database and get the results I need in json, but the second I try to attach those results to the jQuery, it falls flat on it’s face.

Any other suggestions would be much appreciated!

7

That error sounds as if the globak $dbh is not being found.

Can you prove $dbh exists in that script, I mean is the include file linked up correctly?