PDO run script from .sql file

Question

How correctly run sql file for insert in MySQL? Try use PDO:

$mysql_host = "localhost";
$mysql_database = "planets_test";
$mysql_user = "root";
$mysql_password = "";
# MySQL with PDO_MYSQL
$dbh= new PDO("mysql:host=$mysql_host;dbname=$mysql_database", $mysql_user, $mysql_password);
$query = file_get_contents("planets_db.sql");
$stmt = $dbh->prepare($query);
$stmt->execute();

Where planets_db.sql" is script generated PHPmyAdmin?

Script not return error, but table not created.

Haley Mueller · Accepted Answer · 2019-03-20T13:03:07.880

3

Since this is a script you can execute SQL with the following code:

try {
     $mysql_host = "localhost";
     $mysql_database = "planets_test";
     $mysql_user = "root";
     $mysql_password = "";
     $dbh= new PDO("mysql:host=$mysql_host;dbname=$mysql_database", $mysql_user, $mysql_password);
     $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
     $query = file_get_contents("planets_db.sql");
     $dbh->exec($query);
     print("Created $table Table.\n");

} catch(PDOException $e) {
    echo $e->getMessage();//Remove or change message in production code
}

If there is an error it should tell you what went wrong.

edited Mar 20 '19 at 13:03

answered Mar 19 '19 at 19:30

Haley Mueller

487
4
16

"SQLSTATE[HY000]: General error: trying to execute an empty query." . Filename is correct. Script correct too. – test_name Mar 19 '19 at 19:36
Try to print out the file contents to make sure there is something there: echo file_get_contents("planets_db.sql") . PHP_EOL; – Haley Mueller Mar 19 '19 at 20:37
your `$sql` should be `$query`. – Jakumi Mar 19 '19 at 22:21
@Jakumi nice catch – Haley Mueller Mar 20 '19 at 13:03
1

yeah, the only problem I see is, that the sql file probably has more than exactly one sql statement (i suppose the "insert" in the problem description implies that) and thus the `$dbh->exec()` will probably not work as expected, since it's for single statements only, iirc – Jakumi Mar 20 '19 at 15:17

score -1 · Answer 2 · answered Mar 19 '19 at 19:38

-1

I checked your code in my PC. Nothing found any error. Please check your SQL file in the same directory or is it correct SQL file.

answered Mar 19 '19 at 19:38

kavindalb

11
3

This file was generated from phpMyAdmin. If I pass text as qwery, table was created. – test_name Mar 19 '19 at 19:40
You can use following command in the terminal to generate correct SQL file. mysqldump -uroot -p database_name > planets_db.sql – kavindalb Mar 19 '19 at 19:42

PDO run script from .sql file

2 Answers2