unable to insert xml string into mysql table

Question

i want to insert below xml string into mysql database

<message to='". $groupMembersArray[$i] . "@crossmessenger.com' type='groupchat' from='$mFrom'><x  xmlns='jabber:x:event'><offline/><delivered/><displayed/></x><body>$message</body></message>

so for that i have written below code

     $connection = mysqli_connect("localhost","xyz","abc#123","mydb");


for($i = 0 ; $i < $totalMembers ; $i++){

               $stanzaToInsert = "<message to='". $groupMembersArray[$i] ."@crossmessenger.com' type='groupchat' from='$mFrom'><x xmlns='jabber:x:event'><offline/><delivered/><displayed/></x><body>$message</body></message>";

           $msgSize = strlen($stanzaToInsert);
               $insertQuery = "INSERT INTO ofOffline VALUES ('$groupMembersArray[$i]', $messageID, $creationDate, $msgSize, '$stanzaToInsert')";
           mysqli_query($connection, $insertQuery);
           mysqli_commit($connection);
           echo "record $message inserted in table and commited";

}

but nothing is inserting in database. if i replace xml string that means value of stanzaToInsert to some simple text, then it is inserting value into database.

$insertQuery = "INSERT INTO ofOffline VALUES (
                         $groupMembersArray[$i], 
                         $messageID, 
                         $creationDate,
                         $msgSize,
                        'hard coded string'
                       )";

so problem is with xml string.

so can i know "what's wrong with me"?

Are you getting any errors? if no then check your type of message field. — Manish J
– Manish J, Commented Sep 1, 2014 at 5:33

Satish Sharma · Accepted Answer · 2014-09-01 05:09:35Z

1

you should use mysqli_real_escape_string before inserting the value like this

$stanzaToInsert = mysqli_real_escape_string($connection, $stanzaToInsert);

answered Sep 1, 2014 at 5:09

Satish Sharma

9,6356 gold badges31 silver badges52 bronze badges

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

Comments

Manish J · Accepted Answer · 2014-09-01 06:17:18Z

0

Please use "N" befor the xml string for inert the xml message like this:--

(NULL,N'<?xml version="1.0" encoding="utf-16" standalone="yes"?><!--This is a test XML file--><filemeta filetype="Audio"><Comments /><AlbumTitle /><TrackNumber /><ArtistName /><Year /><Genre /><TrackTitle /></filemeta>')

Yourcode should be like this:-

$insertQuery = "INSERT INTO ofOffline VALUES (
                             $groupMembersArray[$i], 
                             $messageID, 
                             $creationDate,
                             $msgSize,
                            N'$xmlstring'
                           )";

answered Sep 1, 2014 at 6:17

Manish J

3092 silver badges7 bronze badges

Collectives™ on Stack Overflow

unable to insert xml string into mysql table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related