[SOLVED] RFC 2822 / PHP / SQL

[pwnuspoints]

Hey there phpfreaks,

 

As always yor help is appriciated, Thanks in advance!

 

I'm working on creating a dynamic RSS feed using php/sql. The rss tutorial I've been referencing requires that I store dates in my database in RFC 2822 format or date("r"). However, when I try to upload a date in RFC 2822 format I get an error.

 

Error.Incorrect date value: 'Wed, 29 Apr 2009 15:44:58 -0500' for column 'date' at row 1

 

My date column is set as 'date' type, so why isn't this working?

 

More importantly, does anyone have a better tutorial for me on how to get a dynamic php RSS feed going? I've been working at this for eight hours. I'm familliar with xml and php - enough to know that this should be way easier. Every tutorial I've tried just doesn't work.

 

Thanks again!

[mikesta707]

does your column type HAVE to be a date type? can you just set it to varchar?

[pwnuspoints]

does your column type HAVE to be a date type? can you just set it to varchar?

 

No, it doesn't -- So, I'll change it to Varchar.

 

Then I guess my question becomes. How do I pull a varchar date from the database and re-format it using date() ?

[Kieran Menor]

Why can't you just store the unix timestamp? You can always use date('r', $timestamp) later on to get the right format. A unix timestamp requires less space and should be more efficient for sorting and such.

 

Edit: You would use am int or a date field to store this.

[mikesta707]

well if it is already saved in the correct format, then you wouldn't need to format it again would you. This is what I did when I wanted to make a dynamic RSS feed. I just formatted the data, had php write the xml, and then stored the formatted data in MySQL. though, in my case, the RSS feed and the mysql didnt really interact at all. Sorry if this doesn't help, but try posting the code that actually makes the RSS feed.

[pwnuspoints]

well if it is already saved in the correct format, then you wouldn't need to format it again would you. This is what I did when I wanted to make a dynamic RSS feed. I just formatted the data, had php write the xml, and then stored the formatted data in MySQL. though, in my case, the RSS feed and the mysql didnt really interact at all. Sorry if this doesn't help, but try posting the code that actually makes the RSS feed.

 

The code below is saved on my server in a directory named 'feed' with the filename 'index.php'

 

Naturally, I've removed my actual database name and password from the code below... But even with the code below. The only thing that ever seems to show is the channel info- It's almost as if it's not connecting to my database.

 

I've tried modifying this code and other code to match other database connections I've coded and tested... but still no luck.

 

<?php $db = new mysqli("localhost", "root", "dbpass", "dbname"); ?>

<?php header('Content-type: text/xml'); ?>

<?php echo "<?";?>xml version="1.0" encoding="iso-8859-1"<?php echo "?>";?>

<?php $cyear=date("Y"); ?>

<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom">

<channel>

<title>****</title>

<description>*****</description>

<link>*****</link>

<copyright>Copyright © <?php echo "$cyear"; ?> </copyright>

<atom:link href="***********" rel="self" type="application/rss+xml" />

<?php

 

$query = "SELECT * FROM table ORDER BY sn DESC LIMIT 0,15";

$results = $db->query($query);

$number = $results->num_rows;

 

for ($i = 1; $i <= $number; $i++) {

 

$row = $results->fetch_assoc();

 

$title = $row['title'];

$description = $row['body'];

$link = $row['link'];

$date = date("r", $row['timestamp']);

 

?>

<item>

<title><?php echo $title; ?></title>

<description><?php echo $description; ?></description>

<link><?php echo $link; ?></link>

<pubDate><?php echo $date; ?></pubDate>

<guid><?php echo $link; ?></guid>

</item>

<?php

 

}

 

?>

</channel>

</rss>

<?php

 

$db->close();

 

?>

[Mchl]

MySQL date format is YYYY-MM-DD hh:mm:ss

 

And do not store date as varchar! Ever!

[pwnuspoints]

MySQL date format is YYYY-MM-DD hh:mm:ss

 

And do not store date as varchar! Ever!

 

Thanks, Actually I think you've told me this before- when I was here with a different problem.

 

Ugh, I give up. I just want a step by step by step shamelessly-hand-holding tutorial on how to create an dynamic RSS feed from an existing database table. Please, won't someone point me to an informative guide?

 

Google has been unhelpful.

[mikesta707]

MySQL date format is YYYY-MM-DD hh:mm:ss

 

And do not store date as varchar! Ever!

 

Really? Your not supposed to do that? That's really interesting, and I never knew that. Is there a reason why, or is it just a bad practice? What could happen to the date if it was stored in a varchar?

[Daniel0]

MySQL date format is YYYY-MM-DD hh:mm:ss

 

And do not store date as varchar! Ever!

 

Really? Your not supposed to do that? That's really interesting, and I never knew that. Is there a reason why, or is it just a bad practice? What could happen to the date if it was stored in a varchar?

 

It's bad practice and it prevents you from using comparison operators and date functions efficiently on them using MySQL.

 

Why can't you just store the unix timestamp? You can always use date('r', $timestamp) later on to get the right format. A unix timestamp requires less space and should be more efficient for sorting and such.

 

You could use UNIX timestamps, but MySQL DATE(TIME) fields can store large ranges of dates.

[Mchl]

Really? Your not supposed to do that? That's really interesting, and I never knew that. Is there a reason why, or is it just a bad practice? What could happen to the date if it was stored in a varchar?

 

Nothing bad will happen, as long as you will not need to do any kind of sorting or selecting basing on this field. Then you will curse yourself. Not to mention, that date stored as string needs several times more bytes than DATE (4B) or DATETIME (8B) field.

 

pwnuspoints: In the attachment you will find a script I created to generate RSS from MediaWiki database. It cheats a little when it comes to some elements, but in general works fine and generates valid RSS2.0 feed.

 

[attachment deleted by admin]

[pwnuspoints]

...attachment...

 

Thanks. I'll study that code.