Automate SQL File Execution and Database Updates with PHP

Introduction

Managing databases often involves executing SQL scripts to update schemas, import data, or apply changes. When you have a directory full of SQL files, automating the execution of these files can save time and reduce the risk of errors. In this article, we’ll explore a PHP script that simplifies the process of executing SQL files that match a specific pattern within a directory.

Read: Efficiently Split Large SQL Files into Manageable Chunks with PHP

The Challenge of SQL File Execution

Database administrators and developers frequently encounter scenarios where they need to execute multiple SQL files. These files may contain various tasks, such as creating tables, inserting data, or altering the database structure. Manually executing each SQL file can be time-consuming and error-prone, especially when dealing with a large number of files.

The PHP script we’ll discuss streamlines this process by automating the execution of SQL files that meet specific criteria, making it a valuable tool for efficient database management.

Introducing the runSQLFilesWithPattern Function

The runSQLFilesWithPattern function is a PHP utility designed to automate the execution of SQL files based on a defined pattern. It scans a directory for SQL files that match the pattern, reads their content, and executes each SQL query within those files sequentially. Let’s delve into the function’s details:



function runSQLFilesWithPattern($directory, $dbHost, $dbUser, $dbPassword, $dbName, $filePattern = "part_*.sql")
{
    // Read the list of files with a specific pattern in the directory
    $files = glob($directory . '/' . $filePattern);
    sort($files); // Sort files in alphabetical order

    // Establish a database connection
    $mysqli = new mysqli($dbHost, $dbUser, $dbPassword, $dbName);

    if ($mysqli->connect_error) {
        die('Could not connect to database: ' . $mysqli->connect_error);
    }

    // Loop through each SQL file and execute its queries
    foreach ($files as $file) {
        $sqlQueries = file_get_contents($file);
        $queries = explode(';', $sqlQueries);

        foreach ($queries as $query) {
            $query = trim($query);
            if (!empty($query)) {
                $result = $mysqli->query($query);
                if (!$result) {
                    echo "Error executing query: " . $mysqli->error . "\n";
                }
            }
        }
    }

    $mysqli->close();
    echo "All queries from SQL files matching the pattern '$filePattern' have been executed.\n";
}

function runSQLFilesWithPattern($directory, $dbHost, $dbUser, $dbPassword, $dbName, $filePattern = "part_*.sql")

{

// Read the list of files with a specific pattern in the directory

$files = glob($directory . '/' . $filePattern);

sort($files); // Sort files in alphabetical order

// Establish a database connection

$mysqli = new mysqli($dbHost, $dbUser, $dbPassword, $dbName);

if ($mysqli->connect_error) {

die('Could not connect to database: ' . $mysqli->connect_error);

}

// Loop through each SQL file and execute its queries

foreach ($files as $file) {

$sqlQueries = file_get_contents($file);

$queries = explode(';', $sqlQueries);

foreach ($queries as $query) {

$query = trim($query);

if (!empty($query)) {

$result = $mysqli->query($query);

if (!$result) {

echo "Error executing query: " . $mysqli->error . "\n";

}

$mysqli->close();

echo "All queries from SQL files matching the pattern '$filePattern' have been executed.\n";

}

How the runSQLFilesWithPattern Function Works

Let’s break down the key components of the runSQLFilesWithPattern function:

Scanning Files: The function starts by scanning the specified directory for SQL files that match the provided pattern ($filePattern). It stores the list of matching files in the $files array and sorts them alphabetically.
Database Connection: Next, the function establishes a connection to the database using the provided host ($dbHost), username ($dbUser), password ($dbPassword), and database name ($dbName). If the connection fails, an error message is displayed, and the script terminates.
Executing Queries: The function iterates through each SQL file in the $files array. For each file, it reads the content and splits it into individual SQL queries based on semicolons ;. These queries are stored in the $queries array.
Query Execution: The function then executes each query within the $queries array. It trims each query to remove leading and trailing whitespace and checks if it’s not empty before execution. If a query fails to execute, an error message is displayed.
Closing Database Connection: After executing all queries, the function closes the database connection to release resources.
Completion Message: Finally, the function displays a completion message indicating that all queries from SQL files matching the specified pattern have been executed.

Using the runSQLFilesWithPattern Function

To utilize the runSQLFilesWithPattern function, follow these steps:

Include the Function: Ensure that the function is included in your PHP script or is available in the same codebase.
Define Parameters: Set the following parameters:
- $directory: The path to the directory containing SQL files.
- $dbHost: The database host.
- $dbUser: The database username.
- $dbPassword: The database password.
- $dbName: The database name.
- $filePattern (optional): The pattern used to match SQL files. By default, it’s set to “part_*.sql.”
Call the Function: Execute the function by calling it with the specified parameters, as demonstrated in the example below:



$directory = 'output_directory'; // Directory where SQL files are stored
$dbHost = 'localhost';
$dbUser = 'root';
$dbPassword = 'rootpassword';
$dbName = 'test_db';

runSQLFilesWithPattern($directory, $dbHost, $dbUser, $dbPassword, $dbName);

$directory = 'output_directory'; // Directory where SQL files are stored

$dbHost = 'localhost';

$dbUser = 'root';

$dbPassword = 'rootpassword';

$dbName = 'test_db';

runSQLFilesWithPattern($directory, $dbHost, $dbUser, $dbPassword, $dbName);

The function will automatically execute the SQL files that match the specified pattern within the specified directory.

Advantages of SQL File Automation

Automating the execution of SQL files provides several advantages for efficient database management:

Time Savings: The script streamlines the execution process, saving time compared to manually running each SQL file.
Error Reduction: Automation reduces the risk of human errors, ensuring that all queries are executed correctly.
Consistency: All SQL files are processed in a consistent manner, preventing variations in execution.
Scalability: The script is scalable and can handle a large number of SQL files, making it suitable for complex database management tasks.

Conclusion

Efficient database management often involves executing multiple SQL files to make schema changes, import data, or perform other tasks. The runSQLFilesWithPattern PHP function presented in this article simplifies this process by automating the execution of SQL files that match a specified pattern within a directory.

By using this script, you can ensure that your database operations are performed consistently, reducing the risk of errors and saving valuable time. Whether you’re updating your database schema, importing data, or performing routine maintenance, automating SQL file execution can streamline your workflow and enhance your database management practices.

In conclusion, the runSQLFilesWithPattern function empowers you to automate SQL file execution, making your database management tasks more efficient and reliable.

Read Also

Recommendation for You

Leave a Reply Cancel reply