Jun
1
2011

Convert RSS pubDate to MySQL Datetime

Today in creating an application to parse through many RSS feeds I needed a quick function to convert RSS publish date to a valid MYSQL datetime string.

A quick Google search revealed 2 things:

  • There was not a copy and paste function readily available
  • There were many unanswered posts asking for such a function

So I thought I would take a few minutes out of my afternoon to put together a quick solution.

The code:

	function pubDateToMySql($str) {
		return date('Y-m-d H:i:s', strtotime($str));
	}

Example usage:

	$sql = "UPDATE yourtable SET date='".mysql_real_escape_string(pubDateToMySQL($pubDate))."' WHERE id='your_id_int'";
	$result = mysql_query($sql);

Explanation of example code:
There are a couple assumption made

  • the variable $pubDate has already been set
  • you are updating an exiting table by ID and you already have your ID

For security reasons the query is wrapped with PHP’s mysql_real_escape_string(), however you should make sure to sanitize your data according to its source.

Well this should get you pointed in the right direction, happy coding…

4 Comments + Add Comment

  • I have loads of rss from other websites that I need to process into a mysql database using php

    any idea on how to do this !?

    thanks a lot for support !

    Looking forward to your reply
    Kind Regards
    Sheran

    Thumb up 0 Thumb down 0

    • Hey Sharon,

      This task is not as difficult as it may seem. Have you ever used cURL?

      Also assuming your using PHP5 (hopefully not 4.x) there is the simple XML library that will allow you to parse your xml and easily insert it into your database.

      Depending on the complexity of you XML schema you can easily turn the returned string from your curl expression to a PHP array like so:

      $php_Array = json_decode(json_encode((array) simplexml_load_string($CURL_RETURNED VALUE)),1);
      

      Hope this gets you pointed in the right direction. Good Luck and happy coding

      Tim

      Thumb up 1 Thumb down 0

  • Thanks. I’ve been looking for this!

    Thumb up 0 Thumb down 0

  • Thank you Tim! Been looking for this.

    Thumb up 0 Thumb down 0

Leave a comment

Show/Hide Footer Actions

Status: Available for your project.