XML and mySQL (Help and Insight Needed)

[shauntain]

Hello,

 

I have been trying to research this for a little while and I was wondering if I could get some help and insight.

 

I desire to store raw user generated XML in a mySQL database using PHP.  First is this advised?

 

The reason I want to do this is because I will be asking users to fill out customized options which I want to easily store and retrieve for the user.

 

The problem with this is I cannot find any efficient way to sanitize the XML data before inserting into the mySQL database. 

These wont work:

• mysql_real_escape_string($xml) - removes details needed for traversing the XML with PHP.
  
• htmlentities($xml) - also removes necessary details
  

 

Now with the data I discussed above I cannot envision a better way to store this information, as it will be different for each user and with varying amounts of variables.

 

Also for the data which is not varying - such as maybe a XML DOM containing usernames and passwords - is there an efficient way to convert from a mySQL response to XML and back from XML to a mySQL query without individually extracting the data.

 

Such that I can pass:

<user>shane</user>

<password>pw</password>

 

and an sql query be made such as INSERT INTO table (user, password) VALUES(shane, pw) and then take a response and return the xml.  Is there a method available for this or do I need to write a custom class?

 

Thank you for the assistance.  I have left my php books at school and have hit a dead end.

Shane.

[trq]

I cannot envision a better way to store this information, as it will be different for each user and with varying amounts of variables.

 

How about you have two tables. One for the data required by the login system, and another to store a users details in key -> value pairs. eg;

 

CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uname VARCHAR(80),
  upass VARCHAR(80)
);


CREATE TABLE users_details (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uid INT,
  k VARCHAR(80),
  v TEXT
);

 

Now, creating a user:

 

INSERT INTO users (uname, upass) VALUES ('thorpe',MD5('whatever'));

 

You then use mysql_insert_id within your logic to grab that new users `id`. From there you can insert there details. ($uid represent the value retrieved via mysql_insert_id)

 

INSERT INTO users_details (uid,k,v) VALUES ($uid,'email','foo@foo.com');
INSERT INTO users_details (uid,k,v) VALUES ($uid,'phone','0418962558');

 

You can then use a fairly simply join to retrieve a user and all there details.

 

SELECT uname,k,v FROM users AS u,users_details AS details WHERE u.id = details.uid;