0

Can you execute a MySQL script (foo.sql) from within a PHP script (bar.php)? If so, how?

And, is this a recommended or not recommended practice, why or why not?

Thanks in advance.

Jared Farrish
  • 48,585
  • 17
  • 95
  • 104
RBR
  • 999
  • 3
  • 13
  • 24

3 Answers3

2

how?

bar.php:

<?php `mysql < foo.sql`;

see Execution OperatorsDocs and Using mysql in Batch ModeDocs.

is this a recommended [...] practice, why [...] ?

It's always recommended to choose the right tool for the job. the mysql commandline interface is pretty powerful, fast and well-tested. It does what you're looking for.

Related: Loading .sql files from within PHP and Best practice: Import mySQL file in PHP; split queries.

Community
  • 1
  • 1
hakre
  • 193,403
  • 52
  • 435
  • 836
  • Do ticks equate to running a command line executable? *Note, I had no idea.* – Jared Farrish Aug 07 '11 at 22:58
  • @Jared Farrish: Yes they do. Like in shell scripts, compare: [Execution Operators](http://php.net/manual/en/language.operators.execution.php). – hakre Aug 07 '11 at 23:04
  • Thanks for your response hakre. Using back ticks in PHP (to execute its contents as a shell command) assumes that php.ini allows it, i.e. shell_exec is enabled. In this case, I can't assume that. Also, don't you have to be logged in to the mysql client to invoke it like that from within a php script or even in the command line? – RBR Aug 08 '11 at 00:15
  • @Rylie: That command has support for username and password, I've linked the docs, it's all in there. If `shell_exec` is disabled, backticks won't work. – hakre Aug 08 '11 at 08:20
  • @Rylie: If backticks are not available, you can not do it this way. If you can not say, you need to check if `shell_exec` is available or not and notify the user that this functionality is not available and/or provide a workaround. Take a look at the other answers here and I think there should be code in PHPMyAdmin and similar tools that do what you want. See as well: [Loading .sql files from within PHP](http://stackoverflow.com/questions/147821/loading-sql-files-from-within-php) – hakre Aug 08 '11 at 08:22
2

mysqli::multi_query is another option.

Evert
  • 93,428
  • 18
  • 118
  • 189
  • Yea it will also be Turbo Pascal incompatible. Are you one of the little people who would still care? – Evert Aug 08 '11 at 14:28
0
$contents = get_file_contents( 'foo.sql' );
$queries = explode( ';', $contents );
foreach( $queries as $query ) {
     mysql_query( $query );
}

I don't think anything's wrong with doing that.

nobody
  • 10,599
  • 4
  • 26
  • 43
  • Well, insert statements or comments containing `;` will render this useless. – hakre Aug 07 '11 at 23:03
  • @hakre Yes you're right, also strings containing `;` would pose a problem, maybe we can handle that with a `preg_split()` – nobody Aug 07 '11 at 23:08
  • 1
    Do not even try to improve your script. You will fail unless you write a full blown MYSQL language SQL parser, but there is no need, the CLI client has it already. Alternatively, the [PHPMyAdmin](http://www.phpmyadmin.net/home_page/index.php) code might have such. – hakre Aug 07 '11 at 23:10
  • @harke you might be right, actually I don't have time to write the regex to try this out, but if one doesn't have access to mysql executable (php safe mode for example) this might be the only option. – nobody Aug 07 '11 at 23:18
  • Not the only option, there is always the other option to just get access to the mysql executable. Don't solve with code what you can solve with configuration. – hakre Aug 07 '11 at 23:20
  • @hakre Actually many people on the net are on cheap limited shared hosts, they have "no other option". – nobody Aug 07 '11 at 23:25
  • I have a cheap limited shared host, have a shell and mysql available. Can't reproduce. Maybe they are just using the wrong shared hoster? – hakre Aug 07 '11 at 23:32
  • @hakre Yeah obviously they are, but the programmer didn't get the host, the stupid boss did. – nobody Aug 07 '11 at 23:36
  • let's wait what Rylie actually writes. – hakre Aug 07 '11 at 23:38
  • Don't use the mysql_ functions. They are deprecated. – Evert Aug 08 '11 at 02:05
  • @Evert No they are not, I don't see any deprecation notice in [the manual](http://www.php.net/manual/en/book.mysql.php). – nobody Aug 08 '11 at 08:03
  • Hi Nobody, I'm following internals closely, and they _will_ be deprecated soon. Plus they are outdated and definitely no longer recommended. – Evert Aug 08 '11 at 14:28