Do you mean don’t bother to encode it as JSON at all and construct everything (i.e. the table body) on the server?
That’s the simplest and most reliable way to handle things. Certain situations may arise that result in JSON being more beneficial, such as when there are hundreds of rows to the table, or you want to do pagination, but until those circumstances are on the radar I don’t see anything wrong with keeping things simple.
Oh, ok.
I had originally done it this way as I find JavaScript way easier to write.
I’ll have a look at this when I have time and see if I can’t change things to work as you suggest.
Thanks.
BTW, what’s your PHP like?
Would you be in a position to offer any feedback?
I developed a custom CMS a few years ago and wrote up some of the help threads that used to be stickied in the PHP forum. I’ll be happy to offer some feedback.
2 Likes
So I had a chance to have a look at this.
index.html:
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>AJAX filter demo</title>
<style>
body {
padding: 10px;
}
h1 {
margin: 0 0 0.5em 0;
color: #343434;
font-weight: normal;
font-family: 'Ultra', sans-serif;
font-size: 36px;
line-height: 42px;
text-transform: uppercase;
text-shadow: 0 2px white, 0 3px #777;
}
h2 {
margin: 1em 0 0.3em 0;
color: #343434;
font-weight: normal;
font-size: 30px;
line-height: 40px;
font-family: 'Orienta', sans-serif;
}
#employees {
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
background: #fff;
margin: 15px 25px 0 0;
border-collapse: collapse;
text-align: center;
float: left;
width: 700px;
}
#employees th {
font-size: 14px;
font-weight: normal;
color: #039;
padding: 10px 8px;
border-bottom: 2px solid #6678b1;
}
#employees td {
border-bottom: 1px solid #ccc;
color: #669;
padding: 8px 10px;
}
#employees tbody tr:hover td {
color: #009;
}
#filter {
float:left;
}
</style>
</head>
<body>
<h1>Temporary worker database</h1>
<table id="employees">
<thead>
<tr>
<th>Name</th>
<th>Percentage</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
<div id="filter">
<h2>Filter options</h2>
<div>
<input type="checkbox" id="car" name="hasCar">
<label for="car">Has own car</label>
</div>
<div>
<input type="checkbox" id="language" name="speaksForeignLanguage">
<label for="language">Can speak foreign language</label>
</div>
<div>
<input type="checkbox" id="nights" name="canWorkNights">
<label for="nights">Can work nights</label>
</div>
<div>
<input type="checkbox" id="student" name="isStudent">
<label for="student">Is a student</label>
</div>
</div>
<script src="http://code.jquery.com/jquery-latest.js"></script>
<script>
function getEmployeeFilterOptions(){
var opts = [];
$checkboxes.each(function(){
if(this.checked){
opts.push(this.name);
}
});
return opts;
}
function updateEmployees(opts){
$.ajax({
type: "POST",
url: "submit.php",
dataType : 'html',
cache: false,
data: {filterOpts: opts},
success: function(result){
$('#employees tbody').html(result);
}
});
}
var $checkboxes = $("input:checkbox");
$checkboxes.on("change", function(){
var opts = getEmployeeFilterOptions();
updateEmployees(opts);
});
updateEmployees();
</script>
</body>
</html>
submit.php:
<?php
// DB Connection
$pdo = new PDO('mysql:host=localhost;dbname=database', 'user', 'pass');
$select = 'SELECT *';
$from = ' FROM people';
$where = ' WHERE TRUE';
$opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');
if (in_array("hasCar", $opts)){
$where .= " AND hasCar = 1";
}
if (in_array("speaksForeignLanguage", $opts)){
$where .= " AND speaksForeignLanguage = 1";
}
if (in_array("canWorkNights", $opts)){
$where .= " AND canWorkNights = 1";
}
if (in_array("isStudent", $opts)){
$where .= " AND isStudent = 1";
}
$sql = $select . $from . $where;
$statement = $pdo->prepare($sql);
$statement->execute();
$results=$statement->fetchAll(PDO::FETCH_ASSOC);
$html = "";
foreach($results as $key => &$row) {
$html .= "<tr>";
$html .= "<td>" . $row["name"] . "</td>";
$percentage = 0;
$percentage += $row["hasCar"];
$percentage += $row["isStudent"];
$percentage += $row["speaksForeignLanguage"];
$percentage += $row["canWorkNights"];
$percentage *=25;
$html .= "<td>" . $percentage . "</td>";
$html .= "</tr>";
}
echo($html);
?>
Updated demo.
@Paul_Wilkins - does that seem like a sane way of doing things?
Feedback is welcome.
Yes, overall it’s a good strategy but I’d want to try and simplify some things, by making them less brittle to change.
Retrieving values from $_POST is a technique that has a much preferred technique of using the input_filter command.
In this particular case, we can tell it to expect that filterOpts is an array with the FILTER_REQUIRE_ARRAY flag, If nothing was submitted we get null, so we could use (array) to cast null to an empty array for us too.
$opts = (array) filter_input(INPUT_POST, 'filterOpts', FILTER_SANITIZE_STRING, FILTER_REQUIRE_ARRAY);
It’s nice to see that you’re using a PDO connection,
Plugging in the multiple where clauses one after another seems to be quite brittle, especially if you might ever want to add more at some later stage. We can make things more easily able to handle change by placing those row names in to their own array, and referring to them from different parts of the code.
$rowNames = array(
"hasCar",
"speaksForeignLanguage",
"canWorkNights",
"isStudent"
);
I’ve also put some parts in to separate functions, to help keep the logic of doing certain things in the one place. This whereCondition function for example will be able to add more where clauses on to your SQL statement, without being at risk from outside tampering because you have stated explicitly in the $rowNames array the rows that will be used.
function whereCondition($opts, $rowNames) {
$conditions = array();
foreach ($opts as $condition) {
if (in_array($condition, $rowNames)) {
array_push($conditions, $condition . ' = 1');
}
}
$where = ' WHERE TRUE';
if (count($conditions) > 0) {
$where = ' WHERE ' . implode(' AND ', $conditions);
}
return $where;
}
Likewise, the personPercentage function can take the $rowNames array and use that to figure out the percentage.
function personPercentage($row, $rowNames) {
$percentage = 0;
foreach($rowNames as $rowName) {
if ($row[$rowName]) {
$percentage += 100 / count($rowNames);
}
}
return $percentage;
}
The above helps to remove the bulk of the complexity from the remainder of the code:
$select = 'SELECT *';
$from = ' FROM people';
$where = whereCondition($opts, $rowNames);
$sql = $select . $from . $where;
$results = executeSQL($sql);
$html = "";
foreach($results as $row) {
$html .= "<tr>";
$html .= "<td>" . $row["name"] . "</td>";
$percentage = personPercentage($row, $rowNames);
$html .= "<td>" . $percentage . "</td>";
$html .= "</tr>";
}
echo($html);
Here’s the full PHP script with those improvements put in to place.
<?php
function whereCondition($opts, $rowNames) {
$conditions = array();
foreach ($opts as $condition) {
if (in_array($condition, $rowNames)) {
array_push($conditions, $condition . ' = 1');
}
}
$where = ' WHERE TRUE';
if (count($conditions) > 0) {
$where = ' WHERE ' . implode(' AND ', $conditions);
}
return $where;
}
function executeSQL($sql) {
// DB Connection
$pdo = new PDO('mysql:host=localhost;dbname=database', 'sitepoint', 'sitepoint');
$statement = $pdo->prepare($sql);
$statement->execute();
$results=$statement->fetchAll(PDO::FETCH_ASSOC);
return $results;
}
function personPercentage($row, $rowNames) {
$percentage = 0;
foreach($rowNames as $rowName) {
if ($row[$rowName] > 0) {
$percentage += 100 / count($rowNames);
}
}
return $percentage;
}
$opts = (array) filter_input(INPUT_POST, 'filterOpts', FILTER_SANITIZE_STRING, FILTER_REQUIRE_ARRAY);
$rowNames = array(
"hasCar",
"speaksForeignLanguage",
"canWorkNights",
"isStudent"
);
$select = 'SELECT *';
$from = ' FROM people';
$where = whereCondition($opts, $rowNames);
$sql = $select . $from . $where;
$results = executeSQL($sql);
$html = "";
foreach($results as $row) {
$html .= "<tr>";
$html .= "<td>" . $row["name"] . "</td>";
$percentage = personPercentage($row, $rowNames);
$html .= "<td>" . $percentage . "</td>";
$html .= "</tr>";
}
echo($html);
?>
What do you think?
1 Like
That the more I learn, the more I realize how little I know 
Seriously, thanks for that Paul. I’ll have a look at what you posted over the next couple of days and get back to you.
I have been lurking for a little while and also have been distracted by my own code. But both of you have really helped me a lot and thanks to you both I have solved my initial problem! So thanks very much!
On a general note, do you have tips on how to keep the loading times / response times to a minimum? Like functions I should avoid or basic coding practices to follow?
Do lots of measurements to figure out where the best gains might be made.
Situations that you want to avoid are to optimise code to gain a 1% benefit, when a 40% improvement can be easily gained elsewhere by caching or other techniques.
Google have some good tools at https://developers.google.com/speed/pagespeed/ that can help you to figure out what can be done to improve loading times.
Hey Paul,
I’ve just been going through the code you posted.
Your version is a lot cleaner and I picked up a couple of neat tricks on the way (the main one being filter_input).
So here, if $_POST['filter_opts'] is empty then filter_input(INPUT_POST, 'filterOpts', FILTER_SANITIZE_STRING, FILTER_REQUIRE_ARRAY) will return null and specifying (array) will cast null to an empty array. Did I get that right?
That’s neat
When I get a minute I’ll update my blog article with this version of the PHP, or maybe write a new article.
I’m more than happy to credit the PHP to you. Have you got a homepage you’d like me to link to?
Yes indeed, though it only becomes beneficial when the code is capable of handling an empty set. Another benefit of doing things this way is that it helps to prevent lots of bounds-checking in the code, that is already achieved due to the zero-length array.
It’s related to the null object pattern idea, and others have been speaking out about this too, for example, Say no to null.
Thanks - the nice thing is that this can easily be updated later on to handle differently weighted values without needing to change the rest of the code.
Sadly I don’t have an online presence to that degree, so just a byline will have to do.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.