AJAX搜索栏使用MYSQL数据库

Question

I am trying to make a user search bar on my webpage using ajax. I want to return values straight from my mysql database instead of using a xml file. This is because I don't want to have to keep updating my xml file everytime a new user is registered on the site. I am new to AJAX and have ran into a snag. Right now my code works if I hard code values into the query. But how do I get the value from the search bar into the query and make the code only run when a value is entered into the search bar. Here is what I have.

I have two files, index (where the search bar and php code are) then a javascript file for the ajax code.

Search Bar Code:

            <form method="post" name="searchForm">
                <input type="text" size="40" placeholder="Search for people" onkeyup="getResults(this.value)" />
                <div id="search">
                    <?php
                        $searchString = $_POST['str'];  
                        $sql = "SELECT name FROM User WHERE name LIKE '?%'";
                        $query = $conn->prepare($sql);
                        $query->execute(array($searchString));
                        $row = $query->fetchAll();
                        echo "<ul>";
                        foreach ($row as $rs) 
                        {
                            echo "<li><a href='#'>" . $rs['name'] . "</a></li>";
                        }
                        echo "</ul>";
                    ?>
                </div>
            </form>

Javascript/AJAX Code:

function getResults(str)
{
    if (str.length == 0)
    { 
        document.getElementById("search").innerHTML="";
        document.getElementById("search").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("search").innerHTML = xmlhttp.responseText;
            document.getElementById("search").style.border="1px solid #A5ACB2";
        }
    }
    xmlhttp.open("POST","index.php?str="+str,true);
    xmlhttp.send();
}

What am I doing wrong here? And what is a way that will allow me to only run the php code once the user starts typing in the search bar besides using a submit button. Thanks.

Answer 1

First of all, separate the actual page (PHP script rendering the HTML) and the code that returns the actual search results. Currently the PHP code you have after your <input> is executed at the time the page is rendered, it will never know what the user entered in your search box.

Your search script should return JSON or XML. Currently it loads the page designed for the users with the full HTML. This is just a waste of resources and bandwidth.

In order to fix your SQL statement, the ? character must stand for the whole term of your LIKE expression.
Example:

$searchString = $_POST['str'] . '%';
$sql = "SELECT name FROM User WHERE name LIKE ?";
$query = $conn->prepare($sql);
$query->execute(array($searchString));

There is even more stuff to fix, but this should help you where to start...