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', 'office@shop.com', '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 `;` — , Aug 17 '16 at 13:10
Read the manual : http://php.net/manual/en/pdo.begintransaction.php OR search over stackoverflow : http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd ;) — Bobot, Aug 17 '16 at 13:18
@BenseidSeid could you plz drop a sample - just in case I miss something. Thanks! — Edmond Tamas, Aug 17 '16 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, Aug 17 '16 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](http://www.doctrine-project.org/projects/migrations.html), [Eloquent](https://laravel.com/docs/5.2/migrations) and [Propel](http://propelorm.org/documentation/09-migrations.html) have this. — tadman, Aug 17 '16 at 14:18

عبد الوهاب · Accepted Answer · 2017-11-21T12:01:44.980

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

score 0 · Answer 2 · edited May 23 '17 at 10:29

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 '17 at 10:29

Community

1
1

answered Aug 17 '16 at 13:16

Jonas Menk

1
1

1

MAN! This is a freaking good idea!!! I was about to handwrite all the stuff from the beginning. Thanks!! – Edmond Tamas Aug 17 '16 at 13:18
Glad I could help! – Jonas Menk Aug 17 '16 at 13:19

score -2 · Answer 3 · answered Aug 17 '16 at 13:24

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

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? — Edmond Tamas, Aug 17 '16 at 13:57

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

3 Answers3