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, which conveniently offers a method named multi_query that allows you to execute multiple queries separated by semicolons. — FThompson
– FThompson, Commented Oct 23, 2013 at 7:14

Community · Accepted Answer · 2023-11-17 20:16:56Z

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.

edited Nov 17, 2023 at 20:16

CommunityBot

11 silver badge

answered Oct 23, 2013 at 7:19

ST3

8,9805 gold badges72 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

test Over a year ago

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 ?

ST3 Over a year ago

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

j.son · Accepted Answer · 2013-10-23 07:35:21Z

2

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

answered Oct 23, 2013 at 7:35

j.son

211 bronze badge

Collectives™ on Stack Overflow

MySQL syntax for creating multiple tables

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related