CODEWITHSUNDEEP
phpsqlxamppmysqli-multi-query
HartleySan
HartleySan

Reputation: 7830

How do I use PHP to execute a SQL file in batch that contains thousands of statements?

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.

Upvotes: 0

Views: 484

Answers (1)

HartleySan
HartleySan

Reputation: 7830

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');

Upvotes: 1

Related Questions