php - Showing a sql table when radio button is selected using AJAX
Get the solution ↓↓↓I have a webpage with three radio buttons and an empty table. My goal is to select a radio button and have the table show a list of employees based on whichever radio button is clicked. Right now the list does not show up when I hit the button. I don't know what I'm doing wrong. I tried adding an alert to the doAjax function and the alert appeared so I know it's getting to that function. Below is my code, I have three files. Any help is appreciated.
//assign3.php
<html>
<head>
<meta charset="UTF-8">
<title></title>
<script src = "http://code.jquery.com/jquery-3.2.1.min.js">
</script>
<script src ="js/assign3js.js" type = "text/javascript" ></script>
</head>
<body>
<table border ='1'style = 'margin:auto' id = "employees" >
<tr><td colspan ='3'><h1>Employees </h1></td></tr>
<th> First Name</th>
<th>Last Name</th>
<th>Employee ID</th>
</table>
<input type="radio" name="employeeType" value="server" id="server"> Server<br>
<input type="radio" name="employeeType" value="bartender"id="bartender"> Bartender<br>
<input type="radio" name="employeeType" value="host"id = "hosts"> Host<br>
</body>
//script.js
$("document").ready(
function()
{
$("#server").click(doAjax);
$("#hosts").click(doAjax1);
$("#bartender").click(doAjax2);
}
);
function doAjax()
{
$.ajax({"method":"POST","url":"servers.php"}).done(update);
}
function update(data)
{
result = JSON.parse(data);
for(index =0; index < result.length; index++)
{
var row = "<tr>";
row += "<td>" + result[index].firstName + "</td>";
row += "<td>" + result[index].lastName + "</td>";
row += "<td>" + result[index].employeeID + "</td>";
row += "</tr>";
$("#employees").append(row);
}
}
//server.php
<?php
$server = (object) null;
$connection = "mysql:host=localhost;dbname=restaurant";
$user = "root";
$pwd = "mysql";
$db = new PDO($connection, $user, $pwd);
$results = $db->query("SELECT firstName, lastName, employeeID FROM
employee JOIN `server` ON employee.employeeID = server.employeeID");
while ($row = $results->fetch())
{
$server->firstName= $row["firstName"];
$server-> lastName=$row["lastName"] ;
$server->employeeId = $row["employeeID"];
}
$employee = [];
array_push($employee,$server);
$json1 = json_encode($employee);
echo $json1;
?>
Answer
Solution:
My answer is just an addition to @alex's answer. Alex has very good pointed out {-code-4}e main problems/aspects!
I would suggest you ano{-code-4}er approach: to use only one js function for getting {-code-4}e employee data, no matter which radio button you are selecting. When you click on a radio button, send an ajax request wi{-code-4} {-code-4}e corresponding radio button's value as argument. This value will be read on {-code-4}e server side and, dependent on it, a corresponding sql statement will be built and executed. The response will be read and appended to {-code-4}e employees table.
There would be too much to say here regarding your code version and my proposal. I hope you'll understand what I did.
Main points:
- I renamed "server.php" to "get-employee.php" (it's obvious why).
- I implemented {-code-4}e use of prepared statements instead of
, so {-code-4}at you are on a safe side in regard of {-code-4}e sql injection, in case you need to use input parameters in your queries.
- The employee data is fetched from db and saved in an array by using {-code-4}e
{-code-2}
me{-code-4}od. No need for fetching and looping {-code-4}rough objects. - The db connection details are defined in constants.
- An array wi{-code-4} db connection options regarding exception handling and o{-code-4}ers is added now (needed).
- Added {-code-4}e
{-code-3}
container to {-code-4}e table, to which {-code-4}e employees data will be appended. In general, if you use{-code-4}
{-code-4}en use it inside a{-code-4}ead
container. And if you use{-code-4}ead
, {-code-4}en use a{-code-3}
too.
I {-code-4}ink {-code-4}is is all {-code-4}at's important. Feel free to ask me any{-code-4}ing... tomorrow.
Good luck fur{-code-4}er.
assign3js.js
$(document).ready(function () {
$('input[name="employeeType"]').click(function () {
var employeeType = $({-code-4}is).val();
updateEmployees(employeeType);
});
});
function updateEmployees(type) {
$.ajax({
me{-code-4}od: 'post',
dataType: 'json',
url: 'get-employee.php',
data: {
'type': type
}
})
.done(function (response, textStatus, jqXHR) {
if (response) {
for (var i = 0; i < response.leng{-code-4}; i++) {
var row = '<tr>';
row += '<td>' + response[i].firstName + '</td>';
row += '<td>' + response[i].lastName + '</td>';
row += '<td>' + response[i].employeeID + '</td>';
row += '</tr>';
$('#employees {-code-3}').append(row);
}
} else {
$('#employees {-code-3}').append('<tr><td colspan="3">No employees found</td></tr>');
}
})
.fail(function (jqXHR, textStatus, errorThrown) {
// alert(textStatus + '\n' + errorThrown + '\n\n' + jqXHR.responseText);
})
.always(function (response, textStatus, jqXHR) {
//...
});
}
assign3.php:
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
<meta name="viewport" content="wid{-code-4}=device-wid{-code-4}, initial-scale=1, user-scalable=yes" />
<meta charset="UTF-8" />
<!-- The above 3 meta tags must come first in {-code-4}e head -->
<title>Demo</title>
<script src="http://code.j.com/j-3.2.1.min.js"></script>
<script src="js/assign3js.js" type="text/javascript"></script>
<style type="text/css">
#employees {
/*margin: auto;*/
border: 1px solid #ccc;
}
#employee-types {
margin-top: 20px;
border: 1px solid #ccc;
}
</style>
</head>
<body>
<table id="employees">
<{-code-4}ead>
<tr>
<{-code-4} colspan="3">
<h1>Employees</h1>
</{-code-4}>
</tr>
<tr>
<{-code-4}>First Name</{-code-4}>
<{-code-4}>Last Name</{-code-4}>
<{-code-4}>Employee ID</{-code-4}>
</tr>
</{-code-4}ead>
<{-code-3}></{-code-3}>
</table>
<fieldset id="employee-types">
<legend>Employee type</legend>
<input type="radio" id="server" name="employeeType" value="server">
<label for="server">Server</label><br/>
<input type="radio" id="bartender" name="employeeType" value="bartender">
<label for="bartender">Bartender</label><br/>
<input type="radio" id="hosts" name="employeeType" value="host">
<label for="hosts">Host</label><br/>
</fieldset>
</body>
</html>
get-employee.php
<?php
// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'restaurant');
define('USERNAME', 'root');
define('PASSWORD', 'mysql');
define('CHARSET', 'utf8');
// Error reporting.
error_reporting(E_ALL);
ini_set('display_errors', 1); // SET IT TO 0 ON A LIVE SERVER!
//
// Create a PDO instance as db connection to db.
$connection = new PDO(
sprintf('mysql:host=%s;port=%s;dbname=%s;charset=%s', HOST, PORT, DATABASE, CHARSET)
, USERNAME
, PASSWORD
, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => FALSE,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]
);
$response = false;
if (isset($_POST['type'])) {
$employeeType = $_POST['type'];
/*
* The bindings array, mapping {-code-4}e named markers from {-code-4}e sql statement
* (if any) to {-code-4}e corresponding values. It will be directly passed as
* argument to {-code-4}e PDOStatement::execute me{-code-4}od.
*
* @link http://php.net/manual/en/pdostatement.execute.php
*/
$bindings = [];
// Set {-code-4}e sql statement based on {-code-4}e submitted employee type.
switch ($employeeType) {
case 'bartender':
// $sql = '...';
// $bindings[:markerName] = markerValue;
break;
case 'hosts':
// $sql = '...';
// $bindings[:markerName] = markerValue;
break;
case 'server':
default:
$sql = 'SELECT
emp.firstName,
emp.lastName,
emp.employeeID
FROM employee AS emp
JOIN `server` AS srv ON srv.employeeID = emp.employeeID';
break;
}
// Prepare {-code-4}e sql statement for execution and return a statement object.
$statement = $connection->prepare($sql);
// Execute {-code-4}e prepared statement.
$statement->execute($bindings);
// Fetch data - all at once - and save it into response array.
$response = $statement->fetchAll(PDO::FETCH_ASSOC);
//
Answer
Answer
Answer
Answer
Answer
Solution:
Your SQL query is wrong, you have it doing
SELECT firstName, lastName, employeeID FROM employee JOIN `server` ON
employee.employeeID = server.employeeID
but employeeID exists in both employee and server so you need to modify your query to specific which table to get employeeID from so it should look like
SELECT employee.firstName, employee.lastName, employee.employeeID FROM employee JOIN `server` ON
employee.employeeID = server.employeeID
I made an SQLFiddle here which shows this. You should check if $results equals false, because the query function will return false if it fails. That worked for me, I had to change line 14 in server.php as well or else it wouldn't display the employee ID, this was because in the ajax script you reference it as employeeID but you set it as employeeId on line 14 so it would now be
$server->employeeID = $row["employeeID"];
Now when you run all of it will only one employee, if you want it to return multiple you need to have an array in server.php that you push employees to because right now it will overwrite the previous employee and only return the last one because of them writing to the same object with the same keys. For multiple employees to return I changed the code to
...
$server = [];
...
$count = 0;
while ($row = $results->fetch()) {
//create new class to stop warnings of creating object from empty value
$server[$count] = new \stdClass();
$server[$count]->firstName= $row["firstName"];
$server[$count]->lastName=$row["lastName"] ;
$server[$count]->employeeID = $row["employeeID"];
$count++;
}
...
what this does is $server is now an array, and every time we loop over an employee in the while loop we assign our values to a new object stored in $server's array. and the script.js looked like this
function update(data) {
var result = JSON.parse(data)[0]; // [0] because the json was wrapped in
// double []'s
for(var index =0; index < result.length; index++) {
var row = "<tr>";
row += "<td>" + result[index].firstName + "</td>";
row += "<td>" + result[index].lastName + "</td>";
row += "<td>" + result[index].employeeID + "</td>";
row += "</tr>";
$("#employees").append(row);
}
}
Not sure if any of this is the best solution to the problem, but I got it to work without too much trouble.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: closed without sending a request; it was probably just an unused speculative preconnection
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.