how to insert and select the records from xml document to mysql using php?

[paulrajj]

hi everybody,

i am newbie to php and mysql. i have a little bit knowledge about php with xml. how to insert and select the records from xml to mysql using php?

[devsusen]

@paulrajj
Not very clear to me, but looks like inserting data from XML to MySql using php.
For this you need the read the XML data and generate sql Insert qureies and run mysql_query() to insert the data into MySql.

[paulrajj]

hi devsusen,

thanks for ur reply.
can u give me an example how to do with xml document and php?

[vl4kn0]

there is my example of inserting data from xml to sql databaze

<?php
 
/*
 * Definition of xml
 */
$example = <<<XML
<?xml version="1.0" encoding="UTF-8" ?>
<Users>
    <User>
        <Name>name1</Name>
        <Password>password1</Password>
    </User>
    <User>
        <Name>name2</Name>
        <Password>password2</Password>
    </User>
    <User>
        <Name>name3</Name>
        <Password>password3</Password>
    </User>
</Users>
XML;
 
/*
 * Load the xml into simplexml object
 */
$xml = simplexml_load_string($example);
 
/*
 * This is beginning for mysql query string
 */
$sql = "INSERT INTO users (name, password) VALUES ";
 
$stack = array();
 
/*
 * Insert formated values from xml into second part of sql query
 */
foreach ($xml as $user) {
    $stack[] = "('{$user->Name}', '{$user->Password}')";
}
 
/*
 * Implde array of values from xml to sql query
 */
$sql .= implode(', ', $stack);
 
/*
 * Connection to databaze server
 */
if (!mysql_connect('host', 'username', 'password'))
    die(mysql_error());
 
/*
 * Selecting a databaze on mysql server
 */
if (!mysql_select_db('database name'))
    die(mysql_error());
 
/*
 * Inserting data into mysql databaze
 */
if (!mysql_query($sql))
    die(mysql_error());
 
echo "Databaze successful updated";
 
 
?>

REGARDS,
vl4kn0

[paulrajj]

thanks for ur code.
its really helpful to me.

[Dormilich]

NOTE:
depending on the complexity of your code you can also use:
- the DOMDocument class for XML (if you have namespaces or want to use XPath)
- a Database Abstraction Layer (PDO, MDB2, MySQLi), where you can use arrays for input (e.g. in prepared statements)

if you're in an experimental mood, you can also try to form the SQL directly from the XML using XSLT.

[bonguyen1]

thank you post code vl4kn0 was reasonable for her to get some change in external XML files independent
file
index.php

<? php
$ link = mysql_connect ( "localhost", "root ","");
mysql_select_db ( "xmlinsert", $ link);
/ *
   * Connection to server databaze
   * /
  if (! mysql_connect ( 'localhost', 'root',''))
      die (mysql_error ());
 
  / *
   * Selecting a mysql server on databaze
   * /
  if (! mysql_select_db ( 'xmlinsert'))
      die (mysql_error ());
 
  / *
   * Inserting data into mysql databaze
  * /
  else
  (
 
$ xml = new SimpleXMLElement ( "phuong_an1.xml", null, true);
 
  $ sql = "INSERT INTO xmlinsert (name, password) VALUES";
 
  $ stack = array ();
 
  foreach ($ xml as $ user) (
      $ stack [] = "('{$ user-> Name) ',' ($ user-> Password }')";
  )
 
  / *
  * Implde array of values from xml to sql query
   * /
  $ sql .= implode ( ',', $ stack);
 
  if (! mysql_query ($ sql))
      die (mysql_error ());
 
   $ sql = "SELECT *
`FROM` xmlinsert
LIMIT 0, 30 ";
$ returl = mysql_query ($ sql) or die ( "Data not found.");
while ($ row = mysql_fetch_array ($ returl)) (
 
  echo "($ row [name]) <br>";
  echo "($ row [password]) <br>";
  )
   )
 
  ?>

phuong_an1.xml file

<?xml version = "1.0" encoding = "utf-8"?
     <Users>
        <user>
            <name> Nguyen </ Name>
           <Password> Nguyen passs </ Password>
       </ User>
<user>
            <name> Nguyen </ Name>
           <Password> Nguyen passs </ Password>
       </ User>
<user>
            <name> Nguyen </ Name>
           <Password> Nguyen passs </ Password>
       </ User>
<user>
            <name> Nguyen </ Name>
           <Password> Nguyen passs </ Password>
       </ User>
  </ Users>

[Markus]

@bonguyen1
Is that valid XML? As I remember, elements are case-sensitive.

[Dormilich]

@Markus
they are, stated at the top of the specs

and

</ name>

is also invalid

[vl4kn0]

@bonguyen1
where did you get this code? all blocks (while, for, if, else if, else, foreach, function definition, class definition) have to be in curly brackets -> {}
for example:

foreach ($xml as $user) {
    $stack[] = "('{$user->Name}', '{$user->Password}')";
}