PHP: reading a text file and executing SQL statements to MySQL

Question

I want to create a form in PHP that will load a .sql file, read its contents, and execute those statements against a MySQL database.

Currently using function get_file_contents but it's not working. What can I do?

Answer 1

If you have several queries in your file, you'll either have to :

• split the file into multiple distinct queries, and use either mysql_query or mysqli_query for each one of those queries.
  • those two functions cannot execute more than one query per call : quoting the manual page of mysql_query : "multiple queries are not supported"
• use mysqli_multi_query to send several queries at once
  • There is no equivalent using the mysql_* family of functions
• use the mysql command-line client, to import the file, with something like this (you'll have to pass the right values, of course) :
  • mysql --user=USERNAME --password=PASSWORD --host=localhost DATABASENAME < your-file.sql

Answer 2

I use this bit of Java code to import sql queries, it basically splits the file into lines using a specific regexp, and strip comment prefixes created by mysqldump. porting this to PHP should be pretty straight forward.

public static void importSQL(Connection conn, InputStream in) throws SQLException
{
    Scanner s = new Scanner(in);
    s.useDelimiter("(;\\s*(\r)?\n)|(--\n)");
    Statement st = null;
    int lineNum = 0;
    try
    {
        st = conn.createStatement();
        while (s.hasNext())
        {
            String line = s.next();
            if (line.startsWith("/*!") && line.endsWith("*/"))
            {
                int i = line.indexOf(' ');
                line = line.substring(i + 1, line.length() - " */".length());
            }

            if (line.trim().length() > 0)
            {
                try
                {
                    st.execute(line);
                }
                catch (SQLException e)
                {
                    logger.error("Error executing line #" + lineNum +  " : " + line , e);
                    throw e;
                }
            }

            lineNum++;
        }
    }
    finally
    {
        if (st != null) st.close();
    }
}

an alternative is to call mysql command line via system. something like :

<?php
system("mysql -u $user -p$password $dbname < $filename");
?>

but is not a very good idea for production code for various reasons (performance, security implications you should be aware of etc).