Live Search Box - Access Database

Question

brenton_77 0 Newbie Poster

14 Years Ago

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>

microsoft-access php seo

Edited 14 Years Ago by digital-ether because: Please use [code] tags to surround your code

2 Contributors
7 Replies
621 Views
1 Day Discussion Span
Latest Post 14 Years Ago Latest Post by digital-ether

digital-ether 399 Nearly a Posting Virtuoso

14 Years Ago

What programming language are you using on the server side? I think you'll have more help in the appropriate forum for that language.

digital-ether 399 Nearly a Posting Virtuoso

14 Years Ago

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.

:)

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

brenton_77 0 Newbie Poster · Answer 1 · 2010-06-09T05:50:19+00:00

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.

brenton_77 0 Newbie Poster · Answer 2 · 2010-06-09T13:50:51+00:00

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

digital-ether 399 Nearly a Posting Virtuoso Team Colleague · Answer 3 · 2010-06-09T19:45:14+00:00

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.

brenton_77 0 Newbie Poster · Answer 4 · 2010-06-09T20:34:01+00:00

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

digital-ether 399 Nearly a Posting Virtuoso Team Colleague · Answer 5 · 2010-06-10T07:17:36+00:00

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".