How Can I Dynamically Load and Execute .sql Files for Database Creation in PHP?

PHP dynamically loads .sql files to create databases

Introduction

Application development often involves creating multiple databases. While PHP natively supports database creation, loading .sql files to populate tables, define constraints and configure settings can be a challenge.

Problem Statement

How to load a .sql file from a PHP script to dynamically create and configure a database to ensure the correct execution of multi-line queries?

Solution

PDO (PHP Data Object) provides a comprehensive solution for database operations, and the loading of SQL files can be achieved through the following steps:

1. Establish PDO connection: Use PDO to connect to the database, and the DSN string specifies the database name, username and password.
2. Read .sql file: Use file_get_contents() to read the contents of the .sql file as a string into a variable.
3. Execute SQL statement: Use the PDO::exec() method to execute the entire SQL string. Unlike PDO::query(), exec() processes multiple SQL statements at once.

Code Example

<code class="language-php">$dsn = 'mysql:dbname=my_database;host=localhost';
$user = 'root';
$password = 'secret';

$db = new PDO($dsn, $user, $password);

$sql = file_get_contents('schema.sql');

$qr = $db->exec($sql);</code>

With this method, you can dynamically load and execute complex SQL files to efficiently create and configure databases in PHP applications.

The above is the detailed content of How Can I Dynamically Load and Execute .sql Files for Database Creation in PHP?. For more information, please follow other related articles on the PHP Chinese website!