Hi I am looking to integrate a dropdown select/option in a page storing a group of date intervals that would filter the mysql query by the corresponding selected option. Not sure if this is a duplicate, but couldn't find anything that was similar on here.
The table I am retrieving the "created date" from has a timestamp column.
Should this be a Mysql query, or handled with javascript? What would be the optimal solution?
Example:
<h3>Posts Filter</h3>
<select>
<option>All Time</option>
<option>Past Day</option>
<option>Past Week</option>
<option>Past Month</option>
<option>Past Year</option>
</select>
<br>
<br>
<hr>
<div style="width:45%;height:200px;float:left;border:1px solid #ddd;padding:10px;margin:2.5%;box-sizing:border-box;">
<h2>Post Header</h2>
<em>Created On: February 21 2017</em>
<p>Bacon ipsum dolor amet short ribs kevin ribeye meatball filet mignon swine pork loin spare ribs, pork belly cow tenderloin venison...</p>
</div>
<div style="width:45%;height:200px;float:left;border:1px solid #ddd;padding:10px;margin:2.5%;box-sizing:border-box;">
<h2>Post Header</h2>
<em>Created On: February 18 2017</em>
<p>Bacon ipsum dolor amet short ribs kevin ribeye meatball filet mignon swine pork loin spare ribs, pork belly cow tenderloin venison...</p>
</div>
<div style="width:45%;height:200px;float:left;border:1px solid #ddd;padding:10px;margin:2.5%;box-sizing:border-box;">
<h2>Post Header</h2>
<em>Created On: January 26 2016</em>
<p>Bacon ipsum dolor amet short ribs kevin ribeye meatball filet mignon swine pork loin spare ribs, pork belly cow tenderloin venison...</p>
</div>
<div style="width:45%;height:200px;float:left;border:1px solid #ddd;padding:10px;margin:2.5%;box-sizing:border-box;">
<h2>Post Header</h2>
<em>Created On: March 15 2016</em>
<p>Bacon ipsum dolor amet short ribs kevin ribeye meatball filet mignon swine pork loin spare ribs, pork belly cow tenderloin venison...</p>
</div>Thank you for any input!
Serge
</div>
Yes um handle by ajax...
<select id="select">
<option value="all">All Time</option>
<option value="day">Past Day</option>
<option value="weel">Past Week</option>
<option value="month">Past Month</option>
<option value="year">Past Year</option>
</select>
<script>
$(document).ready(function(){
$("#select").change(function() {
$.ajax({
type: 'post',
url: "getSql",
data: { $(this).val() },
success: function($response) {
response = $.parseJSON(reponse);
$.each(response, function(k, v) {
// key and value
$("YOUDIV").append(VALUE)
});
}
});
})
})
</script>
The answer is: it depends on the use case. You have to be aware of the trade-offs you're making:
Implementing it in JavaScript means that you will retrieve the entire MySQL table in one go, and perform filtering in the browser.
Query execution speed will be fast since no conditions need to be evaluated by the database
You will only need one round-trip to the database
The amount of data that needs to be sent over the wire will potentially be large
The amount of data that needs to be held in memory by the browser will potentially be large, so data transfer will be slower
Execution in the browser will potentially be slow if a large volume of data needs to be filtered
Implementing it in a MySQL query means that you will retrieve only that part of the table that matches your conditions and will not have to perform filtering in the browser.
Query execution speed will be slower since the database has to evaluate the conditions
You will potentially need multiple round-trips to the database
The amount of data that needs to be sent over the wire will be smaller, so data transfer will be faster
The amount of data that needs to be held in memory by the browser will be smaller
Execution in the browser will be fast since no filtering logic needs to be applied
As you can tell from the listings above, the size of the table plays a crucial factor. If the table is small, I would opt for the JavaScript approach. If the table is large (or will potentially grow large), I would opt for the query approach.