Source a MySQL file through PHP?

Question

how do I source a .sql file, like the one below, through php?

CREATE DATABASE IF NOT EXISTS data_base;
USE data_base;
CREATE TABLE IF NOT EXISTS the_table( 
    package_name varchar(50) NOT NULL,
    PRIMARY KEY (`package_name`)
)

I tried the following, but it doesn't work. I've tried to do some research and it seems I need to do something called LOAD DATA INFILE, but I don't understand it.

mysql_query("source C:/xampp/htdocs/Project1/mysql/source.sql;") or die(mysql_error());

How can I source an .sql file using PHP? Thanks.

Answer 1

You cannot just run an SQL script, even with mysqli::multi_query(). An SQL script can contain some commands that are recognized as builtin commands only by the mysql client, not by the MySQL server's SQL parser.

SOURCE is definitely a command that is preprocessed by the mysql client. The server does not understand that command. So you can't execute SOURCE on the server using the query API.

If you can restrict the content of your SQL script to exclude mysql client builtin commands, it might work to use mysqli::multi_query(). But it won't work for the full set of commands that are allowed in an SQL script.

See also my answer to Running MySQL *.sql files in PHP

LOAD DATA INFILE does not execute SQL statements to create tables, it just loads fields of a text file into an existing table.

Answer 2

You're mixing mysql command line statements with PHP functional calls. Do one or the other.

Utilize the mysql CLI (if available):

// executing mysql cli
exec("mysql < test.sql");

Or run the SQL with mysqli_multi_query():

// running the files contents in bulk
$sql= file_get_contents('test.sql');
mysqli_multi_query($sql);

Note: This is a potential security risk. Be advised.