Carl Max Carl Max - 2 months ago 6
MySQL Question

Creating a MYSQL database and its table at the same time in the same file

I am using PHP to create a MYSQL database and its table with values inserted into it. I want the database and its table to be created using the same PHP file. For some reason, the database and its table is not being created. How do I create a database and its table all at once? Thank you so much, this is the code below:

<?php
$servername = "localhost";
$username = "root";
$password = "";

// Create database
$sql = "CREATE DATABASE school";
if (mysqli_query($conn, $sql)) {
echo "Database created successfully";
} else {
echo "Error creating database: " . mysqli_error($conn);
}

mysqli_close($conn);

$dbname = "school"; //database name

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}


$sql = CREATE TABLE student (
'id' int(6) NOT NULL AUTO_INCREMENT,
'name' varchar(500) NOT NULL,
'gender' varchar(100) NOT NULL,
PRIMARY KEY ('id');

INSERT INTO 'student' ('id', 'name', 'gender') VALUES
(1, 'Cassidy Jackson', 'female'),
(2, 'jack Hilson', 'male');

if (mysqli_query($conn, $sql)) {
echo "Table school created successfully";
} else {
echo "Error creating table: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

Answer

You have a lot of errors. You don't open the connection but try to create a database. After this you close the nonexisting connection, etc. But your SQLs have errors too. First of all they are not string for php, and never use single quotes for column or table name,it is for only values.

Your code

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "";
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Create database
$sql = "CREATE DATABASE school";
if (mysqli_query($conn, $sql)) {
    echo "Database created successfully<br>";
} else {
    echo "Error creating database: " . mysqli_error($conn) . "<br>";
}

mysqli_select_db ( $conn , "school" );

 //database name

// Create connection

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


$sql = "CREATE TABLE `school` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(500) NOT NULL,
  `gender` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`),
   UNIQUE INDEX `idschool_UNIQUE` (`id` ASC));";
if(mysqli_query($conn, $sql)){
    echo "Table school created successfully<br>";
} else {
    echo "Error creating table: " . mysqli_error($conn). "<br>";
}

$sql = "INSERT INTO `student` (id, name, gender) VALUES
(1, 'Cassidy Jackson', 'female'),
(2, 'jack Hilson', 'male')";

if (mysqli_query($conn, $sql)) {
    echo "Values Inserted successfully<br>";
} else {
    echo "Error ─▒nsert values to table: " . mysqli_error($conn) . "<br>";
}

mysqli_close($conn);
?>