Multiple SQL imported by PHP code

Question

In the below code, how would I be able make it look for ALL .sql files in a directory to be read and imported?

<?php
$mysqlDatabaseName ='db123456789';
$mysqlUserName ='dbo123456789';
$mysqlPassword ='yourPassword';
$mysqlHostName ='db1234.oneandone.co.uk';
$mysqlImportFilename ='yourMysqlBackupFile.sql';

//DONT EDIT BELOW THIS LINE
//Export the database and output the status to the page
$command='mysql -h' .$mysqlHostName .' -u' .$mysqlUserName .' -p' .$mysqlPassword .' ' .$mysqlDatabaseName .' < ' .$mysqlImportFilename;
exec($command,$output=array(),$worked);
switch($worked){
    case 0:
        echo 'Import file <b>' .$mysqlImportFilename .'</b> successfully imported to database <b>' .$mysqlDatabaseName .'</b>';
        break;
    case 1:
        echo 'There was an error during import. Please make sure the import file is saved in the same folder as this script and check your values:<br/><br/><table><tr><td>MySQL Database Name:</td><td><b>' .$mysqlDatabaseName .'</b></td></tr><tr><td>MySQL User Name:</td><td><b>' .$mysqlUserName .'</b></td></tr><tr><td>MySQL Password:</td><td><b>NOTSHOWN</b></td></tr><tr><td>MySQL Host Name:</td><td><b>' .$mysqlHostName .'</b></td></tr><tr><td>MySQL Import Filename:</td><td><b>' .$mysqlImportFilename .'</b></td></tr></table>';
        break;
}
?>

By usings phps file (folder) handling functions you find listed in the php documentation. — arkascha
– arkascha, Commented Nov 17, 2013 at 10:48
Example how I would use this in my code? I have <?php foreach (glob("mysqls/*.sql") as $filename) { include $filename; } ?> — user3001214
– user3001214, Commented Nov 17, 2013 at 10:57
Looks fine, except that of course there is little sense in including an sql file into a php file. Instead put your execution routines inside the foreach loop and feed it with one file at a time. So instead of using the statically defined $mysqlImportFilename as file to be executed, use the dynamical ones in each iteration of the foreach loop. — arkascha
– arkascha, Commented Nov 17, 2013 at 10:58

arkascha · Accepted Answer · 2013-11-17 11:14:01Z

3

Ok, I coded the suggestion from the comments I gave. Note: I did not test this code, so there might be some small hickups like typos inside which you have to correct. but it should work in general:

<?php
$mysqlDatabaseName = 'db123456789';
$mysqlUserName     = 'dbo123456789';
$mysqlPassword     = 'yourPassword';
$mysqlHostName     = 'db1234.oneandone.co.uk';
$sqlFileMask       = 'mysqls/*.sql';

// prepare the mysql command to be executed per file below
$command = sprintf('mysql -h %s -u %s -p %s %s',
                   $mysqlHostName, 
                   $mysqlUserName, 
                   $mysqlPassword, 
                   $mysqlDatabaseName);

// export the database and output the status to the page
foreach (glob($sqlFileMask) as $filename) {
    exec($command.' < '.$filename, $output=array(), $worked);
    switch($worked) {
        case 0:
            echo "Imported file '$filename' successfully.";
            break;
        default:
            // you should actually consult the exact return value here...
            echo "There was an error during import of file filename'.";
            break;
    } // switch
} // foreach

?>

Other hints for you:

implement more and better error handling
turn the initial variable initializations into constants
move the configuration into a separate file
extra points: include that file by the http server configuration, no by an include command

edited Nov 17, 2013 at 11:14

answered Nov 17, 2013 at 11:07

arkascha

43.2k8 gold badges61 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

user3001214 Over a year ago

Hi I've got a config.php file all set up, how would I make the code work with it and in PDO?

arkascha Over a year ago

You cannot use PDO in this. PDO is of use when you execute sql statements from within php using a mysql driver/gateway. You don't do that, you use the command line mysql client instead. But I don't see any reasons to use PDO here anyway, since you do not have direct user input that becomes part of the sql statements as far as I can see.

arkascha Over a year ago

About the config file: you either include it using phps include or require commands, or, much more elegant, you have php include that config file by using the php_admin_value command inside the http servers vhost section like this: php_admin_value auto_prepend_file /path/to/config.php`.

user3001214 Over a year ago

My whole website is running of PDO queries as well as the PDO database connection. PDO is similar to Mysql, its an extention. Although in the future Mysql is being removed of php 5.0

arkascha Over a year ago

MySQL is not removed from php :-) What you mean is: the 'mysql extension' is removed from php, it has been deprecated for years now. There is the more modern 'mysqli extension' that is used these days, it serves similar goals as PDO does. However maybe you did not realize, but with the code you posted you do not use phps mysql extension at all! You use the command line client mysql offers. That has nothing to do with php. mysqli or PDO makes sense to use "prepared statements" where interactive input is used directly inside sql statements. You do not do that, so there is no reason to use PDO.

|

Collectives™ on Stack Overflow

Multiple SQL imported by PHP code

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related