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?
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()