Run a database dump (Restore) from an sql file in PHP + PostgreSql

I’m trying to create a system where a user can create a database and have it populated from a UI, pretty much similar to the installation of most CMS’s. I can programatically create the database but having the tables created and populated is where I’m facing a challenge.

I have a template sql file with these tables and data which is occasionally updated by dumping and this is what I’m doing:

try {
        $dbTablesAndData = file_get_contents('path/to/template.sql');
        $db = setDBConnection($clientDb);
        $query = $db->prepare("$dbTablesAndData");
        $query->execute( array() );

      } catch (PDOException $e) {
        $app->response()->setStatus(401);
        $app->response()->headers->set('Content-Type', 'application/json');
        echo  json_encode(array('response' => 'error', 'data' => $e->getMessage() ));
      }

When this runs I get an error: cannot insert multiple commands into prepared statement

So my question is, Is there a way to execute all the queries in this file successfully?

I read of a kind of a related solution from this question but it will not work for me because it proposes splitting the statements by the semi colons:

$statements = explode(';', $script);
foreach($statements as $sql){
    if($sql === '') { continue; }
    $query = $pdo->prepare($sql);
    $query->execute();
    if($query->errorCode() !== '00000'){ die("ERROR: SQL error code: ".$query->errorCode()."n"); }
}

But my sql file contains cases where the semi colons should not be split like declaration of variables in creation of functions etc. Is there any way I can go about this?

Answer

Found a solution to this. Apparently it’S only with PDO where you can’t run multiple commands. So something like pg_query() would suffice rather than prepare()

Advertisement

Answer