Searching mySQL Database with Ajax

Question

I'm attempting to do an AJAX search to return data from a mySQL Database and then update a div on the page with the returned info. I followed along with a tutorial (tailoring it to my needs as I went) and, so far, it appears to do nothing but append the search query to the address. Any help would be greatly appreciated, thanks!

HTML

<html>
<head>
<script>
function searchDB(str) {
  if (str=="") {
    document.getElementById("txtHint").innerHTML="";
    return;
  } 
  if (window.XMLHttpRequest) {
    // code for IE7+, Firefox, Chrome, Opera, Safari
    xmlhttp=new XMLHttpRequest();
  } else { // code for IE6, IE5
    xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
  xmlhttp.onreadystatechange=function() {
    if (xmlhttp.readyState==4 && xmlhttp.status==200) {
      document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    }
  }
  xmlhttp.open("GET","search4.php?q="+str,true);
  xmlhttp.send();
}
</script>
</head>
<body>

<form>
<input type="text" name="q" placeholder="Search Database..." onsubmit="searchDB(this.value)">
</form>
<br>
<div id="txtHint"><b>Search will be returned here.</b></div>

</body>
</html>

PHP

<?php
$q = ($_GET['q']);

$con = mysqli_connect('localhost','username','pass','database');
if (!$con) {
  die('Could not connect: ' . mysqli_error($con));
}

mysqli_select_db($con,"SearchTest");
$sql="SELECT ID, FirstName, LastName FROM SearchTest WHERE FirstName LIKE '%" . $q ."%' OR LastName LIKE '%" . $q . "%'";
$result = mysqli_query($con,$sql);

echo "<table border='1'>
<tr>
<th>First Name</th>
<th>Last Name</th>
</tr>";

while($row = mysqli_fetch_array($result)) {
  echo "<tr>";
  echo "<td>" . $row['FirstName'] . "</td>";
  echo "<td>" . $row['LastName'] . "</td>";
  echo "</tr>";
}
echo "</table>";

mysqli_close($con);
?>

Only appends to the address bar but nothing being printed on screen? Try adding error reporting to the top of your file(s) `error_reporting(E_ALL); ini_set('display_errors', 1);` see if it yields any errors. — Funk Forty Niner, Jul 06 '14 at 01:27

Funk Forty Niner · Accepted Answer · 2014-07-06T03:12:59.947

The problem is most likely with this line mysqli_select_db($con,"SearchTest"); delete it, since you're probably getting mixed up with your table being of the same name
SELECT ... FROM SearchTest.

I'm next to 100% certain that's the problem, since you've already selected the DB with ('localhost','username','pass','database')

Adding error reporting to the top of your file(s)

error_reporting(E_ALL);
ini_set('display_errors', 1);

will more than likely show you that a DB (of that name SearchTest) doesn't exist.

EDIT

TBH, I couldn't make your existing code work and I have no idea why. (Nota: See comments, solved)

This however, does work:

search.php

<html>
<head>
<script src="search.js"></script>
<title>
Search
</title></head>
<body>
<form>
Search: <input type="text" name="search" id="search" onmouseup="searchUser(this.value)">
</form>
<div id="searchresult" name="searchresult"> Search results ...</div>
</body>
</html>

search.js

var xmlHttp
function searchUser(str)
{
xmlHttp=GetXmlHttpObject()
if (xmlHttp==null)
{
alert ("Browser does not support HTTP Request")
return
}
var url="searchresult.php"
url=url+"?q="+str
url=url+"&sid="+Math.random()
xmlHttp.onreadystatechange=stateChanged
xmlHttp.open("GET",url,true)
xmlHttp.send(null)
}
function stateChanged()
{
if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete")
{
document.getElementById("searchresult").innerHTML=xmlHttp.responseText
}
}
function GetXmlHttpObject()
{
var xmlHttp=null;
try
{
// Firefox, Opera 8.0+, Safari
xmlHttp=new XMLHttpRequest();
}
catch (e)
{
//Internet Explorer
try
{
xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
}
catch (e)
{
xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
}
}
return xmlHttp;
}

searchresult.php

<?php 

error_reporting(E_ALL);
ini_set('display_errors', 1);

$q=$_GET["q"];
$DB_HOST = "xxx";
$DB_NAME = "xxx";
$DB_USER = "xxx";
$DB_PASS = "xxx";

$con = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($con->connect_errno > 0) {
  die('Connection failed [' . $con->connect_error . ']');
}

$sql = "SELECT ID, FirstName, LastName FROM SearchTest WHERE FirstName LIKE '%" . $q ."%' OR LastName LIKE '%" . $q . "%'";

$result = mysqli_query($con,$sql);

echo "<table border='1'>
<tr>
<th>First Name</th>
<th>Last Name</th>
</tr>";

while($row = mysqli_fetch_array($result)) {
  echo "<tr>";
  echo "<td>" . $row['FirstName'] . "</td>";
  echo "<td>" . $row['LastName'] . "</td>";
  echo "</tr>";
}
echo "</table>";

mysqli_close($con);
?>

Added the error reporting and took out the mysqli_select and now when I navigate to the php page directly it says: Notice: Undefined index: q in /x/y/public_html/z/php/search4.php on line 5. When running the script on the html page it still only appends "q?=searchterm" to the address bar. My error_log shows nothing related to any of this. — Dylan, Jul 06 '14 at 01:56
@Dylan OK, give me a few minutes, I'll see if I can find that probable error in your code. — Funk Forty Niner, Jul 06 '14 at 01:57
Thanks Fred. By the way line 5 is now this line: $q = ($_GET['q']); — Dylan, Jul 06 '14 at 01:58
You're welcome @Dylan TBH, I couldn't for the life of me, get your code to work. I did find something that does, have a look under my **EDIT**. — Funk Forty Niner, Jul 06 '14 at 02:48
@Dylan I edited my answer. This `onchange="searchUser(this.value)"` was changed to `onmouseup="searchUser(this.value)"` which may work better. But if you think that it doesn't work the way you want it to, just switch it back to `onchange` — Funk Forty Niner, Jul 06 '14 at 02:59
@Dylan You're very much welcome. Yet, I also just noticed now after fooling around with your form a bit, that the probable fix to your existing form. This line `onsubmit="searchDB(this.value)"` should have been `onchange="searchDB(this.value)"` or `onmouseup="searchDB(this.value)"` since the `onsubmit` will more than likely only work with a submit button. This, being in conjunction with the other error as pointed out originally. — Funk Forty Niner, Jul 06 '14 at 03:05

score -1 · Answer 2 · edited May 23 '17 at 12:11

-1

Always use jQuery (or any other js lib / framework) for ajax calls. jQuery is also great for manipulating the DOM (document.getElementById("txtHint").innerHTML=xmlhttp.responseText; reminds me of the 90s).

And your PHP code is highly insecure. See How can I prevent SQL injection in PHP?

edited May 23 '17 at 12:11

Community

1
1

answered Jul 06 '14 at 00:28

nehalist

1,434
18
45

Searching mySQL Database with Ajax

HTML

PHP

2 Answers2