how to fetch dates between result using PHP query

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, Dec 17 '16 at 09:41
You're mixing it up with the alternative `date >= 'first-date' AND 'date <= 'second-date'` — Barmar, Dec 17 '16 at 09: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, Dec 17 '16 at 09: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, Dec 17 '16 at 09:54

score 1 · Answer 1 · edited Dec 17 '16 at 10:08

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 '16 at 10:08

Your Common Sense

156,878
40
214
345

answered Dec 17 '16 at 09:43

Nana Partykar

10,556
10
48
77

I did lot of tries but nothing will work .. – Sanvi Dec 17 '16 at 09:50
You Tried My Answer @Sanvi ? – Nana Partykar Dec 17 '16 at 09:51
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? – Sanvi Dec 17 '16 at 09:52
See my **edit 1** section @Sanvi – Nana Partykar Dec 17 '16 at 09:58
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":[]} – Sanvi Dec 17 '16 at 10:07
Then, what you do is : `select * from \"risk_data_bcsd\" WHERE date between '2016-04-01' AND '2016-04-30'` Run this query directly in your database and see error. @Sanvi – Nana Partykar Dec 17 '16 at 10:08
[18:14:50] Error while executing SQL query on database 'rule_data': unrecognized token: "\" This error because $table_name will convert to this "\ but if I remove then result will come in DB some 700 rows... – Sanvi Dec 17 '16 at 10:16
Then, use `$tableName = stripslashes($tableName);` @Sanvi – Nana Partykar Dec 17 '16 at 10:18
@NanaPartykar may I ask, do you understand SQL syntax at all? – Your Common Sense Dec 17 '16 at 10:20
No Sir. I'm Weak. I'm trying to help. Here, question is not who know what. Every one in this planet is a learner. I learned from here. I know how it feels when your code doesn't work and need to finish the work @YourCommonSense. *No Offense Please* – Nana Partykar Dec 17 '16 at 10:22
Yeah I know the feeling. When you've given a code that will NEVER WORK and make your situation WORSE – Your Common Sense Dec 17 '16 at 10:24
*..code that will NEVER WORK* Great. Then, please help him with the correct answer. So, that he will be out of trouble. @YourCommonSense – Nana Partykar Dec 17 '16 at 10:26
This site is not to get someone out of trouble. This site is for giving answers. *If you happen know one* – Your Common Sense Dec 17 '16 at 10:31
I didnot understand all these discussion but finally my question left unanswered. I still couldnot get what is the problem in my Query string ? why not able to filter based on date range? – Sanvi Dec 19 '16 at 03:11

how to fetch dates between result using PHP query

1 Answers1