How to run SQL Script using specific parameters PHP

Question

I need to run a SQL file (to create a user) from within my PHP. I know how this is done:

$commands = file_get_contents("sql/create_usr.sql")
$commands = explode(";", $commands);
foreach($commands as $command){
    if($command){
        $success += (@mysql_query($command)==false ? 0 : 1);
        $total += 1;
    }
}

How do I run the SQL file with specific parameters (e.g. username, password) from a PHP file. Thanks in advance.

what you need if(trim($command))? The entire ext/mysql PHP extension, which provides all functions named with the prefix mysql_, is officially deprecated as of PHP v5.5.0 and will be removed in the future. — Simone Nigro
– Simone Nigro, Commented Dec 20, 2014 at 14:32

2ndkauboy · Accepted Answer · 2014-12-20 15:11:59Z

2

You might want to use a simple shell execution for this, instead of splitting the file with PHP:

shell_exec( 'mysql DATABASE -u USERNAME -pPASSWORD < sql/create_usr.sql' );

To test, if you can run a SQL-Query using shell_exec you might want to try this code:

echo shell_exec( 'mysql DATABASE -u USERAME -pPASSWORD -e "SELECT DATABASE();" 2>&1' );

edited Dec 20, 2014 at 15:11

answered Dec 20, 2014 at 14:45

2ndkauboy

9,4033 gold badges34 silver badges67 bronze badges

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

15 Comments

Bob Eret Over a year ago

while shell_exec does work, it does not appear to run sql files. For testing I am using the following simple code:- create table example( me varchar(22) );

2ndkauboy Over a year ago

For me it's working as expected. But in your case, you might have to use the full path to the mysql binary. So on a Unix system, somethin like /usr/bin/mysql should work.

2ndkauboy Over a year ago

See my lastest update for some code to check the SQL execution.

Bob Eret Over a year ago

Amazingly, it didn't. With an example user called myuser and password of mypasswd, the full line shell_exec( 'C:\xampp2\mysql\bin\mysql.exe clothe -u myuser -pmypasswd < C:\xamp2\htdocs\sql\create_usr.sql' ); failed to work!!!!!!!!!!!!!!!

Bob Eret Over a year ago

No error - nothing happened. No table created even with code above.

|

esdebon · Accepted Answer · 2014-12-20 14:52:05Z

1

Read your file line by line and the execute the sentense

$handle = fopen("inputfile.txt", "r");
if ($handle) {
    while (($line = fgets($handle)) !== false) {
        // process the line read.
    }
} else {
    // error opening the file.
} 
fclose($handle);

answered Dec 20, 2014 at 14:52

esdebon

2,7995 gold badges27 silver badges34 bronze badges

2 Comments

2ndkauboy Over a year ago

There might not be an SQL query on each line and some queries might be split up to several lines.

Bob Eret Over a year ago

I'm afraid that is the case

Collectives™ on Stack Overflow

How to run SQL Script using specific parameters PHP

2 Answers 2

15 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

15 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related