How to import sql server data into mysql database in php

MySQL is a popular relational database management system, and SQL Server is also a similar database system. Since many people choose MySQL as their database of choice, importing data from SQL Server to MySQL is a common task. In this article, we will introduce how to implement this process using PHP.

1. Preparation work

Before starting the import process, we need to prepare some work. First, you need to export your SQL Server data to a CSV file. This is a common format that Excel and other programs can read and process. The data can be exported to a CSV file using SQL Server Management Studio or other similar tools.

Secondly, we need to make sure we have installed all the software required for PHP. In order to import SQL Server data into MySQL, we will use the PHP Data Objects (PDO) extension. Please make sure you have PHP 5.1 or higher installed and the PDO extension enabled. PHP's official website provides more information and download links about these software.

2. Write a PHP script

Next, we will write a PHP script to read data from the CSV file and insert it into the MySQL database. The following is the basic script:

//Data source file path
$file_path = "data.csv";

//MySQL database connection Information (modify to your own)
$mysql_servername = "localhost";
$mysql_username = "root";
$mysql_password = "password";
$mysql_dbname = "mydatabase";

//Connect to MySQL database
try {

$conn = new PDO("mysql:host=$mysql_servername;dbname=$mysql_dbname", $mysql_username, $mysql_password);

//设置PDO错误模式为异常
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch(PDOException $e) {

echo "Connection failed: " . $e->getMessage();

}

//Read the CSV file data and insert it into the MySQL database
if (($handle = fopen($file_path, "r")) !== FALSE) {

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $stmt = $conn->prepare("INSERT INTO mytable (column1, column2, column3) VALUES (?, ?, ?)");
    $stmt->execute($data);
}
fclose($handle);

}

echo "Data import successful!";

?>

In this script, we define three variables to store the path of the data source file and the connection information of the MySQL database. We then use PDO to connect to the MySQL database. Next, we read the data from the CSV file and insert it into the specified MySQL table. Each time data is inserted, we use the PDO prepare statement and execute method to insert the data into the corresponding column in the MySQL table.

This is a very simple script, so you can modify it as needed. For example, you can change the column names of your insert statement based on the contents of each CSV file you want to import.

3. Run the script

Now, you have completed all the necessary preparations and written a simple PHP script to import data from SQL Server into a MySQL database. Next, you can execute the PHP script by running it from the command line or in your browser.

To run the script in the command line, you can use the following syntax:

php import_data.php

Where, import_data.php is the name of the PHP script file you named.

Run the script in the browser, you can upload the script to the server and access the URL of the script file in the browser. For example:

http://example.com/import_data.php

No matter which method you choose, you should see a message indicating that the data was successfully imported into the MySQL database.

Conclusion

In this article, we introduced how to import SQL Server data into a MySQL database using PHP. This is a very practical task that allows you to move data from one database system to another. Importantly, using the PHP Data Objects extension makes this process fast and easy. Hope this article is helpful to you.

The above is the detailed content of How to import sql server data into mysql database in php. For more information, please follow other related articles on the PHP Chinese website!