Import XML data into database using PHP

Use PHP to import XML data into the database

Introduction:
During development, we often need to import external data into the database for further processing and analysis. As a commonly used data exchange format, XML is often used to store and transmit structured data. This article will introduce how to use PHP to import XML data into a database.

Step 1: Parse the XML file
First, we need to parse the XML file and extract the required data. PHP provides several ways to parse XML, the most commonly used of which is using the SimpleXML extension. The following is a simple XML file example:

<data>
  <item>
    <name>Item 1</name>
    <price>19.99</price>
  </item>
  <item>
    <name>Item 2</name>
    <price>29.99</price>
  </item>
  <item>
    <name>Item 3</name>
    <price>39.99</price>
  </item>
</data>

We can parse and output the data in the XML file through the following code:

$xml = simplexml_load_file('data.xml');
foreach ($xml->item as $item) {
    echo 'Name: ' . $item->name . ', Price: ' . $item->price . '<br>';
}

Run the above code, the following results will be output:

Name: Item 1, Price: 19.99
Name: Item 2, Price: 29.99
Name: Item 3, Price: 39.99

Step 2: Connect to the database
Next, we need to connect to the database and create a table to store the data we parse. It is assumed here that we use the MySQL database as an example.

$host = 'localhost';
$db = 'database';
$user = 'username';
$pass = 'password';

// 连接到数据库
$conn = new mysqli($host, $user, $pass, $db);
if ($conn->connect_error) {
    die('连接数据库失败：' . $conn->connect_error);
}

// 创建表（如果不存在）
$sql = "CREATE TABLE IF NOT EXISTS items (
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
)";
if ($conn->query($sql) !== true) {
    die('创建表失败：' . $conn->error);
}

Step 3: Insert data
Now we can insert the parsed data into the table in the database.

foreach ($xml->item as $item) {
    $name = $conn->real_escape_string($item->name);
    $price = (float) $item->price;
    
    // 插入记录
    $sql = "INSERT INTO items (name, price) VALUES ('$name', '$price')";
    if ($conn->query($sql) !== true) {
        echo '插入记录失败：' . $conn->error;
    }
}

// 关闭数据库连接
$conn->close();

Finally, we need to read the data in the XML file line by line and insert it into the database table. During the insertion process, we used real_escape_string to escape special characters to prevent injection attacks. At the same time, we also processed the price field and converted it to floating point format.

Summary:
This article introduces how to use PHP to import XML data into the database. First, we use the SimpleXML extension to parse the XML file and extract the required data. Then, we connect to the database, create the table, and finally insert the data into the table. This process can be easily used to import external data into the database, providing convenience for subsequent data processing and analysis.

Reference:

• [PHP: SimpleXML](https://www.php.net/manual/en/book.simplexml.php)
• [PHP: mysqli](https://www.php.net/manual/en/book.mysqli.php)

More code examples:

• [ GitHub repository](https://github.com/example-repo)

The above is the detailed content of Import XML data into database using PHP. For more information, please follow other related articles on the PHP Chinese website!