Getting blank date adding date to database (wordpress, php)

Question

Basically I'm trying to add a date to my database (just a date, not datetime)

here's what I have so far:

$wpdb->insert(
    'my_table',
     array('date-adding'=>date('Y-m-d', '1994-06-14')), 
     array('%F')
);

it simply inserts 0000-00-00 so its something simple that's wrong - probly the %F but can't figure out what.

Thanks

A missing inverted comma perhaps? Although quite why that doesn't just error out, I'm not sure. — Strawberry, Aug 27 '13 at 16:00
RTFM: http://php.net/date date() does **NOT** use a string for its timestamp input. You're trying to format the number `1994`, which is all that's going to be left of your date string after PHP tries to conver it to an integer, meaning you're going to be dealing with Jan 1st, 1970, approximately 12:33am. — Marc B, Aug 27 '13 at 16:01

George Yates · Accepted Answer · 2013-08-27T16:49:53.410

3

Your PHP date() function is wrong, what are you trying to accomplish? The second parameter should be a UNIX timestamp, not a date string. Since you're just feeding it a static time string too, just use '1994-06-14', no reason to try and use the date() function.

If you want something dynamic using a string, try this:

date('Y-m-d', strtotime($dynamic_date_string))

EDIT AFTER DISCUSSION: The root problem turned out to be the third parameter formatting string formatting the DATE as a float. Since there was no reason to format the date, removing that optional parameter fixed the issue. Also, formatting the input as a string using %s would have worked.

edited Aug 27 '13 at 16:49

answered Aug 27 '13 at 16:02

George Yates

1,237
9
16

Simply the user has an input - they enter their date of birth and it saves it to the database. So i'm simply trying to add any date I have as a string to my database but everything keeps coming out blank :S – JamesB123 Aug 27 '13 at 16:07
@JamesB123 You could show us your input form then, to make it clearer as to "how" to answer. – Funk Forty Niner Aug 27 '13 at 16:08
Sorry I shortened everything for the question - right now my code is literally the above - i'm just passing a static string in ('1994-06-14') into my database table, all that's missing above is the php tags and global $wpdb; – JamesB123 Aug 27 '13 at 16:10
@JamesB123 If you're trying to pass data as a "string", then use double-quotes instead. I.e.: `$string="1994-06-14";` as compared to `$string='1994-06-14';` is not treated the same. – Funk Forty Niner Aug 27 '13 at 16:12
Tried that, still blank - coming up in database as 0000-00-00 – JamesB123 Aug 27 '13 at 16:15
@JamesB123 Try using the following as an example: `` – Funk Forty Niner Aug 27 '13 at 16:21
@Fred-ii- Single vs. Double quotes has no effect here, in fact he should use single quotes since there's no special characters to parse. – George Yates Aug 27 '13 at 16:32
@GeorgeYates Ah ok thanks George. Trying to find a solution for the OP, seems like it's the `%F` that is giving problems – Funk Forty Niner Aug 27 '13 at 16:34
@JamesB123 Use the `$wpdb->insert` method without the third parameter, since you're formatting with `date()`, you don't need WordPress to format the data. Also, verify that the column name is `date-adding` – George Yates Aug 27 '13 at 16:34
1

@JamesB123 Also, in your script, play around with showing what `$wpdb` actually did. I would, after my `$wpdb->insert` run `var_dump($wpdb->last_query); exit();` to verify that the query executed was what I wanted. – George Yates Aug 27 '13 at 16:36
Aha! Thank you George, changed it back to %s and it worked, ofc cause the php converts it to the correct date format, ah thankyou! – JamesB123 Aug 27 '13 at 16:40
That var_dump line is really useful! Could you add the change to %f->%s in the answer (possibly include the var-dump as well - your choice) so I can accept it – JamesB123 Aug 27 '13 at 16:40
1

Ya, the third parameter is optional and is really only needed if you're sanitizing data, since you're forcing a format on your input, you don't need it. Also, look at [the documentation](http://codex.wordpress.org/Class_Reference/wpdb#Class_Variables), as those variables will help you get out of a lot of sticky situations. – George Yates Aug 27 '13 at 16:43
@GeorgeYates Right on George, glad to see a solution has been found, cheers :) – Funk Forty Niner Aug 27 '13 at 16:44
@GeorgeYates Definitely a +1 on my part. – Funk Forty Niner Aug 27 '13 at 17:00

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

1

Possible solutions:

Use strtotime() and date():

$originalDate = "2010-03-21";
$newDate = date("d-m-Y", strtotime($originalDate));

Source: https://stackoverflow.com/a/2487938/1415724

EDIT #2

Have a look at this:

<html>
<head>
<title>..</title>
<body>
<form action="work1.php" method="post">
Value1: <input type="text" name="date"><br>
<input type="Submit"/>
</form>
</body>
</head>
</html>

<?php
$username="root";
$password="abcdef";
$database="test_date";
$date=$_POST["date"];  // Your First Problem Post value
echo $date;
echo "i m here";
$date = date("Y/m/d", strtotime($date));   // 2nd Problem Date Format
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die("Unable to select database");

$query = "INSERT INTO date VALUES('','$date')";

mysql_query($query);

mysql_close();
?>

Source: http://www.daniweb.com/web-development/php/threads/300356/date-stored-in-mysql-as-0000-00-00

or:

$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();

Source: https://stackoverflow.com/a/16991316/1415724

"EDIT"

Given that the data is coming from an input form field, here is an updated answer.

If you're trying to pass your data as a "string", then use double-quotes instead.

I.e.: $string="1994-06-14"; as compared to $string='1994-06-14';

are not treated the same in PHP.

Try using the following as an example:

<input type="text" name="date_input" value="<?php echo htmlspecialchars($date); ?>" />

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 27 '13 at 16:01

Funk Forty Niner

74,450
15
68
141

Still coming out blank :S – JamesB123 Aug 27 '13 at 16:22
@JamesB123 It's harder for me than others to work without seeing full source code, sorry. – Funk Forty Niner Aug 27 '13 at 16:24
@JamesB123 Check what your DB table/values is set to. – Funk Forty Niner Aug 27 '13 at 16:24
@JamesB123 Have a look at this answer on SO http://stackoverflow.com/a/12120457/1415724 – Funk Forty Niner Aug 27 '13 at 16:25
thanks was having a look at that but still nothing, the only thing I can think is that the %F should be something else - in phpMyAdmin the format is literally set to date and comes out as 0000-00-00 if I enter an sql query into myAdmin everything works fine – JamesB123 Aug 27 '13 at 16:30
I can add other fields fine, if I have a VARCHAR column (say name) doing this: $wpdb->insert( 'my_table', array('forename'=>"james"), array('%s') ); works perfectly – JamesB123 Aug 27 '13 at 16:33
@JamesB123 What happens of you remove `array('%F')` ? – Funk Forty Niner Aug 27 '13 at 16:35
@JamesB123 Or try using double quotes `array("%F")` ? – Funk Forty Niner Aug 27 '13 at 16:35
@Fred-ii- `date("d-m-Y", strtotime($originalDate));` That's wrong, the MySQL DATE datatype requires 'Y-m-d' formatting. – George Yates Aug 27 '13 at 16:40
@GeorgeYates Thanks for that George, yet I included the source of the document underneath that: http://stackoverflow.com/a/2487938/1415724 --- was only quoting that from an SO accepted answer. – Funk Forty Niner Aug 27 '13 at 16:42

Getting blank date adding date to database (wordpress, php)

2 Answers2

Possible solutions:

EDIT #2

or:

"EDIT"