Tutorial

Sign Up

log In

PHP
MySQL Create Table

❮ Previous

Next ❯

Creating a table in MySQL using PHP involves writing a script to establish a database connection and executing a SQL CREATE TABLE query.


A database table has its own unique name and consists of columns and rows.


Create a Table Using MySQLi and PDO


We will create a table named "users", with five columns: "id", "name", "email" and "created_at":



Example (MySQLi Procedural)
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydatabase"; // Change to your database name

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// SQL to create table
$sql = "CREATE TABLE users (
id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
if (mysqli_query($conn, $sql)) {
echo "Table 'users' created successfully";
} else {
echo "Error creating table: " . mysqli_error($conn);
}
// Close connection
mysqli_close($conn);
?>

Above created table explanation:


Data types are used to specify the type of data that can be stored in a table's column.


You can specify other optional attributes for each column, followed by data type.


  • UNSIGNED: Ensures the value is positive.
  • AUTO_INCREMENT: Automatically increments for each new row.
  • PRIMARY KEY: Makes id the unique identifier for each record.
  • NOT NULL: Ensures the field cannot be empty.
  • UNIQUE: Ensures no duplicate email addresses.
  • DEFAULT CURRENT_TIMESTAMP: Automatically sets the current timestamp when a record is created.
Example (Object-Oriented)
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydatabase";

// 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 users (
id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

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

// Close connection
$conn->close();
?>
Example (Procedural Method)
<?php
// Step 1: Database connection settings
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";

// Step 2: Connect to the database
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Step 3: Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// Step 4: SQL query to create a table
$sql = "CREATE TABLE users (
id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- Unique ID for each user
name VARCHAR(100) NOT NULL, -- User's name
email VARCHAR(100) UNIQUE NOT NULL, -- User's email (must be unique)
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Record creation timestamp
)";

// Step 5: Execute the query
if (mysqli_query($conn, $sql)) {
echo "Table 'users' created successfully!";
} else {
echo "Error creating table: " . mysqli_error($conn);
}

// Step 6: Close the connection
mysqli_close($conn);
?>


❮ Previous

Next ❯
Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.
© Copyright 2024 www.whereisstuff.com. All rights reserved. Developed by whereisstuff Tech.