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
– Vivek Singh, Commented Apr 3, 2015 at 6: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
– hakre, Commented Apr 3, 2015 at 6: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
– hakre, Commented Apr 3, 2015 at 6:54

Community · Accepted Answer · 2017-05-23 11:43:54Z

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, 2017 at 11:43

CommunityBot

11 silver badge

answered Apr 3, 2015 at 6:44

Bart Friederichs

33.8k16 gold badges112 silver badges219 bronze badges

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

2 Comments

Bart Friederichs Over a year ago

@Daan, what else is there?

Daan Over a year ago

Take another good look at the query especially the ; after Reservations.

NULL · Accepted Answer · 2015-04-03 08:42:28Z

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 3, 2015 at 8:42

answered Apr 3, 2015 at 6:48

NULL

1,8481 gold badge21 silver badges23 bronze badges

3 Comments

Bart Friederichs Over a year ago

Or better, lose the quotes altogether, the add nothing except execution time.

hakre Over a year ago

@BartFriederichs: I think in PHP that is parse time, not execution time.

You Old Fool Over a year ago

Or even better, lose the extra .");" .. what is that doing there?

You Old Fool · Accepted Answer · 2015-04-03 07:12:46Z

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 3, 2015 at 7:12

answered Apr 3, 2015 at 6:52

You Old Fool

23.2k14 gold badges92 silver badges119 bronze badges

6 Comments

user3763711 Over a year ago

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 Over a year ago

Yes it works! Thank you! That's a smart way to concatenate them together, lesson learned

Bart Friederichs Over a year ago

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.

You Old Fool Over a year ago

@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.

Bart Friederichs Over a year ago

@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.

|

Bivin · Accepted Answer · 2015-04-03 06:45:40Z

0

Your sql query is wrong change the query to

SELECT *
FROM Reservations
WHERE ResDate='$date'

You forgot to add '' for $date

answered Apr 3, 2015 at 6:45

Bivin

1061 silver badge8 bronze badges

Comments

Ajay Chaudhary · Accepted Answer · 2015-04-03 07:06:47Z

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 3, 2015 at 7:06

answered Apr 3, 2015 at 6:57

Ajay Chaudhary

3082 silver badges14 bronze badges

Collectives™ on Stack Overflow

PHP string to MySQL date

5 Answers 5

2 Comments

3 Comments

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

3 Comments

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related