How to write multiple queries in a single tables.sql file and upload it using PHP?

Question

All I want is to create 2 or more tables using tables.sql file via PHP.

PHP `create_db.php`

<?php
require 'config.php';


$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD);
/* check connection */
if($mysqli->connect_errno){
  echo "MySQL connection failed.<br>";
  exit();
}else{
  echo "MySQL successfully connected.<br>";
}

// DB create
if($mysqli->query('CREATE DATABASE IF NOT EXISTS '.DB_NAME.';') === TRUE){
  echo "Database successfully created.<br>";
}else{
  echo "Error: ".$mysqli->errno.", ".$mysqli->error."<br>";
}

// DB select
if($mysqli->select_db(DB_NAME) === TRUE){
  echo "Database successfully selected.<br>";
}else{
  echo "Error: ".$mysqli->errno.", ".$mysqli->error;
}

// Create tables
if($mysqli->query(file_get_contents('../sql/tables.sql')) === TRUE){
  echo "Tables successfully created.<br>";
}else{
  echo "Error: ".$mysqli->errno.", ".$mysqli->error."<br>";
}

$mysqli->close();
?>

DB_HOST, DB_USER, DB_PASSWORD, DB_NAME are defined in config.php file

PHP v7.4.7

SQL `tables.sql`

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(100) NOT NULL,
  `password` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `status` (
  `status` varchar(100) NOT NULL,
  `IDtime` varchar(100) NOT NULL
);

If I upload this tables.sql file directly to MySQL server using command line it is working.

MySQL v8.0.20 MySQL Comunity Server - GPL

Error message

Error: 1064, 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 status ( status varchar(100) NOT NULL, `IDt' at line 16

blahy · Accepted Answer · 2020-07-20 21:24:17Z

1

mysqli::query only accepts single query https://www.php.net/manual/en/mysqli.query.php.

If you want to execute multiple queries at once you have to use mysqli::multi_query https://www.php.net/manual/en/mysqli.multi-query.php.

Example:

$result = $mysqli->query("SELECT * FROM reservations;SELECT * FROM reservations");
var_dump($result, $mysqli->error);

bool(false)
string(172) "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 'SELECT * FROM reservations' at line 1"


$result = $mysqli->multi_query("SELECT * FROM reservations;SELECT * FROM reservations");
var_dump($result, $mysqli->error);

bool(true)
string(0) ""

answered Jul 20, 2020 at 21:24

blahy

1,3191 gold badge8 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

How to write multiple queries in a single tables.sql file and upload it using PHP?

PHP `create_db.php`

SQL `tables.sql`

Error message

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

PHP create_db.php

SQL tables.sql

Error message

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related

PHP `create_db.php`

SQL `tables.sql`