PHP/MYSQL/AJAX - Live Search Using MYSQL Results

Flames · October 7, 2008

Well i found an amazing tutorial on how to make a php/ajax live search the only problem is it doesn't tell me how to use MySQL rather xml documents which won't work for me. the tutorial http://www.w3schools.com/PHP/php_ajax_livesearch.asp

basically all im asking is for a way to change the xml document to search for MySQL records, but i also want some special additions, titles for sections e.g. News or Members that are bold and when click going to a search page with more results of that topic. and below them the proper results from the mysql table to be shown e.g. for members it would show the members underneath in alphabetical order. i also want certain sections to only be searched if the user is logged in e.g. members but i think i can achieve this myself. i also do not want it to do exact searches e.g. some live searches i have seen will give you hello only if you type in hello (hello minus characters from the end e.g. hell) but i want approximate searches e.g. helo would show hello unless better results were found.

F1Fan · October 7, 2008

http://www.w3schools.com/PHP/php_ajax_database.asp

Flames · October 7, 2008

i didnt notice that but could you help me with my other problems?

F1Fan · October 7, 2008

I'm sorry, but as hard as I try, I cannot understand what you're asking for. I'm not a grammar Nazi or anything, but I just can't get through your paragraph and comprehend what you want. I does look like you want several things, so try listing them out rather than just dumping it all into one long paragraph.

I'm not trying to be mean or anything, I just want to understand what you want. You may read that and understand it, but you wrote it. I want to help.

Flames · October 7, 2008

Sorry but i was trying to type in a bit of a rush let me clarify.

1. An approximate search rather than an actual search so hello can be found as helo unless a better alternative is found. so hello would take priority over hello. same for multiple words. hello world has preference over helo world which has preference over helo word.

2. Sectioned live search. So for example 3 sections. News, Blogs and Members which would be in bold text (with number of results). if no results are found it is in italics. live search results would look similar to.

News 2

news1 (note alphabetical order)

news2

Members 3

member1

member2

member3

Blog Post

The titles in bold would lead to a search page e.g. search.php?type=something, where something is a title e.g. news/members/blogs and would give a full list of results found in that section.

3. Some sections only shown when logged in e.g. Members.

new addition 4. if neeeded a scroll bar to view more than 15 results by scrolling down

Hope that helps

F1Fan · October 7, 2008

That does clarify quite a bit, thank you. I actually don't know how to search for "close" results. I do searches using SQL's "ILIKE" and using the wild character "%" to find results.

As far as sorting them, that should be pretty easy. Just make sure your ORDER BY clause is set appropriately. Then, to sort within sections, I just posted something similar to what you're wanting here: http://www.phpfreaks.com/forums/index.php/topic,219978.msg1008500.html#msg1008500 (except that one used categories).

For #3, you need to add logic that checks for the user's logged-in status. Finally, for #4, if results extend beyond the bottom of the page, it will get an automatic scroll bar on the page, unless it's a JavaScript-controlled pop-up window. If it's in a <div> and you want scroll bars, you'll need to use CSS "display:scroll;" to add them.

Flames · October 10, 2008

Here is my basic ajax search code that only searches members but it won't work i cant find any errors.

Form (search.php)

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

ajax (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;
}

php (searchresult.php)

<?php
$q=$_GET["q"];
require("http://www.website.com/config.php");
error_reporting(E_ALL);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db($db, $con);

$sql="SELECT * FROM Account WHERE username = '".$q."'";

$result = mysql_query($sql);

echo "<table border='1'>
<tr>
<th>ID</th>
<th>Username</th>
<th>Email Address</th>
<th>Rank</th>
<th>Status</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['username'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "<td>" . $row['rank'] . "</td>";
echo "<td>" . $row['status'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysql_close($con);
?>

F1Fan · October 10, 2008

First, change "onchange" to "onmouseup." Then, look at your error console for JS errors and list those errors.

Flames · October 10, 2008

i did that (presuming you meant the form) the error console is empty all that happens is the div changes to nothing and it wont come up with anything even if i put the username exactly. im connecting to the database because no errors show up.

table layout is pretty easy. (name is Account)

1 | Flame | ******** | ********* | 0 | (unrequired) | 9

2 | Tester | ********* | ********* | 0 | (unrequired) | 1

etc

if i put F in i get no results if i put Flame in i get no results.

F1Fan · October 10, 2008

I think you need the xmlHttp=GetXmlHttpObject() outside of your function. I've never used it like that.

Flames · October 10, 2008

tried it, the search still has the exact same problem

maybe you want to try it on your own website/localhost to see if it works?

F1Fan · October 10, 2008

You need to troubleshoot more. Try manually navigating to your ajax php page and giving it the parameters that it needs. See what you get. Try alerting a message in your JS to see if it's doing what it's supposed to.

Flames · October 10, 2008

i changed onmouseup to onkeyup and added 3 alert messages to the search.js

var xmlHttp
xmlHttp=GetXmlHttpObject()

function searchUser(str)
{
if (xmlHttp==null)
{
alert ("Browser does not support HTTP Request")
return
}
var url="searchresult.php"
url=url+"?q="+str
url=url+"&sid="+Math.random()
alert ("Searching " + url) //alert1
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
alert ("state ready state complete")//alert2
}
alert ("state ready not 4/complete")//alert3
}
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;
}

when i put in a letter heres the order things happen

alert1

alert3

div dissapears

alert2

alert3

then the alert messages stop

help?

F1Fan · October 10, 2008

Ah, sorry, that's my bad on the onmouseup part. Not sure what I was thinking. I meant onkeyup.

OK, now we're getting somewhere! Your code is actually working, it's just going too far. It's continuing after it's complete, and I believe overwriting what it put in your div before you can actually see it.

What happens when you navigate to the ajax php page manually?

Flames · October 10, 2008

when i go to the search.js page it just shows a lot of text (anyway to hide it from other users), and when i go to searchresults.php nothing happens even if i put searchresults.php?q=flame or q=Flame or anything so is it a mysql error

F1Fan · October 10, 2008

If you are getting an error when navigating to searchresults.php?q=flame, it sound like you have some problems to work out.

Flames · October 10, 2008

i dont get an error when i go to searchresults.php i just dont get anything no matter what q is

F1Fan · October 10, 2008

You should always add "or die(mysql_error()); after your mysql_query. Also, you're using mysql_fetch_array, when you should be using mysql_fetch_assoc.

Flames · October 10, 2008

i added this and nothing happens

there is no output at all

<?php
$query = $_REQUEST['q'];
include("http://www.website.com/config.php");


mysql_select_db($db, $con) or die("error connecting");

$sql="SELECT * FROM Account where username = '.$query.'";
echo $sql;
$result = mysql_query($sql) or die(mysql_error());
echo $result;
echo "<table border='1'>
<tr>
<th>ID</th>
<th>Username</th>
<th>Email Address</th>
<th>Rank</th>
<th>Status</th>
</tr>";

while($row = mysql_fetch_assoc($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['username'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "<td>" . $row['rank'] . "</td>";
echo "<td>" . $row['status'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysql_close($con);
?>

F1Fan · October 10, 2008

Wait a sec. I just noticed your include. Is that config file on the same website? You can't include a php file from another site using include unless that file actually echos PHP code.

Also, when you view the page's source, are you seeing any table stuff? Like the header table?

Finally, get rid of your . inside your query:

$sql="SELECT * FROM Account where username = '.$query.'";

needs to be:

$sql="SELECT * FROM Account where username = '$query'";

xtopolis · October 10, 2008

This php/mysql code worked for me. Try it. I didn't touch your other page.

<?php
error_reporting(E_ALL);
  $q=mysql_real_escape_string($_GET["q"]);//added mysql_real_escape_string
require("http://www.website.com/config.php");

if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db($db, $con);

$sql="SELECT * FROM Account WHERE username LIKE '%$q%' ORDER BY username ASC";//changed it to be: username='$q'";

$result = mysql_query($sql);

echo "<table border='1'>
<tr>
<th>ID</th>
<th>Username</th>
<th>Email Address</th>
<th>Rank</th>
<th>Status</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['username'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "<td>" . $row['rank'] . "</td>";
echo "<td>" . $row['status'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysql_close($con);
?>

Flames · October 11, 2008

it seems there were multiple problems with the code.

a: the direct url seemed to fail e.g. http://www.website.com/config.php, so i copied it to scrits/config.php and changed the require to config.php and it worked

b: $q uses mysql_real_escape_string and gave me an error seeing as mysql wasnt connected.

but overall the code works so my thanks go to both of you.

jbonnett · May 1, 2012

Use "SELECT * FROM Account WHERE username RLIKE '%$q%' ORDER BY username ASC" the RLIKE (regular expression like) finds results more easily

jbonnett · May 1, 2012

also try this i think you might like it

<html>
<head>
<script src="search.js"></script>
<title>
Search
</title></head>
<body onload="document.getElementById('search').focus();">
<form>
Search: <input type="text" name="search" id="search" onkeyup="searchUser(this.value)" onfocus="searchUser(this.value)" value="<? echo $_REQUEST['search']; ?>">
</form>
<div id="searchresult" name="searchresult"> Search results ...</div>
</body>
</html>

Maq · May 1, 2012

jbonnet, the last reply was from October 11, 2008. Please don't revive really old threads like this.

Sign In

PHP/MYSQL/AJAX - Live Search Using MYSQL Results

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Important Information