store object array to mysql database

Question

here's my task. I've parsed an xml document and stored all of its data in an array. The array format is

Array(
    [0]=> deals Object(
        [deal_id]=>... 
        [deal_title]=>...
    )
    [1]=> deals Object(
        [deal_id]=>....
        [deal_title]=>...
    )
)

what i need to do is to store values for each object in mysql database but not all of the object's tags should be stored in the same datatable. The reason why i first created the array is that the same php file will parse the xml and then will insert the values in the database OR if an entry with the same deal_id already exists in the database will update the appropriate tables. So I thought that having the xml in an array will help the further checking code. If anyone has a different suggestion I'd be glad to hear..

Thanks in advance!!!

<?xml version="1.0" encoding="UTF-8"?>
<deals>
<deal>
<id>1</id>
<title>title</title>
<city>city<city>
<price>20</price>
<url>http://....</url>
<previous_price>30</previous_price>
<discount> 10</discount>
<image>http://....</image>
<description> description</description>
<purchases> 1</purchases>
<address>address</address>
<latitude>30.5666</latitude>
<longitude>403.6669</longitude>
<start>datetime</start>
<end>datetime</end>
<active>true</active>
<category>category</category>
<type>type</type>
</deal>
</deals>

that's the xml file structure

how about insert ... on duplicate key update. More here dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html — cristian
– cristian, Commented Aug 9, 2011 at 12:38
For your described scenario its not necessary to turn the xml into an array first. In fact, since it's easy to iterate XML contents, it's superfluous to do so in most scenarios. Just update/insert while traversing the DOM tree. — Gordon
– Gordon, Commented Aug 9, 2011 at 12:41
@Gordon so your suggestion is to pass directy from xml to database??I'm not any close to php or mysql expert so my problem with iretation through xml contents was how in the foreach statement i could ask the database if the deal_id field already exists.. — kate
– kate, Commented Aug 9, 2011 at 12:46
@kate actually, it's the same you'd be doing it with your array. Can you provide an example of your XML? — Gordon
– Gordon, Commented Aug 9, 2011 at 13:05

Edoardo Pirovano · Accepted Answer · 2011-08-09 12:45:53Z

3

This will do what you need:

foreach ($array as $deal) {
    $title = mysql_real_escape_string($deal['deal_title']);
    $id = int($deal['deal_id']);
    if ($id) {
        $query = "INSERT INTO deals (id, title) VALUES ($id, '$title') ON DUPLICATE KEY UPDATE deals SET title = '$title' WHERE id = $id";
        mysql_query($query);
    } else {
        echo "ID is not a valid integer.";
    }
}

answered Aug 9, 2011 at 12:45

Edoardo Pirovano

8,3543 gold badges28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Over a year ago

you shouldnt be using ext/mysql anymore. Use PDO or MySQLi

Community · Accepted Answer · 2017-05-23 12:19:51Z

Here is how to insert from the XML directly using PDO and Prepared Statements:

$deals = simplexml_load_string($xml);
foreach($deals->deal as $deal) {
    // assuming you created a $pdo object before
    $pdo->prepare(
        'INSERT 
            INTO deals (id, title, …, type) 
            VALUES (:id, :title, …, :type) 
            ON DUPLICATE KEY 
                UPDATE deals 
                SET title = :title, …, type = :type 
                WHERE id = :id
        ');
    $pdo->execute(
        array(
            ':id'    => (string) $deal->id,    
            ':title' => (string) $deal->title,
            …
            ':type' => (string) $deal->type,
        )
    );
}

Note that this will create one Query for each $deal in the XML. A more practical and performant approach is described in

Bulk Insertion in MYSQL from XML Files

Collectives™ on Stack Overflow

store object array to mysql database

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related