how to fetch dates between result using PHP query [duplicate]

Question

I am trying to fetch some data from database based on user entered month and year and table name.

From month and year I calculate from_date and to_date but query is not working if I put dates between $from_date and $todate.

$tableName = $_REQUEST['tableName'];
$month = $_REQUEST['monthName'];
$year = $_REQUEST['yearName'];
// echo json_encode($tableName);
$tableName = json_encode($tableName);
//echo $tableName;

$from_date = date('Y-m-d',strtotime($year."-".$month."-01"));
//echo json_encode($from_date);
//$to_date = date('Y-m-d',strtotime($year."-".$month."-01"));
$to_date = date('Y-m-t', strtotime($from_date));
//echo json_encode($to_date);

$conn = new PDO("sqlite:../../assets/rule_data.db");
    $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        //$sqlQuery = "select * from $tableName WHERE date >= '".$from_date."' AND date <= '".$to_date."' ";
        //$sqlQuery = "SELECT * FROM $tableName WHERE v_cr_sysdate >= '".$from_date."'  AND  v_cr_sysdate <= '".$to_date."' ";
        //$sqlQuery = "SELECT * FROM $tableName";
        //$sqlQuery = "select * from $tableName WHERE date >= '".convert('$from_date','%d-%m-%y')."' AND date <= '".date($to_date)."' ";
        $sqlQuery = "select * from $tableName WHERE date between '". date('Y-m-d', strtotime($from_date))."' ";
        $sqlQuery .= " AND date <='". date('Y-m-d', strtotime($to_date))."' ";
        $query = $conn->query($sqlQuery);       
        echo json_encode($query);
        echo json_encode(["riskModules"=>$query->fetchAll(PDO::FETCH_ASSOC)]);

If I remove AND consition and keep only where date >= '$from_date' It will work but not with date range of from and to date.

Please help where I am wrong in giving AND query to where clasuse.

The syntax is date BETWEEN 'first-date' AND 'second-date', not date BETWEEN 'first-date' AND date <= 'second-date' — Barmar
– Barmar, Commented Dec 17, 2016 at 9:41
You're mixing it up with the alternative date >= 'first-date' AND 'date <= 'second-date' — Barmar
– Barmar, Commented Dec 17, 2016 at 9:42
$sqlQuery = "select * from $tableName WHERE date between '$from_date' AND '$to_date' "; $query = $conn->query($sqlQuery); echo json_encode($query); echo json_encode(["riskModules"=>$query->fetchAll(PDO::FETCH_ASSOC)]); — Sanvi
– Sanvi, Commented Dec 17, 2016 at 9:54
I changed query but based on echo statement I can see query forming but not executing {"queryString":"select * from \"risk_data_bcsd\" WHERE date between '2016-04-01' AND '2016-04-30' "}{"riskModules":[]} — Sanvi
– Sanvi, Commented Dec 17, 2016 at 9:54

Your Common Sense · Accepted Answer · 2016-12-17 10:08:07Z

1

You can use BETWEEN clause to replace a combination of "greater than equal AND less than equal" conditions.

Changes

$sqlQuery = "select * from $tableName WHERE date between '". date('Y-m-d', strtotime($from_date))."' ";
$sqlQuery .= " AND '". date('Y-m-d', strtotime($to_date))."' ";

(OR) Which Is Similar As

$sqlQuery = "select * from $tableName WHERE date >= '". date('Y-m-d', strtotime($from_date))."' ";
$sqlQuery .= " AND date <= '". date('Y-m-d', strtotime($to_date))."' ";

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Quick Links

edited Dec 17, 2016 at 10:08

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

answered Dec 17, 2016 at 9:43

Nana Partykar

10.5k10 gold badges50 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Sanvi Over a year ago

I did lot of tries but nothing will work ..

Nana Partykar Over a year ago

You Tried My Answer @Sanvi ?

Sanvi Over a year ago

I changed to $sqlQuery = "select * from $tableName WHERE date between '$from_date' AND '$to_date' "; But this also not working. I tried many ways like <= >= and converting it to date function etc.. I am getting output as {"queryString":"select * from \"risk_data_bcsd\" WHERE date between '2016-04-01' AND '2016-04-30' "}{"riskModules":[]} Query forming but not executing in DB why?

Nana Partykar Over a year ago

See my edit 1 section @Sanvi

Sanvi Over a year ago

I have to put backstroke to only date string or to $from_date and to_date also? after putting query output is as below with no result {"queryString":"select * from \"risk_data_bcsd\" WHERE date between '2016-04-01' AND '2016-04-30' "}{"riskModules":[]}

|

Collectives™ on Stack Overflow

how to fetch dates between result using PHP query [duplicate]

1 Answer 1

14 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Linked

Related