StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to create a view with mysqli_multi_query?
text
Body
copied!<p>I'm developing a PHP application, that should be able to setup a project database and update it / its data definition, using a list of SQL files. Here is a first naïve quick&dirty written procedural prototype:</p> <pre><code><?php function executeSQLFiles(array $dbOptions, array $dbFiles) { $dbConnection = mysqli_connect($dbOptions['host'], $dbOptions['user'], $dbOptions['password'], $dbOptions['database']); if (mysqli_connect_errno($dbConnection)) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } // db setup foreach ($dbFiles['setup'] as $listItem) { $query = file_get_contents(__DIR__ . '/../../config/database/' . $listItem['file']); $result = mysqli_multi_query($dbConnection, $query); if (!$result) { die($listItem['file'] . ': ' . 'Invalid query: ' . mysqli_error($dbConnection) . PHP_EOL); } else { echo $listItem['file'] . ' ' . 'OK' . PHP_EOL; } } // db migration } </code></pre> <p>It works for tables, but doesn't work for views. I don't get any errors, the view just does not get created and I get the message "filename.sql OK".</p> <p>The view creating SQL script (generated with MySQL Workbench) is OK. When I execute it in a MySQL client, a view is created.</p> <pre><code>-- ----------------------------------------------------- -- Placeholder table for view `allproviders` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `allproviders` (`id` INT, `providertype` INT, `providerid` INT, `displayedname` INT, `url` INT, `city_id` INT); SHOW WARNINGS; -- ----------------------------------------------------- -- View `allproviders` -- ----------------------------------------------------- DROP VIEW IF EXISTS `allproviders` ; SHOW WARNINGS; DROP TABLE IF EXISTS `allproviders`; SHOW WARNINGS; DELIMITER $$ CREATE OR REPLACE VIEW `allproviders` AS SELECT `providers`.`id`, `providers`.`type` AS `providertype`, `providers`.`providerid` AS `providerid`, `universities`.`displayedname`, `universities`.`url`, `universities`.`city_id` FROM `providers` JOIN `universities` ON `providers`.`providerid` = `universities`.`id` UNION SELECT `providers`.`id`, `providers`.`type` AS `providertype`, `providers`.`providerid` AS `providerid`, `partners`.`displayedname`, NULL `url`, `partners`.`city_id` FROM `providers` JOIN `partners` ON `providers`.`providerid` = `partners`.`id` $$ DELIMITER ; ; SHOW WARNINGS; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; </code></pre> <p>Am I doing something wrong? How to get it working?</p> <p>Thx</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload