MySQL is the most popular database system that is used within PHP.
Let's first understand about MySQL.
What is MySQL?
MySQL is open-source Relational Database Management System (RDMS). The name of MySQL is a combination of "My" which is the name of co-founder Michael Widenius's (main author of the original version of open-source MySQL) daughter, and "SQL" is abstract from Structured Query Language.
MySQL used by many popular websites like Facebook, Twitter, and YouTube.
MySQL is easy to install. If we install Wamp or Xampp server, then we do not need to install the MySQL database as it is already available in the Wamp or Xampp server.
- MySQL is a Relational Database Management System (RDMS).
- MySQL is distributed and developed by Oracle Corp.
- MySQL is easy to use, fast, powerful, and secure.
- MySQL can be compiled on several platforms.
- MySQL is open-source, which means we can freely and easily download and use it.
- MySQL uses standard SQL.
- MySQL runs on the server.
MySQL database stores data into tables like another relational database. A table is a collection of data, and it is divided into rows and columns. Databases are useful for storing information sequentially.
A university may have a database with the following tables:
- Student Name
- Age
- Batch Year
- Field
- Mobile
Downloading MySQL Database
We can download MySQL free from this link:
http://www.mysql.com
Relationship between MySQL and PHP?
PHP is used to create a program and MySQL is used to interact with data sets. While developing a PHP application, we need to create a connection to a database. Then we will able to insert, delete, select, and update data.
There are multiple ways to use MySQL in a PHP script.
PHP connect to MySQL
PHP 5 works with MySQL database using:
- MySQLi ("i" stands for "improved").
- PDO (PHP Data Objects).
An earlier version of PHP used the MySQL extension.
Should we use MySQLi or PDO?
MySQLi or PDO have their advantages:
- MySQLi only work with MySQL databases, whereas PDO work on 12 different database systems.
- PDO makes the work easy.
- If we need to switch our project to use another database, with the help of PDO, we have to change the connection string and a few queries.
- With MySQLi, we need to re-write the entire code, including quires.
MySQLi Installation:
For installation details:
http://php.net/manual/en/mysqli.installation.php
PDO Installation:
For installation details:
http://php.net/manual/en/pdo.installation.php
Connection to MySQL:
Example (MySQLi Object-Oriented)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Creating Connection
$conn = new mysqli($servername, $username, $password);
// Connection Checking
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
Example (MySQLi Procedural)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Creating connection
$conn = mysqli_connect($servername, $username, $password);
// Checking connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
Example (PDO)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
try {
$conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->get Message();
}
?>
Close the Connection:
The connection can be closed automatically when the script ends. To close the connection before, we can use the following:
Example (MySQLi Object-Oriented)
$conn->close();
Example (MySQLi Procedural)
mysqli_close($conn);
Example (PDO)
$conn = null;
PHP Create MySQL Database
Create MySQL Database using MySQLi:
The CREATE DATABASE command is used to create a database in MySQL.
Create a database name with "details".
Example (MySQLi Object-Oriented)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Create database
$sql = "CREATE DATABASE details";
if ($conn->query($sql) === TRUE)
{
echo "Database created successfully";
}
else
{
echo "Error creating database: " . $conn->error;
}
$conn->close();
?>
Example MySQLi Procedural
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Creating connection
$conn = mysqli_connect($servername, $username, $password);
// Checking connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Creating database
$sql = "CREATE DATABASE details";
if (mysqli_query($conn, $sql))
{
echo "Database created successfully";
}
else
{
echo "Error creating database: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Create a database name with "mydetails".
Example (PDO)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
try
{
$conn = new PDO("mysql:host=$servername", $username, $password);
// set the PDO error mode to exception
$conn-> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "CREATE DATABASE mydetails";
// use exec() because no results are returned
$conn->exec($sql);
echo "Database created successfully<br>";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
- PDO has an exception class that is used to handle any problems which may occur in our database queries. If an exception is thrown within the try { } block, the scripts will stop executing and control moves directly to the first catch () {} block.
PHP create MySQL Tables:
A MySQL database table has its unique name, and it consists of rows and columns.
Create a MySQL table using MySQLi:
For creating a table, we use CREATE TABLE command in the MySQL database.
We will create a table with a name of "students", which consists of five columns, "id", "first_name", "last_name", "age", and "batch".
The columns can hold the data which can specify the data types.
Attributes for each column:
- NOT NULL: Every row contains a value for the column. A row cannot be null.
- DEFAULT: We can set any default value when no value set.
- AUTOINCREMENT: MySQL automatically increases the value of the field by one each time a new record added.
- PRIMARY KEY: Used to identify the rows in a table uniquely.
The table should have a primary key column (like "id"). The value of the primary key column must be unique.
Example (MySQLi Object-oriented):
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "details";
// Creating connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "CREATE TABLE students(
id INT(2) AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
age INT(5),
batch VARCHAR(20)
)";
if ($conn->query($sql) === TRUE) {
echo "Table students created successfully";
}
else {
echo "Error creating table: " . $conn->error;
}
$conn->close();
?>
Example (MySQLi Procedural):
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "details";
// creating connection
$conn = mysqli_connect ($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "CREATE TABLE students(
id INT(2) AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
age INT(5),
batch VARCHAR(20)
)";
if (mysqli_query($conn, $sql)) {
echo "Table students created successfully";
} else {
echo "Error creating table: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Example (PDO)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "mydetails";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "CREATE TABLE students(
id INT(2) AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
age INT(5),
batch VARCHAR(20)
)";
// use exec() because no results are returned
$conn->exec($sql);
echo "Table students created successfully";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
PHP Insert data to MySQL:
Once the database and table created, we can start inserting data into them.
Syntax:
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
Syntax Rules:
- The query must be quoted in PHP Language.
- The string values inside the query must be quoted.
- The numeric and word value must not be quoted.
- A column AUTO_INCREMENT ("id") is no need to specify in the SQL query. The MySQL will automatically add the value.
Example (MySQLi Object-Oriented):
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "details";
// Creating connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error)
{
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO students (first_name, last_name, age, batch)
VALUES ('Sonoo', 'Jaiswal', '23', '2015')";
if ($conn->query($sql) === TRUE)
{
echo "New record created successfully";
}
else
{
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
Example (MySQLi Procedural):
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "details";
// Creating connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn)
{
die("Connection failed: " . mysqli_connect_error());
}
$sql = "INSERT INTO students (first_name, last_name, age, batch)
VALUES ('Sonoo', 'Jaiswal', '23', '2015')";
if (mysqli_query($conn, $sql))
{
echo "New record created successfully";
}
else
{
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>
Example (PDO):
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "mydetails";
try
{
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO students (first_name, last_name, age, batch)
VALUES ('Sonoo', 'Jaiswal', '23', '2015')";
// use exec() because no results are returned
$conn->exec($sql);
echo "New record created successfully";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
MySQL Insert Multiple Records:
Multiple MySQL commands can be executed with the mysqli_multi_query().
Example (MySQLi Object-Oriented):
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "details";
// Creating connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Checking connection
if ($conn->connect_error)
{
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO students (first_name, last_name, age, batch)
VALUES ('Sonoo', 'Jaiswal', '23', '2015')";
$sql .= "INSERT INTO students (first_name, last_name, age, batch)
VALUES ('Rahul', 'Jain', '31', '2019')";
$sql .= "INSERT INTO students (first_name, last_name, age, batch)
VALUES ('Mehak', 'Malik', '26', '2014')";
if ($conn->multi_query($sql) === TRUE)
{
echo "New records created successfully";
}
else
{
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
- Each MySQL command must be separated by a semicolon (;).
Example (MySQLi Procedural):
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "details";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "INSERT INTO students (first_name, last_name, age, batch)
VALUES ('Sonoo', 'Jaiswal', '23', '2015')";
$sql .= "INSERT INTO students (first_name, last_name, age, batch)
VALUES ('Rahul', 'Jain', '31', '2019')";
$sql .= "INSERT INTO students (first_name, last_name, age, batch)
VALUES ('Mehak', 'Malik', '26', '2014')";
if (mysqli_multi_query($conn, $sql)) {
echo "New records created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>
Example (PDO):
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "mydetails";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// begin the transaction
$conn->beginTransaction();
// our SQL statements
$conn->exec("INSERT INTO students (first_name, last_name, age, batch)
VALUES ('Sonoo', 'Jaiswal', '23', '2015')");
$conn->exec("INSERT INTO students (first_name, last_name, age, batch)
VALUES ('Rahul', 'Jain', '31', '2019')");
$conn->exec("INSERT INTO students (first_name, last_name, age, batch)
VALUES ('Mehak', 'Malik', '26', '2014')");
// commit the transaction
$conn->commit();
echo "New records created successfully";
}
catch(PDOException $e)
{
// roll back the transaction if something failed
$conn->rollback();
echo "Error: " . $e->getMessage();
}
$conn = null;
?>
PHP select data from MySQL:
The select command is used to select data from one or more tables.
Syntax:
SELECT coloumn_name from table_name
The above syntax used to select data from a specified column.
SELECT * from table_name
In the above syntax, the (
*) character is used to select data from all the columns.
Select Data with MySQLi:
Example (MySQLi Object-Oriented):
<!DOCTYPE html>
<html>
<body>
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "details";
// Creating connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error)
{
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, first_name, last_name FROM students";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<br> id: ". $row["id"]. " - Name: ". $row["first_name"]. " " . $row"last_name"] . "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
</body>
</html>
The above example selects the id, first_name, and last_name columns from the Students table. In the above code, we have set a MySQL query which selects the id, first_name and last_name columns from the Students table.
The next line runs the query and puts the output data into a variable which is known as a $result.
The num_row() function is used to check the more than zero rows returned or not. If the more than zero rows returned, the fetch_assoc() function keeps all the output into an associative array by which we can loop through.
The while() loops with the result set and outputs the data by id, first_name, and last_name columns.
Example(MySQLi Procedural):
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "details";
// Creating connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn)
{
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT id, first_name, last_name FROM students";
$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["first_name"]. " " . $row"last_name"]. "<br>";
}
}
else
{
echo "0 results";
}
mysqli_close($conn);
?>
Select Data With PDO:
Example (PDO):
<?php
echo "<table style='border: solid 1px black;'>";
echo "<tr>
<th>Id</th>
<th>Firstname</th>
<th>Lastname</th></tr>";
class TableRows extends RecursiveIteratorIterator
{
function __construct($it)
{
parent::__construct($it, self::LEAVES_ONLY);
}
function current()
{
return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>";
}
function beginChildren()
{
echo "<tr>";
}
function endChildren()
{
echo "</tr>" . "\n";
}
}
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "mydetails";
Try
{
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("SELECT id, first_name, last_name FROM students");
$stmt->execute();
// set the resulting array to associative
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v)
{
echo $v;
}
}
catch(PDOException $e)
{
echo "Error: " . $e->getMessage();
}
$conn = null;
echo "</table>";
?>
Delete Data from a MySQL:
The DELETE command is used to delete a data/record from a table.
Syntax:
DELETE FROM table_name
WHERE column_name = value.
- column_name: from which column we want to pick a value.
- value: which value we want to delete from the selected column.
"STUDENTS" Table:
| Id |
First_name |
Last_name |
Age |
Batch |
| 1 |
Sonoo |
Jaiswal |
23 |
2015 |
| 2 |
Rahul |
Jain |
31 |
2019 |
| 3 |
Mehak |
Malik |
26 |
2014 |
Example (MySQLi Object-Oriented):
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "details";
// Creating connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Checking connection
if ($conn->connect_error)
{
die("Connection failed: " . $conn->connect_error);
}
//deleting a record
$sql = "DELETE FROM students WHERE id=3";
if ($conn->query($sql) === TRUE)
{
echo "Record deleted successfully";
}
else
{
echo "Error deleting record: " . $conn->error;
}
$conn->close();
?>
Example (MySQLi Procedural):
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "details";
// Creating connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Checking connection
if (!$conn)
{
die("Connection failed: " . mysqli_connect_error());
}
//deleting a record
$sql = "DELETE FROM students WHERE id=3";
if (mysqli_query($conn, $sql))
{
echo "Record deleted successfully";
}
else
{
echo "Error deleting record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Example(PDO):
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "mydetails";
try
{
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//deleting a record
$sql = "DELETE FROM students WHERE id=3";
// use exec() because no results are returned
$conn->exec($sql);
echo "Record deleted successfully";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
Let`s look at the "students" table after deletion of record:
| Id |
First_name |
Last_name |
Age |
Batch |
| 1 |
Sonoo |
Jaiswal |
23 |
2015 |
| 2 |
Rahul |
Jain |
31 |
2019 |
PHP Update Data in MySQL:
The UPDATE command is used to update existing records in a table.
"STUDENTS" Table:
| Id |
First_name |
Last_name |
Age |
Batch |
| 1 |
Sonoo |
Jaiswal |
23 |
2015 |
| 2 |
Rahul |
Jain |
31 |
2019 |
Example (MySQLi Object-Oriented):
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "details";
// Creating connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Checking connection
if ($conn->connect_error)
{
die("Connection failed: " . $conn->connect_error);
}
$sql = "UPDATE students SET last_name='Jain' WHERE id=2";
if ($conn->query($sql) === TRUE)
{
echo "Record updated successfully";
}
else
{
echo "Error updating record: " . $conn->error;
}
$conn->close();
?>
Example (MySQLi Procedural):
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "details";
// Creating connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Checking connection
if (!$conn)
{
die("Connection failed: " . mysqli_connect_error());
}
$sql = "UPDATE students SET last_name='Jain' WHERE id=2";
if (mysqli_query($conn, $sql))
{
echo "Record updated successfully";
}
else
{
echo "Error updating record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Example (PDO):
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "mydetails";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "UPDATE students SET last_name='Jain' WHERE id=2";
// Preparing statement
$stmt = $conn->prepare($sql);
// executing the query
$stmt->execute();
echo $stmt->rowCount() . " records UPDATED successfully";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
Let`s look at the "students" table after updated the record:
| Id |
First_name |
Last_name |
Age |
Batch |
| 1 |
Sonoo |
Jain |
23 |
2015 |
| 2 |
Rahul |
Jain |
31 |
2019 |
PHP Limit Data from MySQL:
LIMIT clause is used to define the number of records to return.
The LIMIT clause makes the code easy for multi-page results and pagination with MySQL, which is very useful on large tables. Returning a large number of records can impact on performance.
$sql = "SELECT * FROM Employee LIMIT 10";
In the above code, we have selected all the records from 1-10 from a table called "Employee".
When the above query runs, it will return the first 10 records.
