digital-ether
Nearly a Posting Virtuoso
Moderator
1,293 posts since Sep 2005
Reputation Points: 461
Solved Threads: 101
Have something to say? Contribute New Article Reply to this Article
Live Search Box - Access Database
I am currently trying to create a live search box that as I type it will start displaying results that match.
Here is an example http://www.w3schools.com/php/php_ajax_livesearch.asp?output=print but it retrieves its data from an XML file, I need mine to retrieve from a column called FirstName within a table of an Access database.
Any help would be much appreciated.
Below are the two files I have created so far, which might be completely wrong.
Server Code: livesearch.asp
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Open "TestDB"
Set RS = conn.Execute( "SELECT FirstName FROM Table WHERE FirstName like '" & Request("q") & "%'")
Response.Write RS.getString( )
RS.Close
conn.Close
%>
HTML File:Test.html
<html>
<head>
<script type="text/javascript">
function showResult(str)
{
if (str.length==0)
{
document.getElementById("livesearch").innerHTML="";
document.getElementById("livesearch").style.border="0px";
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("livesearch").innerHTML=xmlhttp.responseText;
document.getElementById("livesearch").style.border="1px solid #A5ACB2";
}
}
xmlhttp.open("GET","livesearch.asp?q="+str,true);
xmlhttp.send();
}
</script>
</head>
<body>
<form>
<input type="text" size="30" onkeyup="showResult(this.value)">
<div id="livesearch"></div>
</form>
</body>
</html>The server side language has been changed to ASP, only because I found a this thread where someone else was also trying to achieve the same results.
http://www.aspmessageboard.com/showthread.php?t=229856
I'm happy to use any language required to get this to work. Just thought I would give this section of the forum a try as the original link I posted was using PHP as the server side.
I'd advise using PHP if you post here, and you can post your ASP version in the ASP forum. :)
With PHP, you'll have to connect to the database
http://php.net/manual/en/function.mysql-connect.php
Then make the search query:
http://php.net/manual/en/function.mysql-query.php
Retrieve the results of the query:
http://www.php.net/manual/en/function.mysql-fetch-array.php
or
http://www.php.net/manual/en/function.mysql-fetch-assoc.php
Then display your results as HTML.
The JavaScript portion takes care of retrieving your PHP page, and adding the HTML send by your PHP page to the DIV of id "livesearch".
ie:
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("livesearch").innerHTML=xmlhttp.responseText;
document.getElementById("livesearch").style.border="1px solid #A5ACB2";
}
}
You may want to do some research on XMLHttpRequest if you are not familiar with it.
:)
digital-ether
Nearly a Posting Virtuoso
Moderator
1,293 posts since Sep 2005
Reputation Points: 461
Solved Threads: 101
Thanks for your response, but it still leaves me a little confused as to what to do from here. Here is the PHP code I have created which currently pulls all users first name out of the DB and display then on screen. Ignore the print commands, I was just using them to make sure data was being retrieved.
The issue being I can't figure out how to create a textbox using the code in my early post to search and narrow the results. E.g if I type 'jo' it would bring back a live list of names begining with 'jo' then I want to be able to click on a particlur user and have their details displayed.
Can anyone help me with this? I'm learning this as I go along.
<?php
$conn=odbc_connect("Test","" ,"");
print('<html>');
print('<head>');
print('<title>PHP and MS ACCESS</title>');print('</head>');
print('<body>');
print('<table align="center" width="90%">');
if($conn)
{
$sql="select * from Users where [FirstName] like '%'";
$row=odbc_exec($conn, $sql);
while(odbc_fetch_row($row))
{
$FirstName=odbc_result($row,1);
print('<tr><td>'.$FirstName.'</td></tr>');
}
}
print('</table>');
print('</body>');
print('</html>');The JavaScript in the page should do that:
<html>
<head>
<script type="text/javascript">
function showResult(str)
{
if (str.length==0)
{
document.getElementById("livesearch").innerHTML="";
document.getElementById("livesearch").style.border="0px";
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("livesearch").innerHTML=xmlhttp.responseText;
document.getElementById("livesearch").style.border="1px solid #A5ACB2";
}
}
xmlhttp.open("GET","livesearch.asp?q="+str,true);
xmlhttp.send();
}
</script>
</head>
<body>
<form>
<input type="text" size="30" onkeyup="showResult(this.value)">
<div id="livesearch"></div>
</form>
</body>
</html>
The input field has an onkeyup event handler attached to it:
<input type="text" size="30" onkeyup="showResult(this.value)">
This will fire the showResults() function every time a key is pressed while focus is on the textbox (writing in the textbox).
showResults() will then grab the PHP page from the server and display the results in
<div id="livesearch"></div>
Do you have Firefox? When working with Javascript it is good to have Firefox and Firebug. Firebug is about the best (browser based) JavaScript debugger out there.
digital-ether
Nearly a Posting Virtuoso
Moderator
1,293 posts since Sep 2005
Reputation Points: 461
Solved Threads: 101
No I don't have Fixfox install but will download it along with Firebug to help with debugging.
Can you explain what the following code does?
xmlhttp.open("GET","livesearch.asp?q="+str,true);
xmlhttp.send();
Does it search the PHP page for the variable 'q' and change the contents it it to what has been typed in the textbox? If so I think the problem might actually lie within the PHP page with the select from command as it returns all results regardless of what I type in the textbox. Can you assist with this? Thanks
PHP Syntax (livesearch.php)
<?php
$conn=odbc_connect("Test","" ,"");
print('<html>');
print('<head>');
print('<title>PHP and MS ACCESS</title>');print('</head>');
print('<body>');
print('<table align="center" width="90%">');
if($conn)
{
$sql="select * from Users where [FirstName] like '%'";
$row=odbc_exec($conn, $sql);
while(odbc_fetch_row($row))
{
$FirstName=odbc_result($row,1);
print('<tr><td>'.$FirstName.'</td></tr>');
}
}
print('</table>');
print('</body>');
print('</html>');It would really help you if you looked into XMLHttpRequest. There are a lot of tutorials and examples online to guide you. It is the basis of "AJAX".
xmlhttp.open("GET","livesearch.asp?q="+str,true);
That just opens a HTTP connection to the URL given: livesearch.asp?q="+str
The q is the HTTP query parameter that holds the search you typed in. This is what is passed to PHP, and PHP should base it's response on.
xmlhttp.send();
Will send the HTTP request through the HTTP connection opened.
The
xmlhttp.onreadystatechang
e is a function that will be called, then the HTTP response/reply changes state.
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("livesearch").innerHTML=xmlhttp.responseText;
document.getElementById("livesearch").style.border="1px solid #A5ACB2";
}
}
The above code listens for the HTTP response, and when it receives the full response with a successful HTTP status (200) it writes the HTML received in the HTTP request to the div with id="livesearch".
digital-ether
Nearly a Posting Virtuoso
Moderator
1,293 posts since Sep 2005
Reputation Points: 461
Solved Threads: 101
This question has already been solved
You
© 2012 DaniWeb® LLC
