Solved

PHP and MySQL Live Search

Posted on 2014-03-04
5
690 Views
Last Modified: 2014-03-06
This is a perfect example of what I'm trying to do except with MySQL instead of a xml document.  Can you guys help me doctor up the PHP file to do this from a MySQL db?  I know how to query a MySQL db and return results to a table, but I'm not having any luck with getting it to go to a text box while user is typing.

Perfect example:
http://www.w3schools.com/php/php_ajax_livesearch.asp
0
Comment
Question by:pwtucker
  • 3
  • 2
5 Comments
 
LVL 22

Expert Comment

by:Kim Walker
ID: 39904542
Are you getting an error? The PHP file doesn't have anything to do with putting the results in the text box. The javascript in the top code box does that. Technically there isn't a text box in this example, but I think you're referring to the div that displays the results.

The ajax request is sent to the PHP file which in this example searches an XML file. In your case that PHP file would query a database. You've expressed your ability to handle that part.

When the ajax receives it's response, that response is inserted into the div's innerHTML using the javascript showResults function. Is that where you're having trouble?
0
 
LVL 1

Author Comment

by:pwtucker
ID: 39904626
I don't know how to take results from sql and return it to the textbox that would allow the user to select to populate text box.   There is a text box in the example above unless I really have issues :)  Maybe the results are going to the div but I don't know how to populate the div with a dataset that would be selectable like example.

<form>
<input type="text" size="30" onkeyup="showResult(this.value)">
<div id="livesearch"></div>
</form>
0
 
LVL 22

Expert Comment

by:Kim Walker
ID: 39904830
This is a three step process.

1. AJaX request

Javascript, which is in the top code window in the example, sends a request to a php file using AJaX. The request is based on the value of the text field and is requested each time a key is released in that field.

2. PHP processes request

The PHP file collects the results of that request and returns it. In this case that is done with a simple "return" of a string which contains a series of html tags and content. If you have ever generated a dynamic web page using PHP and MySQL, it's the same principle. Instead of echoing the dynamic html to the browser, you echo it to the AJaX request. No part of the PHP file is any different except that in this case you're only echoing a small portion of html, not the entire page.

3. Javascript processes AJaX response

The html which is echoed by the PHP file is then received as the AJaX response and inserted in the <div id="livesearch"><div> by javascript.
0
 
LVL 1

Author Comment

by:pwtucker
ID: 39904936
Well maybe I don't know what I'm doing.  Below is what I have and normally would echo the results to an html table or div or something where I have  $hint=$row['LightNumber']; and close the db connection after.  

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

while($row = mysql_fetch_array($result))
{
     
        $hint=$row['LightNumber'];
       
}

if ($hint=="")
  {
  $response="no suggestion";
  }
else
  {
  $response=$hint;
  }

//output the response
echo $response;
?>
0
 
LVL 22

Accepted Solution

by:
Kim Walker earned 500 total points
ID: 39906222
In the above code, the $hint variable is overwritten each time the while loop executes, so the echo $response will only echo the last iteration. To append each "LightNumber" you'd need to insert a period to concatenate each iteration.
$hint .= $row['LightNumber'];

Open in new window

However, this would only return a long string of runon LightNumbers.

What do you hope to do with the list of LightNumbers? If you simply want to display a list, you could wrap each LightNumber in a p tag.
$hint .= '<p>'.$row['LightNumber']."</p>\n";

Open in new window

Remember this will be inserted in a div tag, so you need to generate html.

Or you could generate an ordered list
$hint = "<ol>\n";
while($row = mysql_fetch_array($result))
{
    $hint .= '<li>'.$row['LightNumber']."</li>\n";
}
$hint .= "</ol>\n";

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
What does GoogleTagMgr javascripts below do 5 35
How do uses indexes to maximize MySQL Searches 14 55
mysql db 3 69
Wordpress Only run code if on a certain page 11 23
Developer portfolios can be a bit of an enigma—how do you present yourself to employers without burying them in lines of code?  A modern portfolio is more than just work samples, it’s also a statement of how you work.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question