SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Generate xml document from data in mysql database

    Hi,

    I am looking for a tutorial that helps me with generating a xml document from information wich is i take from a mysql database. It's a reviews database.

    I saw the tut that describes how to parse xml but that's not what i want.

    I have PHP Version 4.3.4.

    Any help regarding this topic is appreciated.

  2. #2
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well you could do:
    PHP Code:
     exec('mysqldump -u[b]user[/b] -p -X [b]table [/b]> [b]dumpfile.xml[/b]'); 
    Aaron Brazell
    Technosailor



  3. #3
    SitePoint Member
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply but what has to be set if i want to use a particular reviewid, so basicly:
    select content from reviews where reviewid='1'

  4. #4
    SitePoint Addict Messiah's Avatar
    Join Date
    Jun 2001
    Location
    Bloomington, In.
    Posts
    216
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wrote a small script that does just that. Then incorporated it into my administrative module for adding news. Each time I add a new item, it creates the new rss feed on the fly. Here is how I accomplished this:

    PHP Code:
    <?php
    // filename '/feeds/makefeed.php'
    // function 'create an xml rss feed for syndication'

    // require mysql connection data
    require ( $_SERVER['DOCUMENT_ROOT'] . "/config.php" );
    require ( 
    $_SERVER['DOCUMENT_ROOT'] . "/mysql.php" );

    // open a file handler for writing the xml feed
    $filefopen $_SERVER['DOCUMENT_ROOT'] . "/rss.xml""w" );

    // start the xml document
    $_xml .= "<?xml version=\"1.0\" ?>\r\n";
    $_xml .= "<rss version=\"2.0\">\r\n";
    $_xml .= "<channel>\r\n";
    $_xml .= "<title>" $sitename "</title>\r\n";
    $_xml .= "<description>The personal blog of Jason G Trusty. Artist, Designer, Programmer.</description>\r\n";
    $_xml .= "<link>" $basehref "</link>\r\n";

    // query to select current blog entries
    $qry "SELECT * FROM s_news order by id DESC LIMIT 15";
    $rs mysql_query $qry );

    // create array of existing items using while loop
    while ( $row mysql_fetch_object $rs ) )
    {

        
    // define the number of words to limit by
        
    $max_length=600;

        
    // limit the number of words returned for each item
        
    $blog strip_tags $row->text );
        
    $blog str_replace "&""&amp;"$blog );
        
    $blog substr trim $blog ),0,$max_length );
        
    $blog substr $blog,0,strlen $blog )-strpos strrev $blog )," " ) );

        
    $subject strip_tags $row->subj );
        
    $subject str_replace "&""&amp;"$subject );

        
    // build the xml for each item
        
    $_xml .= "<item>\r\n";
        
    $_xml .= "<title>" $subject "</title>\r\n";
        
    $_xml .= "<link>" $basehref "/?page=comments&amp;action=read&amp;article=" $row->id "</link>\r\n";
        
    $_xml .= "<description>" $blog "</description>\r\n";
        
    $_xml .= "<pubDate>" date "D, d M Y h:i:s T"$row->date ) . "</pubDate>\r\n";
        
    $_xml .= "<guid>" $basehref "/?page=comments&amp;action=read&amp;article=" $row->id "</guid>\r\n";
        
    $_xml .= "</item>\r\n";

    }

    $_xml .= "</channel>\r\n";
    $_xml .= "</rss>\r\n";

    fwrite $file$_xml );
    fclose $file );


    ?>

    Not necessarily the most elegeant method, but it does what I need.
    Messiah | Ink-Press: web publishing simplified!

  5. #5
    SitePoint Member
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great Messiah, thanks for the code.. i am going to play with it.

    One question though, are those variables such <pubDate>, <guid>, <item> etc default ones or just made up by yourself?

    Thanks again.

  6. #6
    SitePoint Addict Messiah's Avatar
    Join Date
    Jun 2001
    Location
    Bloomington, In.
    Posts
    216
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Everything that my feed generator creates is valid rss 2.0 as per the specs.
    Messiah | Ink-Press: web publishing simplified!

  7. #7
    SitePoint Member
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool, i wonder what is wise: as i have categories and reviews. Is it good to make an .xml feed/rss page from every review or just by category? And what is the normal method: generating a new xml file and then a link to the file on every review?

    A bit confused on how i want to achieve it, thanks again.

  8. #8
    SitePoint Addict Messiah's Avatar
    Join Date
    Jun 2001
    Location
    Bloomington, In.
    Posts
    216
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I really can not advise you on your own personal implementation. I can only suggest that you get out there and see what others are doing and make an informed descision based on what you see and what you are wanting to accomplish. It is really a matter of personal preference, not of technical correctness.

    For example, I publish a feed on my site. It only contains the last 15 entries from a single category. Not to say that this is how you should implement yours, mind you. Only that its the most logical solution for the content my site has to offer.
    Messiah | Ink-Press: web publishing simplified!

  9. #9
    SitePoint Member
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aaah yes i see, thanks alot for the info.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •