MySQL timestamp not working with PHP strtotime :(

[junglyboi]

I am having some trouble parsing a mysql timestamp into a php date().

In my 'promoters' table, I have a timestamp column which defaults to now() so that when I update a record, the timestamp updates, and I have a "last update" value to work with.

I'm using php to create an xml page.  Here's my code:

[code]

<?php

header("Content-type: text/xml");

// index.php

echo "<url>";
echo "<loc>http://www.ticketstone.com/</loc>";
echo "<lastmod>";
$mod = date('Y-m-d\TH:i:s-05:00', strtotime(now));
echo $mod;
    echo "</lastmod>";
    echo "<changefreq>hourly</changefreq>";
    echo "<priority>0.7</priority>";
echo "</url>";

// events.php q=all "All Current Events"

echo "<url>";
echo "<loc>http://www.ticketstone.com/events.php?q=all</loc>";
echo "<lastmod>";
$mod = date('Y-m-d\TH:i:s-05:00', strtotime(now));
echo $mod;
    echo "</lastmod>";
    echo "<changefreq>hourly</changefreq>";
    echo "<priority>0.8</priority>";
echo "</url>";

// events.php q=pro "All Current Events by Promoter"

$db = mysql_connect('localhost', 'user', 'password');
mysql_select_db('database', $db);

$result = mysql_query("SELECT * FROM promoters ORDER BY promoter_id", $db);
while ($p = mysql_fetch_array($result)) {
echo "<url>";
echo "<loc>http://www.ticketstone.com/events.php?q=pro&amp;v=";
echo $p['promoter_id'];
echo "</loc>";
$time = date('Y-m-d\TH:i:s-05:00', strtotime($p['promoter_mod']));
echo "<lastmod>" . $time . "</lastmod>";
echo "<changefreq>hourly</changefreq>";
echo "<priority>0.3</priority>";
echo "</url>";
}

echo "</urlset>";

?>
[/code]

promoter_mod is a timestamp column in my table so that I always have the last update time.  So for the first record (promoter_id "1000"), the promoter_mod is "20061120071925".  When I use the strtotime($p['promoter_mod']), I get a result of -1, which then of course gives me the infamous 69 date when I try to format it with date().  Can anyone help me with the code to be able to take the timestamp in mysql and turn it into "Y-m-d\TH:i:s-05:00"?  Puhleese?

Chris

[craygo]

you would have to break up the stamp
[code]<?php
$hr = substr($p['promoter_mod'], 8, 2);
$min = substr($p['promoter_mod'],10,2);
$sec = substr($p['promoter_mod'],12,2);
$mon = substr($p['promoter_mod'],4,2);
$day = substr($p['promoter_mod'],6,2);
$year = substr($p['promoter_mod'],0,4);
$timestamp =  mktime($hr, $min, $sec, $mon, $day, $year);

$time = date('Y-m-d\TH:i:s-05:00', $timestamp);
?>[/code]

Ray

[junglyboi]

sweet... works perfectly :D thaaaanks :D

[roopurt18]

I find that it's much easier to let the database handle this type of thing for you:
[code]<?php
$sql = "SELECT *, "
      . "DATE_FORMAT(promoter_mod, '%Y-%m-%d\t%T') AS promoter_mod_disp "
      . "FROM promoters ORDER BY promoter_id";
$result = mysql_query($sql);
?>[/code]