0

I have a .sql file that contains a handful of TRUNCATE, DROP TABLE IF EXISTS and CREATE TABLE statements as well as thousands of INSERT and UPDATE statements.

I'm trying to use PHP to execute all the statements in the file in batch as follows:

$sql = file_get_contents('path/to/sql/file.sql');
$link->($sql);

Where $link is a valid MySQLi DB link. I've also tried swapping out $link->($sql) with $link->multi_query($sql), but neither seem to work.

If it makes any difference, I'm attempting to do this in an XAMPP localhost environment on Windows 10 with PHP 7.1 and MariaDB 15.1.

Does anyone know why this would cause problems and not work?
What's the best way in PHP to execute a SQL file with thousands of statements in it?
Thanks.

HartleySan
  • 7,404
  • 14
  • 66
  • 119
  • Please add the errors being displayed – Rotimi Jun 10 '18 at 14:19
  • 1
    I use this. It is just something I put together for me. It works. You can at least see how the commands work on windows. :) Yes, it is run as part of my unit tests. I use it to set up the database. https://pastebin.com/wWjfsYCQ – Ryan Vincent Jun 10 '18 at 15:35
  • I see, @RyanVincent. Basically, you have to use the `exec` function in PHP to run the `mysql` command with certain options. That makes sense. Thanks a lot. Is there a way to do this if MySQL and Apache/PHP are installed on separate servers? Thanks. – HartleySan Jun 10 '18 at 15:38

1 Answers1

1

Ryan, thanks again for the advice. As you suggested, running the mysql command directly on the SQL file is the way to go.

The following worked for me:

shell_exec('C:\\xampp\\mysql\\bin\\mysql.exe -h localhost -u root -ppassword -D db-name "SELECT * FROM table-name;"');

Please note that you don't want a space between the -p option and the password itself. If you don't have a password, then just remove the -p option altogether.

Edit: The sample command above is a very arbitrary one to demonstrate running the mysql command. If you want to read and execute a bunch of SQL statements in a file, the following is more what you need:

shell_exec('C:\\xampp\\mysql\\bin\\mysql.exe -h localhost -u root -ppassword -D db-name < C:\\xampp\\htdocs\\path\\to\\file.sql');
HartleySan
  • 7,404
  • 14
  • 66
  • 119