How to create multiple MySQL tables via PHP using a single query?

Question

I am trying to create a "setup script" for my website. I would like to create the database, adding tables and some content at the same time. So far this is how I did it, but it seems kind off messy using multiple queries:

<?php


$servername = "localhost";
$username = "root";
$password = "password";

// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Create database
$sql = "CREATE DATABASE MYDB";
if ($conn->query($sql) === TRUE) {

    echo "1. Database created successfully <br/>";
    $conn->select_db("MYDB");


    $sql_members = "CREATE TABLE MEMBERS (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    USERNAME VARCHAR(30) NOT NULL,
    EMAIL VARCHAR(40) NOT NULL,
    DISCOUNT VARCHAR(5),
    PASSW CHAR(128),
    ROLE VARCHAR(9)
    )";


    if ($conn->query($sql_members) === TRUE) {
        echo "2. Table MEMBERS created successfully <br/>";
    } else {
        echo "Error creating table: " . $conn->error;
    }

    $sql_content = "CREATE TABLE CONTENT (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    TITLE VARCHAR(30) NOT NULL,
    TEXT VARCHAR(30) NOT NULL
    )";

    if ($conn->query($sql_content) === TRUE) {
        echo "3. Table CONTENT created successfully <br/>";
    } else {
        echo "Error creating table: " . $conn->error;
    }



} else {
    echo "Error creating database: " . $conn->error;
}

$conn->close();


?>

Is there a better way? Thanks!

== UPDATE ==

I have tried to export the database and use the resulted .sql file as my setup query, but something is wrong, I get:

Error creating tables: 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 'INSERT INTO CONTACTS (ID, NAME, PHONE, EMAIL, ADDRESS, CITY, `COUN' at line 12

CREATE TABLE IF NOT EXISTS `CONTACTS` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(25) COLLATE utf8_romanian_ci NOT NULL,
  `PHONE` varchar(16) COLLATE utf8_romanian_ci NOT NULL,
  `EMAIL` varchar(35) COLLATE utf8_romanian_ci NOT NULL,
  `ADDRESS` text COLLATE utf8_romanian_ci NOT NULL,
  `CITY` varchar(16) COLLATE utf8_romanian_ci NOT NULL,
  `COUNTRY` varchar(16) COLLATE utf8_romanian_ci NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_romanian_ci AUTO_INCREMENT=2 ;

INSERT INTO `CONTACTS` (`ID`, `NAME`, `PHONE`, `EMAIL`, `ADDRESS`, `CITY`, `COUNTRY`) VALUES
(1, 'Peter Brown', '0742062307', '[email protected]', 'Avenue 13.', 'Santaclaus', 'Austria');

== SOLUTUION ==

I needed "multi_query()" for executing my multiple queries.

you can write all your sql queries in a single file and separate them using ; — user5588894
– user5588894, Commented Aug 17, 2016 at 13:10
Read the manual : php.net/manual/en/pdo.begintransaction.php OR search over stackoverflow : stackoverflow.com/questions/6346674/… ;) — Bobot
– Bobot, Commented Aug 17, 2016 at 13:18
@BenseidSeid could you plz drop a sample - just in case I miss something. Thanks! — Edmond Tamas
– Edmond Tamas, Commented Aug 17, 2016 at 13:20
Do try and relax your column lengths here. Unless you have a very compelling reason use VARCHAR(255) by default, especially for things like names, cities, and most importantly email addresses. — tadman
– tadman, Commented Aug 17, 2016 at 14:16
It's worth noting that what you're describing here is a very primitive form of database migration tool, something most ORMs offer as a core feature. Doctrine, Eloquent and Propel have this. — tadman
– tadman, Commented Aug 17, 2016 at 14:18

عبد الوهاب · Accepted Answer · 2017-11-21 12:01:44Z

5

You can try this too :p

$errors = [];

$table1 = "CREATE TABLE MEMBERS (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    USERNAME VARCHAR(30) NOT NULL,
    EMAIL VARCHAR(40) NOT NULL,
    DISCOUNT VARCHAR(5),
    PASSW CHAR(128),
    ROLE VARCHAR(9)
    )";

$table2 = "CREATE TABLE CONTENT (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    TITLE VARCHAR(30) NOT NULL,
    TEXT VARCHAR(30) NOT NULL
    )";


$tables = [$table1, $table2];


foreach($tables as $k => $sql){
    $query = @$conn->query($sql);

    if(!$query)
       $errors[] = "Table $k : Creation failed ($conn->error)";
    else
       $errors[] = "Table $k : Creation done";
}


foreach($errors as $msg) {
   echo "$msg <br>";
}

edited Nov 21, 2017 at 12:01

answered Feb 26, 2017 at 14:01

عبد الوهاب

662 silver badges4 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 10:29:44Z

0

You could export the whole database including all tables using the command line or using PhPMyAdmin. Then query the content of the file in php to create the database.

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Aug 17, 2016 at 13:16

Jonas Menk

11 bronze badge

2 Comments

Edmond Tamas Over a year ago

MAN! This is a freaking good idea!!! I was about to handwrite all the stuff from the beginning. Thanks!!

Jonas Menk Over a year ago

Glad I could help!

user5588894 · Accepted Answer · 2016-08-17 13:24:42Z

-2

you can create a file and put all your sql queries in it..

CREATE TABLE MEMBERS (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    USERNAME VARCHAR(30) NOT NULL,
    EMAIL VARCHAR(40) NOT NULL,
    DISCOUNT VARCHAR(5),
    PASSW CHAR(128),
    ROLE VARCHAR(9)
);

CREATE TABLE CONTENT (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    TITLE VARCHAR(30) NOT NULL,
    TEXT VARCHAR(30) NOT NULL
);

then in your php code:

$query = file_get_contents ('queries.sql');

if ($conn->query($query) === TRUE) {
    echo "all tables created successfully <br/>";
} else {
    echo "Error creating tables: " . $conn->error;
}

answered Aug 17, 2016 at 13:24

user5588894

2 Comments

Edmond Tamas Over a year ago

I have exported, and tried to use the .sql for my setup script, but I get an error when it comes to insert data into the tables, could you check out my updated answer plz?

tadman Over a year ago

Is === TRUE here really necessary?

Collectives™ on Stack Overflow

How to create multiple MySQL tables via PHP using a single query?

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related