I really have no idea what's going on here. I'm trying to add a RSS feed to my blog. Since RSS requires the date to be in the RFC822 format, I'm using the PHP date function to convert my original timestamp (0000-00-00 00:00:00). The problem is that the date and time being displayed for all my posts in the feed is 1 January 1970 01:33. Here's the code:
<?php
while ($row = $query->fetch_assoc()) {
?>
<item>
<title><?php echo $row['title']; ?></title>
<description>Item description</description>
<link>http://example.com/blog.php<?php echo '?id='.$row['id']; ?></link>
<pubDate><?php echo date('r', $row['date_posted']); ?></pubDate>
</item>
EDIT: Here's my query. Is it here that I convert to a Unix timestamp?
$query = $db->query("
SELECT `posts`.`id`,
`posts`.`cat_id`,
`posts`.`title`,
`posts`.`contents`,
`posts`.`date_posted`
FROM `posts`
ORDER BY `posts`.`date_posted`
DESC
LIMIT 10");
EDIT2: Thanks for all the help, but it still doesn't work. Admittedly, I'm not quite grasping this, but I'm sure there's an easy solution. The whole feed.php document is actually not that long so here's all the code, in case I missed to mention some important detail. Oh, and the dates are stored correctly in the date_posted column in the database.
<?php
$db = new mysqli('host', 'user', 'pass', 'db');
$query = $db->query("
SELECT `posts`.`id`,
`posts`.`cat_id`,
`posts`.`title`,
`posts`.`contents`,
`posts`.`date_posted`
FROM `posts`
ORDER BY `posts`.`date_posted`
DESC
LIMIT 10");
if ($db->affected_rows >= 1) {
echo '<?xml version="1.0" encoding="iso-8859-1" ?>' ?>
<rss version="2.0">
<channel>
<title>Title</title>
<description>RSS feed</description>
<link>http://example.com</link>
<?php
while ($row = $query->fetch_assoc()) {
?>
<item>
<title><?php echo $row['title']; ?></title>
<description><?php echo substr($row['contents'], 0, 150) ?></description>
<link>http://example.com/blog.php<?php echo '?id='.$row['id']; ?></link>
<pubDate><?php echo date('r', $row['date_posted']); ?></pubDate>
</item>
<?php
}
?>
</channel>
</rss>
<?php
}
?>
what's the raw value of $row['date_posted']
? Remember that date()
expects a PHP timestamp, which is a plain unix timestamp - integer representing seconds since Jan 1/1970. If you feed it a string (e.g. 2012-12-11 08:55:00
, you'll just get a mangled value out, as PHP tries to convert that string to an int:
$now = '2012-12-11 08:56:00';
echo date('r', $now); // Wed, 31 Dec 1969 18:33:32 -0600
echo date('r', (int)$now); // Wed, 31 Dec 1969 18:33:32 -0600
echo (int)$now; // 2012
echo date('r', 2012); // Wed, 31 Dec 1969 18:33:32 -0600
You'll have to convert your date field to a unix timestamp in MySQL, or use MySQL's own date formatting functions:
SELECT UNIX_TIMESTAMP(date_posted), ...
or
SELECT DATE_FORMAT(date_posted, 'format string here'), ...