CODEWITHSUNDEEP
phpmysqlajax
Dylan
Dylan

Reputation: 3

Searching mySQL Database with Ajax

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);
?>

Upvotes: 0

Views: 488

Answers (2)

Funk Forty Niner
Funk Forty Niner

Reputation: 74217

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);
?>

Upvotes: 2

nehalist
nehalist

Reputation: 1496

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?

Upvotes: -1

Related Questions