PHP / MySQL table, no results if multiple months

Question

I have a table where I need to show a list of meetings, it's based on dates (default showing next 7 days)and then via a form pick from > to dates. I've gotten that part to work, but the moment that it spans over multiple months, it returns no results. The current week for example, end of january / first few days of february.

In the MySQL DB they're formatted DD/MM/YYYY.

$til = $_POST['til'];
$fra = $_POST['fra'];
$myDate = date('d/m/Y');
$dateWeek = date('d/m/Y', strtotime(' + 7 days'));
if(isset($_POST['hent'])){
    if (!$conn) {
        die("Ingen forbindelse: " . mysqli_connect_error());
    } else {
        $sql = ("select * from kaffe WHERE dato >= '$fra' AND dato <= '$til' order by STR_TO_DATE(dato,'%d/%m/%Y'), meetingstart");
        $sqlQuery = $conn->query($sql) or die($conn->error);
        $sqlQuery->num_rows;
        while($sqlFetch = $sqlQuery->fetch_object()){
            echo "
                Show 7 days results
            ";
        }
    }
}else{
    $sql = ("select * from kaffe WHERE dato >= '$myDate' AND dato <= '$dateWeek' order by STR_TO_DATE(dato,'%d/%m/%Y'), meetingstart");
    $sqlQuery = $conn->query($sql) or die($conn->error);
    $sqlQuery->num_rows;
    while($sqlFetch = $sqlQuery->fetch_object()){
        echo "
            Show from > to dates selected in the datepicker form
        ";
    }
}

I'm guessing the formatting might be part of what's causing the issue, as most suggest the "expected" EU way is done with - instead of /. Had no success getting it to work though.

Hi, welcome to stackoverflow. Your code is vulnerable to [**sql injections**](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work)! You should always [bind your parameters](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) when dealing with SQL queries. — Nicolas, Jan 29 '20 at 12:43
"I'm guessing the formatting might be part of what" - you are correct. Not a part but the sole reason — Your Common Sense, Jan 29 '20 at 12:51
If it was me, I'd start with the sql, and then, once I knew that was working, I'd move on to the PHP — Strawberry, Jan 29 '20 at 12:53
I'll be adding real_escape_strings when I get the part with the dates working. I need to figure out how to set it up though, as YCS said, it's the formatting of the dates. Hoping someone can point me in the right direction, as I don't have much experience with this. — Zehheal, Jan 29 '20 at 12:58
Please don't use `real_escape_strings`. You should use prepared statements. — Dharman, Jan 29 '20 at 13:09
As you probably (should) know, `SELECT '12/01/2020' > '03/03/2020' FROM dual` returns `TRUE`. So better start from converting all these dates into a proper format, at least on SQL level. — mitkosoft, Jan 29 '20 at 13:29
Will converting them into 12-01-2020 instead of 12/01/2020 work then? Is that all that it requires? I've been away from this sort of work for 7+ years, meaning I'm not familiar with majority of the "basics" even. — Zehheal, Jan 29 '20 at 13:31
Would be best to make them `Y-m-d`. Hint: `STR_TO_DATE('12/01/2020', '%d/%m/%Y')` — mitkosoft, Jan 29 '20 at 13:32
I'll get to work on converting the dates from d/m/y to y-m-d then and then use the STR_TO_DATE to convert it I take it? Would I have have to edit the structure? Currently my "dato" field is just a varchar to keep it simple. — Zehheal, Jan 29 '20 at 13:45

score 0 · Accepted Answer · answered Jan 29 '20 at 14:11

In general, you have to unify all the dates to a proper format. Also, it's enough to use BETWEEN in your SQL queries. So, based on your code, you want the following:

<?php
    $til = date('Y-m-d', strtotime($_POST['til']));
    $fra = date('Y-m-d', strtotime($_POST['fra']));
    $myDate = date('Y-m-d');
    $dateWeek = date('Y-m-d', strtotime(' + 7 days'));

    //query 1
    $sql = ("SELECT * FROM kaffe WHERE STR_TO_DATE(dato, '%Y-%m-%d') BETWEEN '$fra' AND '$til' ORDER BY STR_TO_DATE(dato, '%Y-%m-%d'), meetingstart");

    //query 2
    $sql = ("SELECT * FROM kaffe WHERE STR_TO_DATE(dato, '%Y-%m-%d') BETWEEN '$myDate' AND '$dateWeek' ORDER BY STR_TO_DATE(dato, '%Y-%m-%d'), meetingstart");
?>

I'm not placing your entire code as I believe you will find easily what to replace. Also, some checks for variable's existence would be more than great to be included in your code.

score -1 · Answer 2 · edited Jan 29 '20 at 16:15

-1

Replace first query with this:

$sql = ("select * from kaffe WHERE dato >= '".$fra."' AND dato <= '".$til."' order by STR_TO_DATE(dato,'%d/%m/%Y'), meetingstart");

and also replace second query with this:

$sql = ("select * from kaffe WHERE dato >= '".$myDate."' AND dato <= '".$dateWeek."' order by STR_TO_DATE(dato,'%d/%m/%Y'), meetingstart");

Your value was not merging into the query

edited Jan 29 '20 at 16:15

Travis

657
6
24

answered Jan 29 '20 at 13:42

Nayan Sangtani

17
2

I'm only getting a partial result when attempting with those queries unfortunately. The default 7 days still don't return any results. – Zehheal Jan 29 '20 at 13:56

PHP / MySQL table, no results if multiple months

2 Answers2