0

I have the following file schema.sql to be used on a MySQL database:

--
-- schema.sql
--

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;



/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `sometable` (`blah` tinyint(1) not null) Engine=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

DELIMITER //

CREATE PROCEDURE call_procedure()

BEGIN

SET @column_exists = (SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = (SELECT DATABASE())
AND TABLE_NAME = 'sometable'
AND COLUMN_NAME = 'stuff');

    -- Only modify if column exists
    IF @column_exists IS NULL THEN
    ALTER TABLE sometable ADD COLUMN stuff TINYINT(1) NOT NULL;
    END IF;

END;

//

DELIMITER ;

-- Execute the procedure
CALL call_procedure();

-- Drop the procedure
DROP PROCEDURE call_procedure;

When I go to my bash command line on Ubuntu, I can run this command and get exactly the results I want:

mysql -u root -pMyPass mydatabase < schema.sql

In other words, the table sometable is created and I see two columns blah and stuff.

So then I created a PHP script like this:

/// schema.php
$schema = file_get_contents('schema.sql');
$PDO = new PDO("mysql:host=localhost;dbname=mydatabase","root","MyPass");
$PDO->exec("set names utf8");
$PDO->exec($schema);

When I run this script, I get a database table sometable that only has one column blah. The column stuff never got created.

Why won't PHP run my sql script and create the column stuff the way my bash command line did it?

John
  • 32,403
  • 80
  • 251
  • 422
  • You need to execute one command at a time, not a pile of statements. – tadman Jun 29 '18 at 20:25
  • Check the MySQL error message. I'd bet on `DELIMITER //`. You might not need it with PHP/PDO. – Paul Spiegel Jun 29 '18 at 20:27
  • 1
    In fact, you *cannot* use `DELIMITER` with PDO, because `DELIMITER` is not recognized by the SQL parser of the MySQL Server. It's a builtin command of the `mysql` client. – Bill Karwin Jun 29 '18 at 20:28

0 Answers0