在我输入时从MySQL填充多个输入字段

Question

Lots of places to look at with lots of idea how to do this but I'm not succeeding with any of them.

I have an input field called 'callsign' as you type it goes out to a MySQL table and returns suggestions based on what you have already typed. I want hints as to what the value should be to appear in the input field so I can select one of them or just keep typing. When I select the one I want I also want the Fname field to fill in, it is also returned from MySQL, see the XML below.

I really want to learn how to do this but a long explanation using terms I only sort of understand won't go nearly as far as an example. Help!

The input looks like this:

 Call Sign: <input type='text' value='' placeholder='Search' id='cs1' style='text-transform:uppercase' autofocus onkeyup='showHint(this.value)'>&nbsp;&nbsp;Name: <Input type='text' name='Fname'style='text-transform:capitalize'>

The MySQL like this:

 <?php
 require_once "dbConnectDtls.php";
 $q = $_REQUEST["q"];
 $stmt = $db_found->prepare("SELECT DISTINCT callsign, Fname
    FROM NetLog 
    WHERE recordID IN (SELECT max(recordID) 
    FROM NetLog 
    WHERE callsign LIKE ? GROUP BY callsign)"); 
 $stmt->execute(array("$q%"));
 $result = $stmt->fetchAll();
 print_r($result);
 ?>

The result of running the test MySQL above with a q = 'w' returns:

Array ( [0] => Array ( [callsign] => W0DLK [0] => W0DLK [Fname] => Deb [1]
=> Deb ) [1] => Array ( [callsign] => W0GPS [0] => W0GPS [Fname] => 
John [1] => John ) [2] => Array ( [callsign] => WA0TJT [0] => WA0TJT 
[Fname] => Keith [1] => Keith ) [3] => Array ( [callsign] => W0KCN [0] 
=> W0KCN [Fname] => Net Control [1] => Net Control ) )

And my function like this:

 function showHint(str) {
    if (str.length == 0) {
        document.getElementById("txtHint").innerHTML = "";
        return;
    } else {
        var xmlhttp = new XMLHttpRequest();
        xmlhttp.onreadystatechange = function() {
            if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
                document.getElementById("txtHint").innerHTML = xmlhttp.responseText;
            }
        }

        xmlhttp.open("GET", "gethint.php?q=" + str, true);
        xmlhttp.send();
    }
  }

Answer 1

The feature you are trying to implement is a sort of search / autocomplete that in simple terms takes an input string and returns related results that are displayed below the box that the user is typing in.

It looks like what you are trying to do is have an onkeyup attribute bound to a PHP function that submits the search string (which is the contents of the <input> field that you have defined. As far as I know, using PHP in this manner will not work as the PHP code in your onkeyup attribute will be executed only one time when your page is initially loaded, and not again after the user begins typing.

The solution to this problem in high level terms is to use JavaScript as an intermediary between the user input / HTML "frontend" and the PHP / MySQL "backend". JavaScript (and specifically the jQuery library) was created to assist you with tasks like this. JavaScript is executed in the client's browser, and not on the server like PHP code, so if you have an onkeyup event with a JavaScript function inside of it, you will see it execute even after the page has loaded, so long as the user is typing in the correct input field.

So, what you want to do is something like this:

1. Create your input field with an onkeyup="someJavaScriptFunction()" attribute. This will fire someJavaScriptFunction() every time someone types.
2. The someJavaScriptFunction() will be defined somewhere else on your page and be wrapped in <script> tags.
3. Your JavaScript function will first have to grab the content of the <input> box.
4. Next, it will need to use the AJAX function of the jQuery library to send a POST request to your PHP code.
5. Your PHP code will then do some behind the scenes work, talk to your MySQL database, get the suggested results it needs based off of the input string, and return them to the client.
6. The success component of your AJAX function will receive these results in the form of some sort of string of data, which you will need to parse to convert into a JavaScript array or object
7. You will need to use jQuery or some more JavaScript to display the results below the input box, and probably some more HTML and a different JavaScript function to make them clickable.

I strongly suggest walking through a few JavaScript and jQuery tutorials to understand how this stuff works. There are a few things you may need to familiarize yourself with so you can fill in the components between the HTML and the PHP code.