Export Array of SimpleXMLElement objects to MySQL Database

Question

I have a SOAP Response from a Web Service and have extracted the XML data as a SimpleXMLElement. I have then iterated through this object to extract the various fields I need and save them into an array, which becomes an array of SimpleXMLElement objects.

I am now trying to export this data into a MySQL Database which, according to my research, means turning the array into a String and then using mysql_query("INSERT INTO (whatever) VALUES (whatever)");. I have tried implode and serialize but neither work and I get the error:

Fatal error:  Uncaught exception 'Exception' with message 'Serialization of 'SimpleXMLElement' is not allowed'

This is what the array I have created from the SimpleXMLELement looks like:

Array
(
[0] => Array
    (
        [uid] => SimpleXMLElement Object
            (
                [0] => WOS:000238186400009
            )

        [journal] => SimpleXMLElement Object
            (
                [@attributes] => Array
                    (
                        [type] => source
                    )

            )

        [publication] => SimpleXMLElement Object
            (
                [@attributes] => Array
                    (
                        [type] => item
                    )

                [0] => Abundance of hedgehogs (Erinaceus europaeus) in relation to the density and distribution of badgers (Meles meles)
            )

        [year] => 2006
        [author1] => SimpleXMLElement Object
            (
                [0] => Young, RP
            )

        [address] => SimpleXMLElement Object
            (
                [0] => Cent Sci Lab, Sand Hutton, Yorks, England
            )

        [author2] => SimpleXMLElement Object
            (
                [0] => Davison, J
            )

        [author3] => SimpleXMLElement Object
            (
                [0] => Trewby, ID
            )

        [citations] => SimpleXMLElement Object
            (
                [@attributes] => Array
                    (
                        [local_count] => 15
                        [coll_id] => WOS
                    )

            )

    ) ... etc ...
)

Can anyone help me with the method to get this data into my database, please? Do I need to change it into (yet) another format?

What is the problem? Do you not understand the error message? — Sverri M. Olsen, Sep 04 '14 at 11:02
Yes, I understand the error message, I can't use those methods to turn the data into a String as it's classing it as a `SimpleXMLElement` rather than an array. I want to know how to get the array data displayed above into a MySQL database. Apparently the data needs to be in String format in order to insert it into a database with `mysql_query` — John Dawson, Sep 04 '14 at 11:08
@jayrdi: Please, _please_, ***please*** stop using `mysql_*`. It's deprecated, and unsafe. It's no longer maintained and doesn't support any of the more modern (and _vital_) features like prepared statements. Switch to `PDO` or `mysqli_*` ASAP — Elias Van Ootegem, Sep 04 '14 at 12:02

score 2 · Accepted Answer · answered Sep 04 '14 at 11:29

You have to iterate through your array to create a new array fulfilled with strings instead of SimpleXMLElement, such as :

<?php

// your array (already built)
$arraySimpleXml = array(
                    "example1" => new SimpleXMLElement("<test>value</test>"),
                    "example2" => new SimpleXMLElement("<test>value2</test>")
                  );

// output array, to store in database
$result = array();

foreach($arraySimpleXml as $key => $simpleXml) {
  $result[$key] = $simpleXml->asXML();
}

// gets your result as a string => you can now insert it into mysql
$dbInsertion = serialize($result);

?>

Great, thanks for your help. Didn't know about `asXML()` so thanks for letting me know. — John Dawson, Sep 04 '14 at 11:36

score 0 · Answer 2 · answered Sep 04 '14 at 11:35

So I worked out how to change the data into a standard array rather than an array of SimpleXMLElements so that I can successfully insert it into a MySQL database.

When iterating the SimpleXMLElement object to extract the data I needed I cast the type as String so that now my array has the format (as opposed to above):

Array
(
[0] => Array
    (
        [uid] => WOS:000238186400009
        [journal] => JOURNAL OF ZOOLOGY
        [publication] => Abundance of hedgehogs (Erinaceus europaeus) in relation to the density and distribution of badgers (Meles meles)
        [year] => 2006
        [author1] => Young, RP
        [address] => Cent Sci Lab, Sand Hutton, Yorks, England
        [author2] => Davison, J
        [author3] => Trewby, ID
        [citations] => 15
    )
)

Thought I'd post this in case anyone has a similar problem in future. To do this, when iterating the data instead of:

$uid = $record->UID;

I did:

$uid = (string)$record->UID;

For each of the data fields I required. This ensures the data is stored as a String and so removes the SimpleXMLElement format.

Export Array of SimpleXMLElement objects to MySQL Database

2 Answers2