Ajax refine search

Question

I have a table for a sports day where there are 4 columns name, house, event, result. I have no problem creating and displaying the database but i want to be able to search in a bar and to use AJAX to automatically search all 4 columns for whats in the search bar. I am using PHPmyadmin to store the database with mySQLI. i am able to display the database on the page that i want. I also want when the page starts for the whole table to be displayed and then when you start typing it just removes any items that do not match the search. I have never used Ajax before so sorry for my bad code as it is all from w3schools site. the DB is called sports_day and the table is called full_results. here is my current code.

    <script>
function showUser(str) {
    if (str == "") {
        document.getElementById("txtHint").innerHTML = "";
        return;
    } else {
        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 (this.readyState == 4 && this.status == 200) {
                document.getElementById("txtHint").innerHTML = this.responseText;
            }
        };
        xmlhttp.open("GET","results_query.php?q="+str,true);
        xmlhttp.send();
    }
}
</script>

<form>
search for pupil
<input type="text" size="30" name="user" onkeyup="showUser(this.value)">
<div id="livesearch"></div>
<br>
</form>

<div class="col-sm-12">

<div id="txtHint"><b> pupil's info will be listed here</b></div>

</div>

and on a page called results_query.php is this code

<body>

<?php
$q = intval($_GET['q']);

$con = mysqli_connect("localhost","root","","sports_day");
if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
}

mysqli_select_db($con,"sports_day");
$sql="SELECT * FROM full_results WHERE id = '".$q."'";
$result = mysqli_query($con,$sql);

echo '<tr>';
    echo '<th>NAME</th>';
    echo '<th>HOUSE</th>';
    echo '<th>EVENT</th>';
    echo '<th>RESULT</th>';
echo ' </tr>';
while($row = mysqli_fetch_array($result)) {
  echo "<tr>";
  echo "<td>" . $row['NAME'] . "</td>";
  echo "<td>" . $row['HOUSE'] . "</td>";
  echo "<td>" . $row['EVENT'] . "</td>";
  echo "<td>" . $row['RESULT'] . "</td>";
  echo "</tr>";
}
echo "</table>";
mysqli_close($con);
?>
</body>

at the moment what happens is none of the table is shown and when i type anything in the search box the whole table appears along with in plain text at the bottom the title and all the contents of the table in a long line.

any suggestion to get my code to work would be greatly appreciated!

thanks!

What data a user will put in that *search bar* to get desired results in the table? Is it the *id*, *name*, *house*, *event* or *result* value? — Rajdeep Paul, Dec 21 '16 at 15:49
i would like it so that the user could put any of the values and the table would only show row where at least one of columns contains is returned so they could type in Joe Bloggs and find all of his results or 100m and find all of the 100m results! the id will not be displayed to the user! — Alistair Gimlette, Dec 22 '16 at 14:49
I've given an answer below. Hopefully this will resolve your issue. — Rajdeep Paul, Dec 22 '16 at 19:36

score 0 · Answer 1 · edited May 23 '17 at 11:46

If you use your 'results_query.php' file only for getting the data from database, then you don't need to create a <body> tag. If you use only PHP then you can easily skip any plane HTML. That's just a digression :)

But to the point. You can change the way you return your data from database. I think, instead of doing a lot of echo's it is better to add result to the variable and echoing the variable at the end.

    $data = '<tr>' . '<th>NAME</th>' . '<th>HOUSE</th>' . '<th>EVENT</th>' . '<th>RESULT</th>' . '</tr>';

    while($row = mysqli_fetch_array($result)) {
      $data .= '<tr>';
      $data .= '<td>' . $row['NAME'] . '</td>';
      $data .= '<td>' . $row['HOUSE'] . '</td>';
      $data .= '<td>' . $row['EVENT'] . '</td>';
      $data .= '<td>' . $row['RESULT'] . '</td>';
      $data .= '</tr>';
    }

    $data .= '</table>';
    mysqli_close($con);

    echo $data;

See if this changes something.

What about showing entire table after the page's loaded, you will have to change both PHP and JavaScript code a little bit.

You can change your JS so it gets everything from your full_results table after page is loaded. There are several ways to do this and you can read about them here:

pure JavaScript equivalent to jQuery's $.ready() how to call a function when the page/dom is ready for it

The easiest way would be to do this this way:

    <script>
        function showUser(str) {
            var url;
            var xmlhttp;

            if (str == "") { //if empty string - get all data
              url = "results_query.php";
            } else { //get particular data otherwise
              url = "results_query.php?q="+str;
            }

            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 (this.readyState == 4 && this.status == 200) {
                document.getElementById("txtHint").innerHTML = this.responseText;
              }
            }

            xmlhttp.open("GET", url, true);
            xmlhttp.send();
        }
    </script>

        <form>
        search for pupil
        <input type="text" size="30" name="user" onkeyup="showUser(this.value)">
        <div id="livesearch"></div>
        <br>
        </form>

        <div class="col-sm-12">

        <div id="txtHint"><b> pupil's info will be listed here</b></div>

        </div>

        <script>
           //calling your function with empty string because we want to get all data
           showUser("");
        </script>

and in the PHP file you can do something like this:

<?php
$q = 0;
//check if 'q' parameter was passed
if(isset($_GET['q'])) {
  $q = intval($_GET['q']);
}

$con = mysqli_connect("localhost","root","","sports_day");
if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
}

mysqli_select_db($con,"sports_day");
$sql = ($q) ? "SELECT * FROM full_results WHERE id = '".$q."'" : "SELECT * FROM full_results";

Now your JavaScript function will be called after loading your page. It will call your PHP script with AJAX and this script should return all data from your table.

In line ($q) ? "SELECT * FROM full_results WHERE id = '".$q."'" : "SELECT * FROM full_results"; there is a simple check if $q is different from 0. Our variable will be set to 0 if no argument was passed, so whenever $q is equal to '0', we just want to get all the data from full_results and specific data otherwise.

I also added var xmlhttp because it is only local variable. You can read more about that in here:

https://stackoverflow.com/a/1471738/7301294

I hope it will help you. Let me know if you have any other problems and never be afraid to ask. Good luck!

i replaced my old code with yours and on the results page no table is shown and nothing happens whatever i type in my search i then went on the results_query page and i was getting an error message saying "Undefined index: q in line 2" any ideas?? — Alistair Gimlette, Dec 21 '16 at 17:18
@AlistairGimlette Yes, you're right, there was a bug actualy, sorry about that. I've edited my post a little bit. There need to be if statement, that checks if $_GET['q'] is set. There was a problem because it was trying to get value of $_GET['q'] but it has been never set since we weren't passing any parameters. Try now. — TomaszCyrulik, Dec 21 '16 at 17:35
hey, noticed you missed out a couple of bracket one curly one after `document.getElementById("txtHint").innerHTML = this.responseText;` and another normal bracket after closing the if statement! – — Alistair Gimlette, Dec 22 '16 at 13:48
the table is being displayed after adding in some additional table tags in the php page but what i type doesnt seem to change the table!! thanks for all the help btw!! — Alistair Gimlette, Dec 22 '16 at 14:16

score 0 · Accepted Answer · edited May 23 '17 at 12:24

The solution would be like this:

Keep your HTML search form as it is.

<form>
    search for pupil
    <input type="text" size="30" name="user" onkeyup="showUser(this.value)">
    <div id="livesearch"></div>
    <br>
</form>

... I also want when the page starts for the whole table to be displayed and then when you start typing it just removes any items that do not match the search.

See this <div> section here,

<div class="col-sm-12">
    ...
</div>

You didn't put anything in this <div> section. First of all, you have to display your entire table in this section, which you can later filter out using the AJAX request. Also, assign an id to this <div> section so that it could be easier for you put the AJAX response in this <div> section. So the code for this <div> section would be like this:

<div class="col-sm-12" id="pupil-info">
<?php
    $con = mysqli_connect("localhost","root","","sports_day");
    if (!$con) {
        die('Could not connect: ' . mysqli_error($con));
    }

    mysqli_select_db($con,"sports_day");
    $sql = "SELECT * FROM full_results";
    $result = mysqli_query($con,$sql);

    echo '<table>';
    echo '<tr>';
        echo '<th>NAME</th>';
        echo '<th>HOUSE</th>';
        echo '<th>EVENT</th>';
        echo '<th>RESULT</th>';
    echo ' </tr>';
    while($row = mysqli_fetch_array($result)) {
      echo "<tr>";
      echo "<td>" . $row['NAME'] . "</td>";
      echo "<td>" . $row['HOUSE'] . "</td>";
      echo "<td>" . $row['EVENT'] . "</td>";
      echo "<td>" . $row['RESULT'] . "</td>";
      echo "</tr>";
    }
    echo "</table>";
    mysqli_close($con);
?>
</div>

Change your Javascript/AJAX code in the following way,

<script>
    function showUser(str){
        var str = str.trim();
        var xmlhttp;
        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 (this.readyState == 4 && this.status == 200) {
                document.getElementById("pupil-info").innerHTML = this.responseText;
            }
        };
        xmlhttp.open("GET","results_query.php?q="+encodeURIComponent(str),true);
        xmlhttp.send();
    }
</script>

Please note that you should encode the user inputted str value using encodeURIComponent() function before passing it to the results_query.php page.

Finally, on results_query.php page process your AJAX request like this:

<?php

    $con = mysqli_connect("localhost","root","","sports_day");
    if (!$con) {
        die('Could not connect: ' . mysqli_error($con));
    }

    mysqli_select_db($con,"sports_day");
    $sql = "SELECT * FROM full_results";
    if(isset($_GET['q']) && !empty($_GET['q'])){
        $sql .= "  WHERE CONCAT(id, NAME, HOUSE, EVENT, RESULT) LIKE '%".$_GET['q']."%'";
    }
    $result = mysqli_query($con,$sql);

    echo '<table>';
    echo '<tr>';
        echo '<th>NAME</th>';
        echo '<th>HOUSE</th>';
        echo '<th>EVENT</th>';
        echo '<th>RESULT</th>';
    echo ' </tr>';
    if(mysqli_num_rows($result)){
        while($row = mysqli_fetch_array($result)) {
            echo "<tr>";
                echo "<td>" . $row['NAME'] . "</td>";
                echo "<td>" . $row['HOUSE'] . "</td>";
                echo "<td>" . $row['EVENT'] . "</td>";
                echo "<td>" . $row['RESULT'] . "</td>";
            echo "</tr>";
        }
    }else{
        echo "<tr>";
            echo "<td colspan='4' style='text-align:center;'>No records found</td>";
        echo "</tr>";
    }
    echo "</table>";
    mysqli_close($con);

?>

Sidenote: Learn about prepared statement because right now your query is susceptible to SQL injection. Also see how you can prevent SQL injection in PHP.

thanks, i haven't been too worried about SQL injections as the people who will be using this system barely know how to turn on their own phones!! :)) btw, code runs like a dream!!! you are an absolute legend!! — Alistair Gimlette, Dec 23 '16 at 10:51
if you know how to fix some of my positioning issues that would be AWESOME!! http://stackoverflow.com/questions/41303182/centering-and-formatting-forms-bootstrap — Alistair Gimlette, Dec 23 '16 at 14:27
@AlistairGimlette Wait for CSS developers to answer *that* question. — Rajdeep Paul, Dec 23 '16 at 14:37

Ajax refine search

2 Answers2