PHP MySQLi Create Table
PHP Tutorial » PHP MySQLi Create Table
To create a table in MySQL database we use CREATE TABLE statement.
Syntax: SQL syntax to create a MySQL table
We will create a table named "Users", with six columns as: "id", "first_name", "last_name", "user_name", "email" and "reg_date":
id INT(7) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(42) NOT NULL,
last_name VARCHAR(42) NOT NULL,
user_name VARCHAR(42) NOT NULL,
email VARCHAR(65),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
Here are some explanations for each column:
- NOT NULL - is used because we do not want this field to be NULL, null values are not allowed
- DEFAULT value - Set a default value that is added when no other value is passed
- UNSIGNED - Used for number types, limits the stored data to positive numbers and zero
- AUTO INCREMENT - tells MySQL to go ahead and add the next available number to the id field
- PRIMARY KEY - we usually use for an ID number, and is often used with AUTO_INCREMENT
Create table in DataBase MySQLi (object oriented)
The following examples create a table "Users" in database my_DB:
<?php
$server_name = "localhost";
$user_name = "my_username";
$password = "my_password";
$db_name = "my_DB";
// Create connection
$link = new mysqli($server_name, $user_name,
$password, $db_name);// Check connection
if ($link->connect_error) {
die("Connection failed: " . $link->connect_error);
}// sql to create table
$sql = "CREATE TABLE Users (
id INT(7) NOT NULL AUTO_INCREMENT,
first_name VARCHAR(42) NOT NULL,
last_name VARCHAR(42) NOT NULL,
user_name VARCHAR(42) NOT NULL,
email VARCHAR(65),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id))";
if ($link->query($sql) === TRUE) {
echo "Table Users created successfully";
} else {
echo "Error creating table: " . $link->error;
}$link->close();
?>
Related subjects:
How to create database phpmyadmin?
Insert multiple rows
PHP select / view data
Conect to MySQLi
How can I create a table for my database in MySQLi?
PHP MySQLi Create Table, temporary table, user, if not exists
Create table in DataBase MySQLi (Procedural)
The following examples create a table named "Users" in database my_DB using MySQLi (Procedural)
<?php
$server_name = "localhost";
$user_name = "my_username";
$password = "my_password";
$db_name = "my_DB";
// Create connection
$link = mysqli_connect($server_name, $user_name,
$password, $db_name);// Check connection
if (!$link) {
die("Connection failed: " . mysqli_connect_error());
}// sql to create table
$sql = "CREATE TABLE Users (
id INT(7) NOT NULL AUTO_INCREMENT,
first_name VARCHAR(42) NOT NULL,
last_name VARCHAR(42) NOT NULL,
user_name VARCHAR(42) NOT NULL,
email VARCHAR(65),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id))";
if (mysqli_query($link, $sql)) {
echo "Table Users created successfully";
} else {
echo "Error creating table: " . mysqli_error($link);
}mysqli_close($link);
?>
Create table Users in DataBase using (PDO) extension
The following example create a table named "Users" in database my_DB using PDO
<?php
$server_name = "localhost";
$user_name = "my_username";
$password = "my_password";
$db_name = "my_DB";
try {
$link = new PDO("mysql:host=$server_name;dbname=$db_name", $user_name, $password);
// set the PDO error mode to exception
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// sql to create table
$sql = "CREATE TABLE Users (
id INT(7) NOT NULL AUTO_INCREMENT,
first_name VARCHAR(42) NOT NULL,
last_name VARCHAR(42) NOT NULL,
user_name VARCHAR(42) NOT NULL,
email VARCHAR(65),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id))";
// use exec() because no results are returned
$link->exec($sql);
echo "Table MyGuests created successfully";
} catch(PDOException $e) {
echo $sql . "<br>" . $e->getMessage();
}$link = null;
?>
How can I create a table for my database in MySQLi? PHP MySQLi Create Table, temporary table, user, if not exists
PHP MySQLi Create Table - php tutorial
This tool makes it easy to create, adjust, and experiment with custom colors for the web.
Magnews2 is a modern and creative free magazine and news website template that will help you kick off your online project in style.
Find here examples of creative and unique website layouts.
Find here examples of creative and unique website CSS HTML menu.