如何使用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是从你的表单提交的值