Using MySQLi in PHP
Introduction
MySQLi (MySQL Improved) is a relational database driver used in the PHP programming language to provide an interface with MySQL databases. It is an enhancement of the older MySQL extension, offering more features and security. This tutorial will guide you through the basic usage of MySQLi, from connection to performing CRUD (Create, Read, Update, Delete) operations.
Connecting to a MySQL Database
To start using MySQLi in your PHP script, you need to establish a connection to the MySQL database. This is done using the mysqli_connect() function.
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$database = "database";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
Creating a Table
With a successful connection to the database, you can now create a table using the CREATE TABLE SQL statement.
<?php
// SQL to create 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)) {
echo "Table MyGuests created successfully";
} else {
echo "Error creating table: " . mysqli_error($conn);
}
?>
Inserting Data
Inserting data into a table is done using the INSERT INTO SQL statement.
<?php
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "
" . mysqli_error($conn);
}
?>
Fetching Data
Fetching data from the database can be done using the SELECT SQL statement.
<?php
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// Output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
?>
Updating Data
Updating existing records in a table can be accomplished using the UPDATE SQL statement.
<?php
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
if (mysqli_query($conn, $sql)) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . mysqli_error($conn);
}
?>
Deleting Data
To delete records from a table, use the DELETE SQL statement.
<?php
$sql = "DELETE FROM MyGuests WHERE id=3";
if (mysqli_query($conn, $sql)) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . mysqli_error($conn);
}
?>
Closing the Connection
It is a good practice to close the database connection once you are done with your operations.
<?php
mysqli_close($conn);
?>
Conclusion
This tutorial provided a comprehensive guide on using MySQLi in PHP, covering the basics of connecting to a database, creating tables, and performing CRUD operations. Mastery of these fundamentals will enable you to build more complex and dynamic web applications.