As has been pointed out - the method used so far is at risk of sql injection so before getting committed to using the now deprecated mysql suite of functions you would be wise to read up on and implement mysqli which, when you employ prepared statements will offer significant protection from malevolent sql injection attacks.
As your ajax query is being sent to the same page ( by the looks of code posted ) one important thing to do is exit from the phpafter sending the response - otherwise you end up sending the entire page ( which would also be badly formed as there would be content outside the html tags ) and I suspect this is not your desired goal.
The ajax function looks, to my untrained eye, ok but as I don't use jQuery I might well be wrong and have missed something important.
<?php
/*
as the rest of the page doesn't use a db connection,
only load the db conn if the page is requested via post
*/
if( $_SERVER['REQUEST_METHOD']=='POST' ){
/* assign db connection to a variable */
$conn=mysql_connect("localhost","root","") or die ("could not connect");
mysql_select_db("reg") or die ("could not find db");
/* empty() does an implied `isset` */
if ( !empty( $_POST['search_term'] ) ) {
$search_term = mysql_real_escape_string( $_POST['search_term'] );
/*
You ought to look at using mysqli ( prepared statements )
rather than the now deprecated `mysql_*` functions
*/
$query = mysql_query( "SELECT `ingName` FROM `ing` WHERE `ingName` LIKE '$search_term%'", $conn );
if( $query ){/* only send response if the query succeeds */
while( $row = mysql_fetch_assoc( $query ) ) {
echo '<li>',$row['ingName'],'</li>';
}
}
}
mysql_close( $conn );
/* make sure that the rest of the page is not sent back with the response data */
exit();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Gotta have a title!</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
<script type="text/javascript">
$( document ).ready( function() {
$('.searchFunction').keyup( function( event ) {
/* I could not get this to work - I don't know what this is doing as I don't use jQuery */
/*var search_term = $(this).attr('value');*/
/* this however does work */
var el=event.target || event.srcElement;
var search_term=el.value;
/* maybe better to search after a few letters have been added? */
if( search_term.length < 2 )return;
/* it appears you are posting to the same page */
$.post( document.location.href, { search_term:search_term }, function( data ) {
$('.result').html( data );
$('.result li').click( function( event ) {
var result_value = $(this).text();
$('.searchFunction').attr('value', result_value );
$('.result').html('');
});
});
});
});
</script>
</head>
<body>
<div class="container">
<input type="text" name='search_term' class="searchFunction">
<input type="submit" value="Search">
<div class="dropdown">
<ul class="result"></ul>
</div>
</div>
</body>
</html>
Full, working example
<?php
if( $_SERVER['REQUEST_METHOD']=='POST' ){
if ( !empty( $_POST['search_term'] ) ) {
$dbhost = 'localhost';
$dbuser = 'root';
$dbpwd = 'xxx';
$dbname = 'xxx';
$db = new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );
/* using lower() helped account for vagueries in spelling */
$sql='select * from `maps` where
lower( `location_name` ) like lower( "%'.$_POST['search_term'].'%" );';
$res=$db->query( $sql );
if( $res ){
while( $rs=$res->fetch_object() ){
echo "<li>".$rs->location_name."</li>";
}
}
}
exit();
}
?>
<!doctype html>
<html lang='en'>
<head>
<title>Gotta have a title!</title>
<script src='https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js'></script>
<script type='text/javascript'>
$( document ).ready( function() {
$('.searchFunction').keyup( function( event ) {
var search_term=this.value;
/* maybe better to search after a few letters have been added? */
if( search_term.length < 5 )return;
/* it appears you are posting to the same page */
$.post( document.location.href, { search_term:search_term }, function( data ) {
$('.result').html( data );
$('.result li').click( function( event ) {
var result_value = $(this).text();
$('.searchFunction').attr( 'value', result_value );
$('.result').html('');
});
});
});
});
</script>
</head>
<body>
<div class='container'>
<input type='text' name='search_term' class='searchFunction'>
<input type='submit' value='Search'>
<div class='dropdown'>
<ul class='result'></ul>
</div>
</div>
</body>
</html>
