WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

PHP Create MySQL Tables


A database table has its own one of a kind name and comprises of segments and rows.


Create a MySQL Table Using MySQLi and PDO

The CREATE TABLE explanation is utilized to make a table in MySQL.

We will make a table named "MyGuests", with five segments: "id", "firstname", "lastname", "email" and "reg_date":

Make 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
)

Notes on the table above:

The information type indicates what sort of information the segment can hold. For a total reference of all the accessible information types, go to our Data Types reference.

After the information type, you can determine other discretionary qualities for each column:

  • NOT NULL - Each line must contain an incentive for that segment, invalid qualities are not allowed
  • DEFAULT esteem - Set a default esteem that is included when no other esteem is passed
  • UNSIGNED - Used for number sorts, restrains the put away information to positive numbers and zero
  • AUTO INCREMENT - MySQL naturally builds the estimation of the field by 1 each time another record is added
  • PRIMARY KEY - Used to remarkably distinguish the lines in a table. The section with PRIMARY KEY setting is regularly an ID number, and is frequently utilized with AUTO_INCREMENT

Each table ought to have an essential key segment (for this situation: the "id" segment). Its esteem must be extraordinary for each record in the table.

The following precedents tells the best way to make the table in PHP:

Example (MySQLi Object-oriented)

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

//Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
/Check connection
in the event that ($conn->connect_error) {
    die("Connection fizzled: " . $conn->connect_error);
}

//sql to make 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) {
    reverberation "Table MyGuests made successfully";
} else {
    reverberation "Error making table: " . $conn->error;
}

$conn->close();
?>

Example (MySQLi Procedural)

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

//Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
/Check connection
in the event that (!$conn) {
    die("Connection fizzled: " . mysqli_connect_error());
}

//sql to make 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 (mysqli_query($conn, $sql)) {
    reverberation "Table MyGuests made successfully";
} else {
    reverberation "Error making table: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

Example (PDO)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    /set the PDO blunder mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

   /sql to make 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
    )";

   /use executive() on the grounds that no results are returned
    $conn->exec($sql);
    reverberation "Table MyGuests made successfully";
    }
catch(PDOException $e)
    {
    reverberation $sql . "<br>" . $e->getMessage();
    }

$conn = null;
?>