inserting datetime string into MySQL results in 0000-00-00

Question

I'm trying to add job info to my DB from an API.

One of my feeds supplies a date(posted) and an expiration date.

I'm trying to insert both fields (along with others).

The date gets entered fine, however the expiry date gets set to the default 0000-00-00 00:00:00

example data from api:

{  
    "jobId":30081256,
    "expirationDate":"22/08/2016",
    "date":"11/07/2016"
}

I parse the dates ready to be entered like this:

$job_date = $job->date;
$timestamp = strtotime(str_replace('/', '-', $job_date));
$date = date("Y-m-d H:i:s", $timestamp);

//expiry
$job_expiry = $job->expirationDate;
$expiry_timestamp = strtotime(str_replace('/', '-', $job_expiry));
$expiry_date = date("Y-m-d H:i:s", $expiry_timestamp);

when I echo the results of $date and $expiry_date, i get:

2016-07-11 00:00:00

and

2016-08-22 00:00:00

to me all looks good and the dates are formatted as sql would expect, but the expiry date does not get set.

I'm not sure if it has something to do with my query (shortened to show the fields in question - all other fields insert correctly):

"INSERT INTO 
jobs2 (jobref, date, expiry)
VALUES ('"
.$jobref
."','"
.$date
."','"
.$expiry_date
."') ON DUPLICATE KEY UPDATE
`date` = VALUES(date),
`expiry` = VALUES(expiry)"

Maybe the ON DUPLICATE part? The job may already be in my database, but sometimes it has been refreshed/ updated and the posted date or expiry may be different so I want to keep them updated with the latest values from the API.

whats going on here?

table structure as requested:

CREATE TABLE `jobs2` (
      `date` datetime NOT NULL,
       `title` varchar(200) COLLATE latin1_general_ci NOT NULL,
       `company` varchar(100) COLLATE latin1_general_ci NOT NULL,
       `email` varchar(100) COLLATE latin1_general_ci NOT NULL,
       `url` varchar(1200) COLLATE latin1_general_ci NOT NULL,
       `salarymin` int(20) DEFAULT NULL,
       `salarymax` int(20) DEFAULT NULL,
       `benefits` varchar(100) COLLATE latin1_general_ci NOT NULL,
       `salary` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
       `jobtype` varchar(30) COLLATE latin1_general_ci DEFAULT NULL,
       `full_part` varchar(30) COLLATE latin1_general_ci DEFAULT NULL,
       `salary_per` varchar(20) COLLATE latin1_general_ci DEFAULT NULL,
       `location` varchar(100) COLLATE latin1_general_ci DEFAULT NULL,
       `country` varchar(30) COLLATE latin1_general_ci NOT NULL,
       `description` varchar(3000) COLLATE latin1_general_ci NOT NULL,
       `category` varchar(100) COLLATE latin1_general_ci DEFAULT NULL,
       `image` varchar(150) COLLATE latin1_general_ci NOT NULL,
       `latitude` float NOT NULL,
       `longitude` float NOT NULL,
       `town` varchar(50) COLLATE latin1_general_ci NOT NULL,
       `county` varchar(50) COLLATE latin1_general_ci NOT NULL,
       `location_id` varchar(255) COLLATE latin1_general_ci NOT NULL,
       `jobref` varchar(100) COLLATE latin1_general_ci NOT NULL,
       `partner` varchar(30) COLLATE latin1_general_ci DEFAULT NULL,
       `company_id` int(20) DEFAULT NULL,
       `featured` varchar(10) COLLATE latin1_general_ci DEFAULT NULL,
       `solr_stored` int(1) DEFAULT NULL,
       `expiry` datetime NOT NULL,
       UNIQUE KEY `location_id` (`location_id`),
       KEY `partner` (`partner`),
       KEY `company` (`company`),
       KEY `title` (`title`),
       KEY `company` (`company`),
       KEY `longitude` (`longitude`),
       KEY `latitude` (`latitude`),
       KEY `jobref` (`jobref`),
       KEY `jobtype` (`jobtype`),
       KEY `company_id` (`company_id`),
       KEY `salarymin` (`salarymin`),
       KEY `salarymax` (`salarymax`),
       KEY `salary_per` (`salary_per`),
       KEY `date` (`date`),
       FULLTEXT KEY `title` (`title`),
       FULLTEXT KEY `company` (`company`),
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

@rpsep2 - well, this doesn't make a lot of sense. You have multiple not-null columns that have no default, so you INSERT query can't run successfully at all. You can't insert a record with only `jobref`, `date` and `expiry` without specifying `title`, `company`, `email` etc. — Galz, Jul 15 '16 at 22:51
could you post your query without the harcoded dates??? how are you inserting the dates to your query? — aampudia, Jul 15 '16 at 23:03
sorry, i shortened the query as pasting the entire thing is pretty huge, as it includes job desciption etc. every other column inserts fine, its just the expiry that sets to the default 0000-00-00 00:00:00 instead of the actual expiry date — rpsep2, Jul 15 '16 at 23:04
What do you see if you echo the SQL after you've substituted the variables? — Barmar, Jul 15 '16 at 23:49
I would highly recommend using prepared statements for setting the parameters into the query. It saves you the trouble of messing around with escaping and quotes, plus prevents mysql injection. See http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Galz, Jul 15 '16 at 23:52
@Barmar i get a query as i would expect it, e.g. INSERT INTO jobs2 (jobref, date, expiry) VALUES ('30081256','2016-07-11 00:00:00','2016-08-22 00:00:00') ON DUPLICATE KEY UPDATE `date` = VALUES(date), `expiry` = VALUES(expiry) — rpsep2, Jul 16 '16 at 00:08
I suspect there's a problem elsewhere in the query, so those date-time strings are not actually being assigned to the correct columns, and some other values that aren't legal dates are. — Barmar, Jul 16 '16 at 00:15
But we can't see that problem when you show just the shortened version. — Barmar, Jul 16 '16 at 00:16
Agree with @Barmar. Specifically inserting a string on int is not going to do that (you will get an error), BUT inserting 0 will do this. And the column right before expiry is `solr_stored INT(1)` - guessing the value is 0? — Galz, Jul 16 '16 at 00:21
Maybe the problem is that the column names are in the wrong order compared to the values. — Barmar, Jul 16 '16 at 00:26
Isn't `date` a reserved keyword in MySQL? Then you need to wrap the column name with back ticks: `(jobref, \`date\`, expiry)` — M. Eriksson, Jul 16 '16 at 00:56

inserting datetime string into MySQL results in 0000-00-00

0 Answers0