How Can I Execute SQL Scripts from PHP Using the MySQL Command-Line Tool?

Embedding SQL Scripts in PHP: A Detailed Guide

Introduction

Executing SQL scripts directly from PHP presents a challenge due to limitations and edge cases encountered with direct execution. Therefore, employing an external tool, such as MySQL's CLI tool, becomes necessary.

Executing *.sql Files with shell_exec()

To run *.sql files in PHP, the following approach using shell_exec() is recommended:

$command = 'mysql'
    . ' --host=' . $vals['db_host']
    . ' --user=' . $vals['db_user']
    . ' --password=' . $vals['db_pass']
    . ' --database=' . $vals['db_name']
    . ' --execute="SOURCE ' . $script_path
;
$output1 = shell_exec($command . '/site_db.sql"');
$output2 = shell_exec($command . '/site_structure.sql"');

Additional Considerations

It's worth noting that the --option=value format is more robust for specifying MySQL CLI options. Furthermore, --execute="SOURCE ..." can be used to specify file execution.

Differences between shell_exec() and exec()

While both shell_exec() and exec() allow execution of external commands, shell_exec() ensures that the entire script is executed, capturing its output. Exec(), on the other hand, only returns the last line of output.

Related Questions and Resources

• For alternative methods, refer to Ignacio Vazquez-Abrams's response.

• Consult these related Stack Overflow questions for additional insights:

  • Loading .sql files from within PHP
  • Executing SQL scripts with stored procedures
  • Multiple SQL queries in a single mysql_query statement

The above is the detailed content of How Can I Execute SQL Scripts from PHP Using the MySQL Command-Line Tool?. For more information, please follow other related articles on the PHP Chinese website!