Looking for a simpleXML walk through

[woodsonoversoul]

What I'm trying to do is:

1) Retrieve text (formatted as xml) from the database

2)Convert the text to xml

3)Add a single element to the root of the xml

4)Turn the xml back into text

5)Update the database with the new text (xml)

 

The xml/text I have stored in the database looks like:

<userFavSongs id="root"></userFavSongs>

 

simpleXML looks like it will work, but I'm open to any methods available.

 

I'm trying to add in a node that looks like <song>some song id</song>

 

If any of this looks incorrect or like I'm doing unnecessary steps, let me know, this is my first time working with strings converted to xml. So all help is appreciated. Anyone have any tutorials/examples? Thanks

[rhodesa]

http://devzone.zend.com/node/view/id/651

[woodsonoversoul]

Thanks rhodesa, still not really seeing how to create and add nodes, or convert from text to xml...

[woodsonoversoul]

Here's the code I'm using:

if(mysql_num_rows($result) > 0){
  
  while($row = mysql_fetch_array($result)){
    //check if the current songs artist is in the artist array
    $userFavSongs = $row['favorite_songs'];
    echo $userFavSongs;
    }//end while
    
    //I need to define this as <xml>
    //and probably put everything in containing tags
    $userFavSongsXml = simplexml_load_string($userFavSongs) or die ("Unable to load XML!"); ;
    $userFavSongsXmlRoot = $userFavSongsXml->userFavSongs[0];
    $userFavSongsArray = array();
    //assign songs to array
    foreach ($userFavSongsXml->song as $song) {
      $userFavSongsArray = $song;
    }

    //$userFavSongsArray = $userFavSongsXmlRoot->getElementsByTagName('song');
    
    if($incORdec == 'inc'){
      $userFavSongsXml->addChild('song', $songId); 
      $userFavSongs = (string)$userFavSongsRoot;
      }

 

And I'm getting an, "Unable to load XML" error. Is my xml not formatted correctly? Is one empty element illegal?

[woodsonoversoul]

bump before shower for work...

[woodsonoversoul]

changed what's the text stored in the DB to:

 

<?xml version="1.0"?><userFavSongs></userFavSongs>

 

still no luck...

[rhodesa]

first, you do know that after you add a node, it's just in that XML object right? you still have to write it back to where it came from?

 

and if you already have a DB setup, why don't you use the DB to store the favorite songs, instead of XML in a TEXT field?

[woodsonoversoul]

I thought the beauty of XML was that it was plain text. I'm not really familiar with "multidimensional" (or database within a database) databases, and, to me, they sound like almost they same amount of work to traverse/work though as XML. If I'm going about this totally the wrong way I can change it, I'm really just looking for some guidance...

[woodsonoversoul]

bump for this morning...

[RichardRotterdam]

I'm not really familiar with "multidimensional" (or database within a database) databases,

A database within a database  ??? That does not sound right at all.

 

What I'm trying to do is:

1) Retrieve text (formatted as xml) from the database

How did the xml get in the database in the first place? Do you have the option to parse the xml before you insert it into the database? That way you can simply use a query which will output information that's directly usable.

 

[rhodesa]

Sorry if it's not what you want to hear, but you are going at this the wrong way. It's not a database within a database, but rather tables that relate to each other.

 

By the sounds of it, you already have some sort of Users table. Where each row in the table represents a unique user. That is a great start. Next, we want to keep track of of each users' favorite songs. While reading a book on database design would help a lot, the general rule of thumb is not to have "many-to-many" relationships. You want to keep it to "one-to-one" or "one-to-many". This is a one-to-many case since each user can have several favorite songs.

 

So, our favorite songs table (let's call it 'fav_songs') needs to track the user's id and the song id. So it's a simple 2 column table. (This is just the start, feel free to add other columns to track things like when they added that favorite, how much they like it, etc). So, the two columns would be the same datatype as your user_id column in your users table and the song_id column in your songs table. Adding a favorite is as simple:

INSERT INTO fav_songs (user_id,song_id) VALUES ('$user_id','$song_id')

To get a user's favorite songs:

SELECT * FROM fav_songs WHERE user_id = '$user_id'

You can even join it with the songs table:

SELECT * FROM fav_songs f LEFT JOIN songs s ON f.song_id = s.song_id WHERE f.user_id = '$user_id'

 

In sticking with tables, not only will you find it significantly faster, it also allows you to relate the data across users. For example, what if you want to know how many users have song_id 12 in their favorites? With the XML, you would have to open and parse the XML for every user...with the tables you can just run a simple query:

SELECT COUNT(*) FROM fav_songs WHERE song_id = '12'

 

I hope that helps explain it

[woodsonoversoul]

Thanks so much, that's exactly what I'll do. That's one of the best forum responses I've ever gotten. Thanks again!