0

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
}
?>
miken32
  • 42,008
  • 16
  • 111
  • 154

2 Answers2

2

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'), ...
Marc B
  • 356,200
  • 43
  • 426
  • 500
  • The column which $row['date_posted'] fetches rows from is of datatype timestamp in mysql. It's default mode is set to 0000-00-00 00:00:00. The specific posts are hence in the format 2012-12-09 16:42:12 and so on. This is unknown territory for me. Do I convert the timestamp in my php document or in the database itself? –  Dec 11 '12 at 15:12
  • I included my query above. I would greatly appreciate an explanation of where I convert the date field. –  Dec 11 '12 at 15:34
  • if you use the unix_timestamp option, you code should "just work" as is without changes. you'll get a timestamp in a format that PHP can accept. If you use the date_format option, then you yank out all the date formatting stuff in PHP and simply output the string you get from mysql. – Marc B Dec 11 '12 at 15:38
  • Yeah, but where do I actually write "SELECT UNIX_TIMESTAMP..."? Do I include it in my query? If so, where in the query? Thanks! –  Dec 11 '12 at 15:46
  • You were right in theory, but I couldn't understand how to implement your solution. I found another way though: simply using the strtotime function in the following manner: echo date('r', strtotime($row['date_posted'])); –  Dec 11 '12 at 23:46
0

That's because php's date object is a UNIX Timestamp. It starts at 1/1/1970. You need to check for dates that are outside that range, such as 0000-00-00 and handle it manually.

Also, check your database and make sure your timestamps are being stored correctly. If you are expecting a real date and are getting 0000-00-00 then you probably inserted the PHP timestamp directly, MySQL does not recognize that format. You have to insert timestamps in as strings in this format: Y-m-d H:i:s ex: INSERT INTO table (time) VALUES ('2012-01-01 13:30:00') WHERE id=1. You can use php's date('Y-m-d H:i:s') to get this format.

If you expect some dates to be 0's, while others are actual dates, here's how you can insert an inline if to replace the 0000 dates with whatever you want, while converting normal dates as desired.

<?php
while ($row = $query->fetch_assoc()) {
?>
<item>
    <title><?php echo $row['title']; ?></title>
    <description>Item description</description>
    <link>http://mysite.com/blog.php<?php echo '?id='.$row['id']; ?></link>
    <pubDate><?php echo strncmp($row['date_posted'], '0000-00-00', 10) == 0 ? 'NOT PUBLISHED' : date('r', $row['date_posted']); ?></pubDate>
</item>
Levi
  • 2,103
  • 14
  • 9
  • Thanks for the effort, but I copied the code snippet you provided and it still displays the wrong date. I've also checked that the dates are stored correctly in the database itself, which they are. I included all the code for feed.php above in case you might spot some other error there. Thanks again! –  Dec 11 '12 at 21:30