CODEWITHSUNDEEP
phpmysqldatabasemysqli
Marcus Christiansen
Marcus Christiansen

Reputation: 3197

MySQLi cannot create new table in PHP

I'm getting frustrated with running basic SQL statements in PHP. I keep running into syntax errors that ask me to refer to the current server version of MySQL.

I'm trying to run the follow SQL query:

CREATE DATABASE mc_todo_app;

use mc_todo_app;

CREATE TABLE todos (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    task VARCHAR(255) NOT NULL,
    completed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

In PHP I then try to run this script.

require "config.php";

// Create connection
$conn = mysqli_connect($host, $username, $password);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = file_get_contents('data/init.sql');
$result = mysqli_query($conn, $sql);

if (false === $result) {
  printf("Error: %s\n", mysqli_error($conn));
} else {
  echo "DB and Table successfully created!";
}

mysqli_close($conn);

This produces the following error message:

Error: 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 'use mc_todo_app; CREATE TABLE todos ( id INT PRIMARY KEY AUTO_INCREMENT, tas' at line 3

Here are the details of my Database server: enter image description here

I'm even trying basic Table creation statements from W3Schools without success.

What am I doing wrong?

Upvotes: 1

Views: 189

Answers (1)

Qirel
Qirel

Reputation: 26460

mysqli_query() can only execute one query at a time, while you are attempting to execute multiple queries. You can use mysqli_multi_query() instead. 

$sql = file_get_contents('data/init.sql');
$result = mysqli_multi_query($conn, $sql);

Upvotes: 2

Related Questions