基于下拉选择的SQL查询

Question

I want to write an SQL query based on dropdown selection values.

For example, if admin selects today, then SQL query should get only today's records and so on.

HTML

<select name="report_date">
    <option value="Today">Today</option>
    <option value="Yesterday">Yesterday</option>
    <option value="Last7Days">Last 7 Days</option>
    <option value="This Week">This Week</option>
    <option value="LastWeek">Last Week</option>
    <option value="ThisMonth">This Month</option>
    <option value="LastMonth">Last Month</option>
    <option value="ThisYear">This Year</option>
    <option value="LastYear">Last Year</option>
    <option value="AllTime">All Time</option>
</select>

<input type="text" name="product_name"/>

PHP

$fixedrange = $_GET['report_date'];
$pname= $_GET['product_name'];

if($fixedrange=="Today"){
    $today = date('Y-m-d');
    $fixeddate = 'OR o.`custom_date`='. $today;
}

SQL Query

SELECT *
FROM order_items AS oi
INNER JOIN orders o ON o.id=oi.`order_id`
WHERE oi.`product_name` LIKE '%$pname%' $fixeddate
  AND o.status = '1'
ORDER BY o.custom_date DESC

Answer 1

You should use concat

$sql = " SELECT * FROM order_items AS oi
   INNER JOIN orders o ON o.id=oi.`order_id` 
   WHERE oi.`product_name` LIKE concat('%', $pname,'%') 
   $fixeddate 
   AND o.status = '1' 
   ORDER BY o.custom_date DESC ; ";

or without using php date you can use date(o.custom_date) = curdate()

 if($fixedrange=="Today"){
    $today = date('Y-m-d');
    $fixeddate = 'OR  date(o.`custom_date`) = curdate() ' ;
}

$sql = " SELECT * FROM order_items AS oi
  INNER JOIN orders o ON o.id=oi.`order_id` 
  WHERE oi.`product_name` LIKE concat('%', $pname,'%') 
  $fixeddate
  AND o.status = '1' 
  ORDER BY o.custom_date DESC ; ";