PHP string to MySQL date

Question

I've been stuck on the same question for a while. Although I'm sure this question should exist somewhere, none of the answers I researched and tried works which is strange.

Here's my code:

if(isset($_POST['date'])){
    $date=$_POST['date'];
    echo $date; //outputs 2015-04-09
    $date = date("Y-m-d", strtotime($date)); //Dropped quotation marks. before it was '$date'
    $res = mysqli_query($cxn, "SELECT *
                                FROM Reservations;
                                WHERE ResDate=$date);")
                    or die ("Couldn't execute query.");
    while($row = mysqli_fetch_assoc($res)){
        extract($row);
        echo "Reservation Number: $ResNo<br>Member ID: $MNo<br>VIN: $VIN<br>Reservation Date: $ResDate<br>Reserved Pick-Up Time: $ResTime<br>Pick-Up Address: $PickUpAddress<br>";
        echo "________________________________________<br>";
    }
}

The problem is at WHERE ResDate=$date. I'm trying to make $date into DATE type in MySQL. Here are some other ways I tried (I might missed a few)

WHERE ResDate=STR_TO_DATE('$date', '%m-%d-%Y') (also tried $date without ' ')

WHERE ResDate='$date' (also tried $date without ' ')

WHERE ResDate=CONVERT(DATETIME, '$date')(also tried $date without ' ')

WHERE ResDate=CAST('$date' AS DATE)(also tried $date without ' ')

For all the above variations, I got "Couldn't execute query." I'm running out of ideas.. any help? Thanks in advance.

UPDATE: Here's some code from the page before:

$dates = mysqli_query($cxn, "SELECT ResDate
                            FROM Reservations")
                or die ("Couldn't execute query.");

$dateArray = array();
array_push($dateArray, '<option>' . "Choose Date" . '</option>');
while($row = mysqli_fetch_assoc($dates)){
    extract($row);
    array_push($dateArray, '<option>' . "$ResDate" . '</option>');
}

So $ResDate from the database should be transferred to the next page, I think..

first correct you query $res = mysqli_query($cxn, "SELECT * FROM `Reservations` WHERE `ResDate`='$date'") — Vivek Singh, Apr 03 '15 at 06:41
Please ***isolate*** you problem first. I have the strong feeling the problem you're interested in - when asked for isolated - you would solve already your own. To do that, create a new example from *scratch* with as little code as necessary to only demonstrate the problem. — hakre, Apr 03 '15 at 06:52
And it's not enough to say that none of the answers you searched made it. You have to give reference about which one you write here and how they didn't help in your case. — hakre, Apr 03 '15 at 06:54

score 1 · Answer 1 · edited May 23 '17 at 11:43

1

Assuming $_POST['date'] is set correctly, (one of) your error(s) is here:

$date = date("Y-m-d", strtotime('$date'));

with single quotes, variables aren't expanded, so this resolves to literally $date, instead of the value in $_POST['date']. Lose the single quotes in strtotime and you should be set.

That, and:

you need the quotes around $date in your query.
You have a stray semicolon in your query after Reservations.
You have a stray closing parenthesis after $date

In cases like this, it is good practice to echo or log the query before you execute it, so you can see what actually goes to the server. Also, reading the error message from the server can help a lot.

edited May 23 '17 at 11:43

Community

1
1

answered Apr 03 '15 at 06:44

Bart Friederichs

33,050
15
95
195

@Daan, what else is there? – Bart Friederichs Apr 03 '15 at 06:50
1

Take another good look at the query especially the `;` after Reservations. – Daan Apr 03 '15 at 06:51

NULL · Answer 2 · 2015-04-03T08:42:28.263

1

Just change this line

$date = date("Y-m-d", strtotime('$date'));

To this

$date = date("Y-m-d", strtotime($date));

And this

$res = mysqli_query($cxn, "SELECT * FROM Reservations
                                WHERE ResDate=$date);")
                    or die ("Couldn't execute query.");

To this

$res = mysqli_query($cxn, "SELECT * FROM Reservations
                                WHERE ResDate=".$date.";")
                    or die ("Couldn't execute query.");

edited Apr 03 '15 at 08:42

answered Apr 03 '15 at 06:48

NULL

1,848
1
21
23

Or better, lose the quotes altogether, the add nothing except execution time. – Bart Friederichs Apr 03 '15 at 06:49
@BartFriederichs: I think in PHP that is parse time, not execution time. – hakre Apr 03 '15 at 06:50
Or even better, lose the extra `.");"` .. what is that doing there? – But those new buttons though.. Apr 03 '15 at 07:08

score 0 · Answer 3 · answered Apr 03 '15 at 06:45

0

Your sql query is wrong change the query to

SELECT *
FROM Reservations
WHERE ResDate='$date'

You forgot to add '' for $date

answered Apr 03 '15 at 06:45

Bivin

106
1
8

But those new buttons though.. · Accepted Answer · 2015-04-03T07:12:46.907

0

There are a couple problems, both syntax related. You've got an extra ) in your query and $date should not be quoted in the date() function. Try this:

if (isset($_POST['date'])) {
    $date = date("Y-m-d", strtotime($_POST['date']));
    $res = mysqli_query($cxn, "SELECT *
                               FROM Reservations
                               WHERE ResDate = '" . $date . "'")
                    or die ("Couldn't execute query.");

edited Apr 03 '15 at 07:12

answered Apr 03 '15 at 06:52

But those new buttons though..

21,377
10
81
108

I tried exactly the same and put `echo $date;` right after `$date = date("Y-m-d", strtotime($date));` (outputs 2015-04-09) but still doesn't work.. – user3763711 Apr 03 '15 at 07:09
Yes it works! Thank you! That's a smart way to concatenate them together, lesson learned – user3763711 Apr 03 '15 at 07:20
If you use string concatenation, it is better to use single quotes, as it has slightly better performance (no need for variable expansion). If you use double quotes, you can just put the `$date` variable in there. – Bart Friederichs Apr 03 '15 at 07:49
@BartFriederichs - can you illustrate what you mean? I don't think it's possible without using both because the date needs to be quoted in the query. If I'm wrong I'd be happy to learn a better way. – But those new buttons though.. Apr 03 '15 at 10:09
@billynoah, you can always use escape sequences: `'SELECT * FROM Reservations WHERE ResDate = \''.$date.'\''`. In the case of SQL queries, you shouldn't use this anyway, but prepared statements. – Bart Friederichs Apr 03 '15 at 10:28
Ok Thanks that makes sense. I do a lot of work with Opencart and the method in my post is the convention there. And yes, I agree about prepared statements. Thanks for your input! – But those new buttons though.. Apr 03 '15 at 15:54

Ajay Chaudhary · Answer 5 · 2015-04-03T07:06:47.690

0

Assumming that the date received from PHP is in for 'd-m-Y' you can use the following code :

$tDate = DateTime::createFromFormat('d-m-Y', $_POST['date']);
$date = $tDate->format('Y/m/d');

Please note this requires PHP Version 5.3 or later

for details in mannual please refere to http://php.net/manual/en/class.datetime.php

edited Apr 03 '15 at 07:06

answered Apr 03 '15 at 06:57

Ajay Chaudhary

298
2
14

PHP string to MySQL date

5 Answers5