PHP MySQL Create Table
PHP-MySQLi Tutorial » PHP MySQL Create Table
A database table for MySQL has its own unique name and is composed of columns and rows.
Create a MySQL Table Using MySQLi and PDO
We will create a table named "Students", with six columns as: "id", "first_name", "last-name", "email", "password" and "date":
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(50),
password VARCHAR(50),
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
Meaning of attributes for each column:
- NOT NULL - Each row must contain a value for that column, 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 - MySQL automatically increases the value of the field by 1 each time a new record is added
- PRIMARY KEY - Used to uniquely identify the rows in a table. The column with PRIMARY KEY setting is often an ID number, and is often used with AUTO_INCREMENT
Each table should have a primary key column (in this case: the "id" column). Its value must be unique for each record in the table.
Create a MySQL Table Using ( MySQLi Object-oriented ) - example
Open a Connection to MySQL
Before we can access data in MySQL database, we need to be able to connect to the server:
<?php
$server_name = "localhost";
$user_name = "username";
$password = "password";
$db_name = "myDB";
// Create connection
$con = new mysqli($server_name, $user_name, $password, $db_name);
// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}
// sql to create table
$sql = "CREATE TABLE Students (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
if ($con->query($sql) === TRUE) {
echo "Table Students created successfully";
}
else
{
echo "Error creating table: " . $con->error;
}
$con->close();
?>
When you create a new database, you must only specify the first three arguments to the mysqli object (server-name, user-name and password).
MySQLi Procedural Example (MySQLi Procedural)
Example (MySQLi Procedural)
<?php
$server_name = "localhost";
$user_name = "username";
$password = "password";
$db_name = "myDB";
// Create connection
$con = mysqli_connect($server_name, $user_name, $password, $db_name);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
// sql to create table
$sql = "CREATE TABLE Students (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
if (mysqli_query($con, $sql)) {
echo "Table Students created successfully";
} else {
echo "Error creating table: " . mysqli_error($con);
}
mysqli_close($con);
?>
Tags: php mysql create table if not exist, foreign key, auto_increment, unique key, php mysql create table and insert data, primary key
Example (PDO) - example
Example (PDO)
<?php
$serve_rname ="localhost";
$user_name ="username";
$password ="password";
$db_name ="myDB_PDO";try {
$con = new PDO("mysql:host=$server_name;dbname=$db_name", $user_name, $password);
// set the PDO error mode to exception
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);// sql to create table
$sql = "CREATE TABLE Students (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";// use exec() because no results are returned
$con->exec($sql);
echo "Table Student created successfully";
} catch(PDOException $e) {
echo $sql . "<br>" . $e->getMessage();
}$cnn = null;
?>
php mysql create table if not exist, foreign key, auto_increment, unique key, php mysql create table and insert data, primary key
PHP MySQL Create Table - php mysqli
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.