CODEWITHSUNDEEP
phpmysqlfile-io
Harish Kurup
Harish Kurup

Reputation: 7525

How to run a set of SQL queries from a file, in PHP?

I have some set of SQL queries which is in a file(i.e query.sql), and i want to run those queries in files using PHP, the code that i have wrote is not working,

//database config's...
$file_name="query.sql";
$query=file($file_name);
$array_length=count($query);
for($i=0;$i<$array_length;$i++)
{
    $data .= $query[$i];
}

echo $data;    
mysql_query($data);

it echos the SQL Query from the file but throws an error at mysql_query() function...

Upvotes: 1

Views: 3580

Answers (4)

Sam
Sam

Reputation: 122

These kind of operations are performed usually as shell scripts. For further readings, following readings shall be helpful to know the pros and cons. However, normally batch kind of operations should be done under .sql scripts otherwise running .sql are highly discouraged.

References:

Running MySQL *.sql files in PHP

http://php.net/manual/en/function.shell-exec.php

Upvotes: 0

Nono
Nono

Reputation: 7302

Try this:    

// SQL File
    $SQLFile = 'YourSQLFile.sql';

    // Server Name
    $hostname = 'localhost';

    // User Name
    $db_user = 'root';

    // User Password
    $db_password = '';

    // DBName
    $database_name = 'YourDBName';

    // Connect MySQL
    $link = mysql_connect($hostname, $db_user, $db_password);

    if (!$link) {
    die("MySQL Connection error");
    }

    // Select MySQL DB
    mysql_select_db($database_name, $link) or die("Wrong MySQL Database");

    // Function For Run Multiple Query From .SQL File
    function MultiQuery($sqlfile, $sqldelimiter = ';') {
    set_time_limit(0);

    if (is_file($sqlfile) === true) {
    $sqlfile = fopen($sqlfile, 'r');

    if (is_resource($sqlfile) === true) {
    $query = array();
    echo "<table cellspacing='3' cellpadding='3' border='0'>";

    while (feof($sqlfile) === false) {
    $query[] = fgets($sqlfile);

    if (preg_match('~' . preg_quote($sqldelimiter, '~') . '\s*$~iS', end($query)) === 1) {
    $query = trim(implode('', $query));

    if (mysql_query($query) === false) {
    echo '<tr><td>ERROR:</td><td> ' . $query . '</td></tr>';
    } else {
    echo '<tr><td>SUCCESS:</td><td>' . $query . '</td></tr>';
    }

    while (ob_get_level() &gt; 0) {
    ob_end_flush();
    }

    flush();
    }

    if (is_string($query) === true) {
    $query = array();
    }
    }
    echo "</table>";

    return fclose($sqlfile);
    }
    }

    return false;
    }

    /* * * Use Function Like This: ** */

    MultiQuery($SQLFile);

Upvotes: 1

Matti Virkkunen
Matti Virkkunen

Reputation: 65166

  1. Don't use file() unless you really want the data line-by-line. file_get_contents() is better.
  2. $query==file($file_name); I don't think you want to do a comparison here.
  3. mysql_query will only ever execute a single query at once. You'll have to come up with some way of separating your queries in the file and run them one-by-one.

Upvotes: 2

Your Common Sense
Your Common Sense

Reputation: 158009

If you ever bother yourself to read mysql_query() manual page, it says this function can execute only one query at once.
So, you have to put it inside of the loop

Upvotes: 1

Related Questions