Create a MySQL Table Using PHP

In this article i will show you how to create MySQL Table Using PHP.

What is a table?

Table is a set of data elements using a model of straight columns and horizontal lines, a cell is the unit where a row and column meet. A table has a specified number of columns, but can have any number of rows.

Creating a MySQL Table using MySQLi and PDO

The steps for creating a table are the same as creating a database. The difference instead of creating a new database will link to an existing database and create a table in that database.

CREATE TABLE statement is used to create a table in my sql database

In this article a table named “users”, with three columns: “id”, “full name” and “email”.

CREATE TABLE  users(
        id INT(2)  PRIMARY KEY AUTO_INCREMENT, 
        fullname VARCHAR(30) NOT NULL,
        email VARCHAR(30) )";

The data types use for this table:

VARCHAR: Contains a unit of character of different lengths that may contain letters, numbers, and special characters.

INT: The INTEGER is a type of data that accepts numerical values ​​with a specified zero value.

PRIMARY KEY: Used to identify lines in a separate table. A column with PRIMARY KEY settings is usually an ID number.

NOT NULL: Each row must contain the value of that column, empty values are not allowed.

AUTO INCREMENT: MySQL automatically increases the value of the field by 1 when the new data created on a table

 

Create a MySQL Table using MySQLi and PDO

 

MySQLi Object-Oriented
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

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

// SQL Code For Creating Table
$sql = "CREATE TABLE  users(
        id INT(2)  PRIMARY KEY AUTO_INCREMENT, 
        fullname VARCHAR(30) NOT NULL,
        email VARCHAR(30) 
        )";

if ($conn->query($sql) === true) {
    echo "Table Created Successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

$conn->close();


?>
MySQLi Procedural
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

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

// SQL CODE FOR CREATING A TABLE

$sql = "CREATE TABLE users(

id INT(2) PRIMARY KEY AUTO_INCREMENT, 

fullname VARCHAR(30) NOT NULL,

email VARCHAR(30) 


        )";

if (mysqli_query($conn, $sql)) {
    echo "Table Created Successfully";
} else {
    echo "Error creating table: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
PDO
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

try {
    $conn = new PDO(
        "mysql:host=$servername;dbname=$dbname",
        $username,
        $password
    );

    // setting the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL CODE FOR CREATING A TABLE
    $sql = "CREATE TABLE  users(
        id INT(2)  PRIMARY KEY AUTO_INCREMENT, 
        fullname VARCHAR(30) NOT NULL,
        email VARCHAR(30) 
            )";

    // using exec() because no results are returned
    $conn->exec($sql);
    echo "Table Created Successfully";
} catch (PDOException $e) {
    echo $sql .
        "
" .
        $e->getMessage();
}

$conn = null;
?>

 

Leave a Comment

Your email address will not be published.