Create a MySQL Table using php

You can use a php script to create MySQL table.

Example.

[php]<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

// sql to create table
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
echo "Table MyGuests created successfully";
} else {
echo "Error creating table: " . $conn->error;
}

$conn->close();
?>[/php]

Code Explanation:

First, we have to use the code to connect the server.

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

Once the connection is successfully establish the next step is creating a MySQL table. To do that we will run the MySQL query.


$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";

In the $sql query we are asking php script to CREATE TABLE myGuests. In myGuest table, we will be having different columns.

id – The id represents the integer value means it will hold the numbers. We have assigned the AUTO_INCREMENT condition which will increase the number by 1 when new entry added to the database. It has given the value PRIMARY KEY which means it will be always unique. You can add duplicate value to the PRIMARY Key.

firstname – In this column, we will be adding text value which will consider the VARCHAR. The NOT NULL condition says you can’t keep it blank. There has to be some value in this field.

lastname – In this column similar to the firstname we have instructed the mysql to give the condition VARCHAR with NOT NULL value.

email – The email field has the VARCHAR value with no special condition. It means it can be run without adding any value to it.

reg_date – Is the statement to add default date to the column.

In the next line of php, we are checking if the connection is successfully achieve by using if statement.

if ($conn->query($sql) === TRUE) {
echo "Table MyGuests created successfully";
} else {
echo "Error creating table: " . $conn->error;
}

In the if statement we are checking both the connection $conn which represents the server connection and query($sql) represents the mysql connection. The identical check operator is used to see whether the value is TRUE or not. According to the outcome, the echo statement is generated.

If everything goes well as per the script then you will see a new entry in your mysql table.