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!
