Help with Ajax and PHP search autofill

Lostnode · October 27, 2010

Ok, this is stumping my mind. I have a PHP/AJAX script which works almost perfectly. But its lacking one detail I absolutely need. Every AJAX demo I have seen is missing it or doesn't work right.

Here is where the problem is.

I have a list of countries, I want the user to spell it right when using my search, so you put in say "Virgin Islands"

It should pull up the following in the list:

Virgin Islands (British)

St. Thomas (U.S. Virgin Islands)

However all I get is:

Virgin Islands (British)

It has to do with the MySQL Query...

Here is what came with the demo:

$query = sprintf("SELECT ctry_name  
FROM international 
WHERE ctry_name '%s%%'  
GROUP BY ctry_name",
$ctry_name);

That's what pulls up only Virgin Islands

I tried to mod the query to be

"SELECT ctry_name FROM interntaional WHERE ctry_name LIKE '%" . $ctry_name . "%'"

But it breaks the script, in other AJAX Examples, it doesn't even look it up and returns all entries in the database.

Like I said the above example works fine, it just returns just the one value, I want ti to return all variables.

darkfreaks · October 27, 2010

why does it need to be changed first off and second post the full script and more detail. :confused:

darkfreaks · October 27, 2010

what about the following script?

http://www.devbridge.com/projects/autocomplete/jquery/

seems to work and its fully uses Jquery and Ajax.

Lostnode · October 27, 2010

why does it need to be changed first off and second post the full script and more detail.

It needs to change so that when a User Types Virgin Islands, It comes up with both:

Virgin Islands (British)

St. Thomas (U.S. Virgin Islands)

Not just Virgin Islands (as stated above in my original post.)

The link you gave does the same thing, its almost what I want, just it takes the first part of what you typed in, I.E. When I type the letter V I get only what starts with V, not what contains a V.

If I type V I should get everything that starts with a V, ends with a V and contains a V somewhere within.

% $variable % is supposed to work according to a whole lot of forums out there, however it doesn't seem to work with Ajax.

Here are the 4 original files (unedited) that I picked up.

ajax-test.php

<html>
<head>
<title>AJAX testing</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript" type="text/javascript" 
	src="JsonRequest.js"></script>
<script>

//slow down the event handler...
var searching;
function doRequestTimeout(dat){
	clearTimeout(searching);
	searching = setTimeout('doRequest("'+dat+'")', 300);
}

//display data returned by ajax...
function display(data){
	var displayDiv;
	displayDiv = document.getElementById("cityList");

	//set to visible if not already
	displayDiv.style.display = 'block';
	displayDiv.innerHTML = '';

	//start the unordered list
	displayDiv.innerHTML = '<ul class="cities">';

	//populate the list
	for(i in data){
		displayDiv.innerHTML += '<li><a href="#" onClick="populateTextField(\''+data[i]+'\');">'+data[i]+'</a></li>';
	}

	//end the unordered list
	displayDiv.innerHTML += '</ul>';
}

//declare url for json requests...
var url = 'ajax-query.php'

//create json request object
//arguments own name, callback function to process data once request has been made
var json = new JsonRequest('json', new Function('data', 'display(data)'));

//function to be called to get the results from the server
function doRequest(text){
	json.makeRequest(url+"?city="+text);
}

//populate the text field with the result that was clicked on
function populateTextField(datum){
	document.getElementById("cityField").value = datum;
	document.getElementById("cityList").style.display = 'none';
}

</script>
<style>


#cityList ul{
	margin: 0px;
	padding: 0px;
}

#cityList li{
	display:block;
}

#cityList li a {
	border-bottom: 1px solid #fff;
	display: block;
	padding-left: 10px;
	font-family: arial;
	font-weight: bold;
	font-size: 11px;
	text-decoration: none;
	background: #B3B3B5;
	color: #fff;
}

#cityList li a:hover {
	background: #37445E;
}
</style>
</head>

<body>

<input type="text" name="city" id="cityField" onKeyUp="doRequestTimeout(document.getElementById('cityField').value);">

<div id="cityList" style="width:360px;"></div>

</body>
</html>

ajax-query.php

<?
//######################################################################
//connect to the database etc.

## Database Connection
define(DB_HOST, "localhost"); 
 # DB Username
define(DB_USER, "");
 # DB password
define(DB_PASS, "");
 # DB to select - Database Name
define(DB_NAME, "dbname");

//mysql_connect(DB_HOST, DB_USER, DB_PASS) or die(mysql_error());

//mysql_select_db(DB_NAME);

//returns array of database result
function db_num_ajax($sql){

	$q = mysql_query($sql) or die(mysql_error());
	$result = array();

	while($row = mysql_fetch_array($q, MYSQL_NUM)){
		array_push($result, $row);
	}

mysql_free_result($q);
return $result;
}
//######################################################################

include_once('Services_JSON.php');

//make json object...
$json = new Services_JSON();

//get array of data
//$data = getData($_GET);
$data = getData($_GET[city]);

//send no-cache header (otherwise IE caches ajax requests)...
header("Cache-Control: no-cache");

//encode and return json data...
echo $json->encode($data);

function getData($city){
	$query = sprintf("SELECT debtors_city  
						FROM debtors 
						WHERE debtors_city LIKE '%s%%'  
						GROUP BY debtors_city",
						$city);
	$result = db_num_ajax($query);
	return $result;

}


?>

JsonRequest.js

function JsonRequest(mn,callback){
this.mn = mn;
this.cb = callback;
this.rObj = this.makeObj();
};

JsonRequest.prototype.makeRequest = function(url,p){
// return false if no XHR object, so that form/link whatever will know to submit manually
if (!this.rObj) return false;
if (!p) p = false;

// set up the request
if (p){
	this.rObj.open('POST', url, true);
	this.rObj.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
	this.rObj.setRequestHeader("Content-length", p.length);
	this.rObj.setRequestHeader("Connection", "close");
}
else this.rObj.open('GET', url, true);

// set the callback function
if (this.cb) this.rObj.onreadystatechange = new Function('if ('+this.mn+'.rObj.readyState == 4) '+this.mn+'.cb(eval("("+'+this.mn+'.rObj.responseText+")"));');
else this.rObj.onreadystatechange = function(){return};

// send the request
this.rObj.send(p ? p : null);

// return true- TODO: return false if request somehow fails...
return true;
};

JsonRequest.prototype.makeObj = function(){
if (window.XMLHttpRequest) // Mozilla, Safari,...
	r = new XMLHttpRequest();
  else if (window.ActiveXObject) { // IE
    try {
        r = new ActiveXObject("Msxml2.XMLHTTP");
    } catch (e) {
      try {
          r = new ActiveXObject("Microsoft.XMLHTTP");
      } catch (e) {}
  }}
return r ? r : false;
};

Lostnode · October 27, 2010

The 4th is attached, too big to type out, and as I can tell has no bearing on the output other than mine tweaking.

Again, I need it to pull entries that CONTAIN the search parameter and not START WITH the search parameter... THis is why it needs to be changed.

[attachment deleted by admin]

BlueSkyIS · October 27, 2010

one option: change the query to this:

$city = mysql_real_escape_string($city);

$query = "SELECT debtors_city  
						FROM debtors 
						WHERE debtors_city LIKE '%$city%'  
						GROUP BY debtors_city";

Lostnode · October 27, 2010

one option: change the query to this:

$city = mysql_real_escape_string($city);

$query = "SELECT debtors_city  
						FROM debtors 
						WHERE debtors_city LIKE '%$city%'  
						GROUP BY debtors_city";

DING DING DING, WE HAVE A WINNER - Thank you very very much, this works like a charm.

Sign In

Help with Ajax and PHP search autofill

Recommended Posts

Lostnode

Link to comment

Share on other sites

darkfreaks

Link to comment

Share on other sites

darkfreaks

Link to comment

Share on other sites

Lostnode

Link to comment

Share on other sites

Lostnode

Link to comment

Share on other sites

BlueSkyIS

Link to comment

Share on other sites

Lostnode

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information