About Lesson
Create Database and Table:
Before performing CRUD operations, we need a database and a table to store data.
- Database → Container for tables.
- Table → Holds records in rows and columns.
PHP MySQLi provides functions to create databases and tables.
1. Create Database:
Syntax(Procedural):
mysqli_query($connection, $sql);Example:
PHP
<?php
$servername = "localhost";
$username = "root";
$password = "";
// Step 1: Connect to MySQL server
$conn = mysqli_connect($servername, $username, $password);
// Step 2: Check connection
if(!$conn){
die("Connection failed: " . mysqli_connect_error());
}
// Step 3: Create Database
$sql = "CREATE DATABASE mydatabase";
if(mysqli_query($conn, $sql)){
echo "Database created successfully";
} else {
echo "Error creating database: " . mysqli_error($conn);
}
// Step 4: Close connection
mysqli_close($conn);
?>Explanation of Functions:
| Function | Purpose |
|---|---|
mysqli_connect() | Connects to MySQL server |
mysqli_query() | Executes SQL query |
mysqli_connect_error() | Returns connection error |
mysqli_error() | Returns SQL query error |
mysqli_close() | Closes the connection |
2. Create Table:
Syntax:
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);Example:
PHP
<?php
$conn = new mysqli("localhost", "root", "", "mydatabase");
// Step 1: Check connection
if($conn->connect_error){
die("Connection failed: " . $conn->connect_error);
}
// Step 2: Create Table
$sql = "CREATE TABLE students (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
if($conn->query($sql) === TRUE){
echo "Table 'students' created successfully";
} else {
echo "Error creating table: " . $conn->error;
}
// Step 3: Close connection
$conn->close();
?>Explanation of Functions and Syntax:
| Function / Keyword | Purpose |
|---|---|
new mysqli(...) | Creates connection (OO style) |
$conn->query($sql) | Executes SQL query |
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY | Unique identifier, auto increments |
name VARCHAR(50) NOT NULL | Name column, max 50 chars, required |
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP | Stores creation time automatically |
$conn->error | Displays error if query fails |
$conn->close() | Closes connection |