mysql - 如何使用 mysql date_format 动态给出日期？

Question

我正在满足分别每 7、15、30、50、75 或 100 天生成报告的要求，其中用户通过选择下拉列表进行选择。首先，我编写了 7 天的查询来生成报告，效果很好。但我不确定如何根据用户选择使该选择动态化。

我对 7 天报告的查询如下:

SELECT table1.order_id FROM table2 LEFT JOIN table1 ON table1.order_id = table2.order_id  WHERE table1.assigned_user_id=:assigned_user_id AND DATE(table2.track_date) >= (DATE(NOW()) - INTERVAL 7 DAY) AND table2.order_status=2 GROUP BY table1.order_id

我的选择下拉列表是:

<select name="supply_analysis" id="supply_analysis" class="supply_analysis">
            <option value="7" <?php echo (isset($_GET['analysis']) && $_GET['analysis']=='7')?'selected':''?>>7 days</option>
            <option value="15" <?php echo (isset($_GET['analysis']) && $_GET['analysis']=='15')?'selected':''?> >15 days</option>
            <option value="30" <?php echo (isset($_GET['analysis']) && $_GET['analysis']=='30')?'selected':''?> >30 days</option>
            <option value="50" <?php echo (isset($_GET['analysis']) && $_GET['analysis']=='50')?'selected':''?> >50 days</option>
            <option value="75" <?php echo (isset($_GET['analysis']) && $_GET['analysis']=='75')?'selected':''?> >75 days</option>
            <option value="100" <?php echo (isset($_GET['analysis']) && $_GET['analysis']=='100')?'selected':''?> >100 days</option>
 </select>

我根据用户选择生成报告的查询是:

"SELECT table1.order_id FROM table2 LEFT JOIN table1 ON table1.order_id = table2.order_id  WHERE table1.assigned_user_id=:assigned_user_id AND DATE(table2.track_date) >= (DATE(NOW()) - INTERVAL ".$_GET['analysis']." DAY) AND table2.order_status=2 GROUP BY table1.order_id"

目前，查询似乎正常工作，但我不确定它是否正常工作。请问有什么建议/帮助吗？谢谢。

Answer 1

看看这个:

$values = [7 , 15 , 30 , 50 , 75 , 100];

if(in_array($values , $supply_analysis)) //use this to prevent SQL injection 
{
  $SQL = "SELECT table1.order_id 
          FROM table2 LEFT JOIN table1 ON table1.order_id = table2.order_id  
          WHERE table1.assigned_user_id=:assigned_user_id AND DATE(table2.track_date) >= (DATE(NOW()) - INTERVAL {$supply_analysis} DAY) AND table2.order_status=2 GROUP BY table1.order_id";

  //do your database query here

}

$supply_analysis 是从您的表单提交的值