Date not being sent over to mysql

Question

I have looked everywhere, but I can't seem to find the correct code. I have an html form take in a date from the user using the "date" input.

index.html:

<form name="myform" action="process.php" method="POST">
     Date: <input type="date" name="date"><br>
     <input type="submit" value="Submit">
</form>

Process.php:

$link=mysqli_connect("my.sql.server", "username", "psswd", "database_name")
$date = $_POST['date'];
mysqli_query($link,"INSERT INTO visit_exclude_dates (date) VALUES (CAST $date AS DATE)");

the value of $date when it comes out of the form is in the yyyy-mm-dd format, and I am able to successfully write to the database. My problem is that when I read the database, the date is being shown as 0000-00-00. So I assume that the problem is with this line:

mysqli_query($link,"INSERT INTO visit_exclude_dates (date) VALUES (CAST $date AS DATE)");

EDIT: I would like to note that visit_exclude_dates is the name of my table

What makes you believe you can "successfully write to the database"? — Oswald, Jun 07 '13 at 19:13
I suppose successfully is not a good term, but I know something is being written to the database, because more indexes are being added to the 'date' variable of my table. I am able to see this in phpMyAdmin, and by using 'SELECT date FROM visit_exclude_dates' — dan.m.kumar, Jun 07 '13 at 19:34

Eric Wich · Answer 1 · 2013-06-07T19:35:38.703

2

Try adding ticks around the date field in your query. Provided you're SURE the input is Y-m-d format, It'll most likely fix your issue:

$date = date("Y-m-d", strtotime($_POST['date']));
$result = mysqli_query($link,"INSERT INTO visit_exclude_dates (date) VALUES ('{$date}')");

if(!$result) {
    echo "Error: " . $link->error);
    die();
}

$result->close();
$link->close();

edited Jun 07 '13 at 19:35

answered Jun 07 '13 at 19:11

Eric Wich

1,504
10
8

Editing and removing the Cast function. If you sanitize your input with strtotime, this is much neater. – Eric Wich Jun 07 '13 at 19:15
How would I go about doing that? also the code above, was not able to write to the database, no values were returned, not even 0000-00-00 – dan.m.kumar Jun 07 '13 at 19:27
What do you mean no values were returned? This code will return FALSE if it didn't work, and a result object if it did. Did you look in your database? Did it write anything to the db at all? I am editing my code with something that will show you if there was a mysql error. – Eric Wich Jun 07 '13 at 19:33
I am using phpMyAdmin, before I updated the code, my table was filled with my attempts at adding dates (and it would show each of them as 0000-00-00, unless I added queries to the table by using the phpMyAdmin interface). Now nothing is being updated. – dan.m.kumar Jun 07 '13 at 19:37
Put in that code with the error checking, and let me know if you get a MySQL error. It'll help us get to the bottom of it. – Eric Wich Jun 07 '13 at 19:38
Nothing is being echoed out. I went to phpMyAdmin, but my table didn't change – dan.m.kumar Jun 07 '13 at 19:43
If it's just dying and not doing anything, you probably have a syntax error somewhere in your code. Put an echo down at the bottom like echo "made it"; and see if it prints. If not, your script is dying before it executes fully. Check your web server logs for PHP Fatal errors and see what you find. – Eric Wich Jun 07 '13 at 19:47
Wow!, I echoed "made it"; at the bottom and It didn't show up, and I don't have access to those web server logs at the moment (I'm on a very restricted computer). – dan.m.kumar Jun 07 '13 at 19:54
Yeah you'll need to go through your code and look for syntax errors. Missing semicolons, make sure your parenthesis match up, etc. The code I posted is syntactically correct, I made a test script and it's all good. Good luck! – Eric Wich Jun 07 '13 at 20:06

score 0 · Answer 2 · edited Jun 07 '13 at 19:14

0

Use strtotime

You will need to force a specific pattern that the user has to input.

edited Jun 07 '13 at 19:14

answered Jun 07 '13 at 19:11

Alexander Cogneau

1,286
4
12
25

Date not being sent over to mysql

2 Answers2

Linked