MySQL查询检索帖子并根据特定的日期间隔进行过滤 [英] Mysql query retrieving posts and filtering based on specific date intervals

问题描述

我想在一个页面中集成一个下拉选择/选项，该页面存储一组日期间隔，这些间隔将通过相应的所选选项过滤mysql查询.不知道这是否是重复项，但找不到与此处相似的内容.

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.

我要从中检索创建日期"的表有一个timestamp列.

The table I am retrieving the "created date" from has a timestamp column.

这是Mysql查询还是使用javascript处理?最佳解决方案是什么?

Should this be a Mysql query, or handled with javascript? What would be the optimal solution?

示例:

<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>

谢谢您的输入！

同步

推荐答案

答案是:这取决于用例.您必须知道要进行的权衡:

The answer is: it depends on the use case. You have to be aware of the trade-offs you're making:

1. 在JavaScript中实现意味着您将一次性检索整个MySQL表，并在浏览器中执行过滤.

1. 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

在MySQL查询中实现意味着您将只检索表中符合您条件的那部分，而不必在浏览器中执行过滤.

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

从上面的清单中可以看出，表格的大小至关重要.如果表很小，我会选择JavaScript方法.如果表很大(或可能会变大)，我会选择查询方法.

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.

这篇关于MySQL查询检索帖子并根据特定的日期间隔进行过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！