1

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:

<?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
}

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".

The view creating SQL script (generated with MySQL Workbench) is OK. When I execute it in a MySQL client, a view is created.

-- -----------------------------------------------------
-- 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;

Am I doing something wrong? How to get it working?

Thx

automatix
  • 14,018
  • 26
  • 105
  • 230
  • does the `$dbOptions['user']` have the correct GRANTs for creating VIEWs when connecting through your webserver? – dnagirl Mar 14 '13 at 14:19
  • Yes. It's the root user and has all priivlegs from äny host. [see here](http://i.stack.imgur.com/cxBYv.png). – automatix Mar 14 '13 at 14:38
  • I found out, that it works, when I delete the delimiters (lines `DELIMITER $$`, `$$`, and `DELIMITER $$`). – automatix Mar 14 '13 at 18:56
  • An explanation, why it only wokrs without `DELIMITER`s see [here](http://stackoverflow.com/questions/15418218/how-to-get-mysqli-working-with-delimiters-in-sql-statements#comment21807948_15418218). – automatix Mar 16 '13 at 11:02

0 Answers0