MySQL syntax for creating multiple tables

Question

I am having a some problem that i don’t understand and trying to create tables from a php file , so i created a variable and i added all the create statements in it so i can run a mysql_query on it , i keep getting an error that there is a syntax problem , so i echo the content of the variable and past it in the sql section at phpmyadmin and it word fine but if i try it from php it gives me an error as :

Error creating table:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE IF NOT EXISTS Materials ( Id int(11) NOT NULL AUTO_INCREMENT,' at line 9

here is the string in the variable that i run the query on :

   // Create Tables Variable
$Tables = "";

// Create Students Table
$Tables = $Tables . "CREATE TABLE IF NOT EXISTS `Students` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(50) DEFAULT NULL,
  `LastName` varchar(50) DEFAULT NULL,
  `Age` int(3) DEFAULT NULL,
  `Major` varchar(50) DEFAULT NULL,
  `Image` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`Id`)
);";

// Create Materials Table
$Tables = $Tables . "CREATE TABLE IF NOT EXISTS `Materials` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) DEFAULT NULL,
  `LastName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`Id`)
);";

// Create Materials Students intersectons table for many to many relation
$Tables = $Tables . "CREATE TABLE `Material_Student`(
    `Student_Id` INT NOT NULL,  
    `Material_id` INT NOT NULL,    
    FOREIGN KEY (`Student_Id`) REFERENCES Students(`id`) ON UPDATE CASCADE,  
    FOREIGN KEY (`Material_id`) REFERENCES Materials(`id`) ON UPDATE CASCADE
);";

// Create Pages Table
$Tables = $Tables . "CREATE TABLE `Pages`(
    `Id` INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(`Id`),
    `Name` varchar(50),
    `Conent` varchar(5000)

);";

// Create News Table
$Tables = $Tables . "CREATE TABLE `News`(
    `Id` INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(`Id`),
    `Title` varchar(50),
    `Conent` varchar(5000),
    `Image` varchar(100)
);";

// Create Notifications Table
$Tables = $Tables . "CREATE TABLE `Notifications`(
    `Id` INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(`Id`),
    `Title` varchar(50),
    `Conent` varchar(5000),
    `Student_Id` INT NOT NULL,  
    FOREIGN KEY (`Student_Id`) REFERENCES Students(`id`) ON UPDATE CASCADE
);";

so what am I missing here ??

You should upgrade to a non-deprecated mysql API such as [mysqli](http://php.net/manual/en/book.mysqli.php), which conveniently offers a method named [`multi_query`](http://php.net/manual/en/mysqli.multi-query.php) that allows you to execute multiple queries separated by semicolons. — FThompson, Oct 23 '13 at 07:14
somehow related:: http://stackoverflow.com/questions/14023810/create-multiple-mysql-tables-using-php — Sudhir Bastakoti, Oct 23 '13 at 07:20

score 2 · Accepted Answer · answered Oct 23 '13 at 07:19

2

In usual way you cannot execute many queries, I would recommend to execute all of them like different queries, however MySQLi has an option to execute many queries at once, documentation and examples you can find here.

answered Oct 23 '13 at 07:19

ST3

8,826
3
68
92

So The Problem is Trying to execute multiple queries at once using mysql_query() , so i need to do each one separately or use mysqli::multi_query ( string $query ) , did i understand the solution well ? – test Oct 23 '13 at 07:24
@user Yes, `mysqli::multi_query ( string $query )` let to execute multiple queries at once. But you have to use `MySQLi` methods, instead of `MySQL`. However they are really similar, so you shouldn't find difficulties. There is a good example on the page I gave a link. – ST3 Oct 23 '13 at 07:29

score 2 · Answer 2 · answered Oct 23 '13 at 07:35

Your issue seems to be passing a multiple-query into Php's single query function.

See this link for explanation. http://php.net/manual/en/mysqli.quickstart.multiple-statement.php

I suggest you either break down your statements and execute one at a time, or use multi_query function instead.

http://www.php.net/manual/en/mysqli.multi-query.php

$mysqli = new mysqli("localhost", "yourusername", "yourpassword", "test");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->multi_query($Tables)) {
    echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

...

HTH

MySQL syntax for creating multiple tables

2 Answers2