Question:

I am parsing out HTML from another page, that includes a date and time . I need to store this date/time in a MySQL database. I successfully constructed a string that has the timestamp in it. The string right now is set to “02/16/2010 12:51:47″ . When I try to insert this string into MySQL, I am given all zeros. When I attempt to use the strtotime function, “1969-12-31 18:00:00″ is inserted into the table.

$dispatchTimestamp1 is equal to “02/16/2010 12:51:47″ . The attached code is what I have used to try to convert the string to a date.

I’ve been working on this on and off for 2+ weeks. Any help would be great.

Code Snippet:

$mysqldate = date(“Y-m-d H:i:s”, strtotime($dispatchTimestamp1));

Solution:

See if this makes sense to you.  You would need to add the appropriate DB connect and select statements, (and of course execute the query) and you might want to add some error checking.

Are we on firm ground in understanding that your “dispatch_timestamp” column is defined as a DATETIME field?

One final note – the strategy of scraping a web site to acquire input data is always a little “iffy.”  I do it all the time, but there are a number of risks, not the least of which is that the owners of the site may change the format and this can cause your scrape script to break.  So you have to be on guard against this with very detailed error checking before you rely on the data you’ve scraped out of the page.  A better strategy is to ask the owners for an API that presents the data in some predictable format, such as CSV or XML.

You can install this script and run it as-is to see how the moving parts work.  Best regards, ~Ray

<?php // RAY_temp_farrissf.php
error_reporting(E_ALL);
echo “<pre>\n”;

// READ THE HTML
$htm = file_get_contents(‘http://oasis.pjm.com/drate.html’);

// ACTIVATE THIS TO SEE THE RAW DATA
// VAR_DUMP($htm);

// REMOVE THE UNNECESSARY STUFF
$txt = strip_tags($htm, ‘<td>’);

// SET FIRST FIELD DELIMITERS
$arg_a = ‘<TD width=350 align=center>’;
$arg_z = ‘</TD>’;

// ISOLATE THE FIRST DATE/TIME FIELD AND CONVERT TO ISO DATE
$pos_a = strpos($txt, $arg_a) + strlen($arg_a);
$pos_z = strpos($txt, $arg_z, $pos_a);
$date  = substr($txt, $pos_a, $pos_z-$pos_a);
$iso   = date(‘c’, strtotime($date));

// SET THE SECOND FIELD DELIMITER AND REMOVE THE FRONT OF THE STRING
$arg_a = ‘<TD align=center>’;
$pos_a = strpos($txt, $arg_a);
$txt   = substr($txt, $pos_a);

// REMOVE THE EXTRANEOUS INFORMATION
$txt   = str_replace($arg_a, ”, $txt);

// EXPLODE THE STRING INTO AN ARRAY
$arr = explode(‘</TD>’, $txt);

// TIDY UP THE ARRAY, ASSIGNING NAMED KEYS TO RELATIVE POSITIONS
$out = array();
$num = 1;
foreach ($arr as $key => $val)
{
$val = trim($val);
if (empty($val)) continue;
$out["dispatch" . "$num"] = $val;
$num++;
}

// CONSTRUCT THE QUERY STRING
$sql = “INSERT INTO dispatch_table2 ( dispatch_timestamp, “;

// COLUMN NAMES SEPARATED BY COMMAS
foreach ($out as $key => $val)
{
$sql .= “$key” . ‘,’;
}
$sql = rtrim($sql, ‘,’);

// MIDPOINT IN THE QUERY – INCLUDING THE ISO TIMESTAMP
$sql .= ” ) VALUES ( ‘$iso’, “;

// COLUMN VALUES SEPARATED BY COMMAS
foreach ($out as $key => $val)
{
$sql .= “‘$val’,”;
}
$sql = rtrim($sql, ‘,’);

// END OF QUERY STRING
$sql .= ” )”;

// SHOW THE WORK PRODUCT
var_dump($sql);