Although, in PHP you can connect to various databases, MySQL is the most popular database used with PHP. MySQL is a Relational Database Management System (RDMS) which is very fast and easy to use for developing web based applications. It was developed by Michael Widenius and David Axmark in 1994 and now is widely used in small as well as in big organisations MySQL is freely available to download and can be compiled on multiple platforms.
MySQL Database uses tables in the form of rows and columns for storing information. You can query a database for fetching some specific information. The newer versions of MySQL uses:
- MySQLi where i stands for Improved.
- PDO (PHP Data Objects)
Both the above methods are Object Oriented but MySQLi also offers a Procedural API. Also they support prepared statements which is further useful in protecting data from SQL injections. However, MySql extension has been deprecated since 2012.
We shall be discussing all MySQL Query Examples in both MySQLi and PDO Format.
Opening & Closing db connection
1. MySQLi Procedural
|
<?php
$server = "localhost";
$username = "root";
$password = "root";
$database = "mydb";
// Creating connection
$conn = mysqli_connect($server, $username, $password, $database);
if (!$conn) {
die(mysqli_connect_error());
}
echo "Server Connection Successful";
?>
|
To close connection, use:
mysqli_close($conn);
|
2. MySQLi Object Oriented
|
<?php
$server = "localhost";
$username = "user";
$password = "pwd";
$database = "mydb";
// Creating connection
$conn = new mysqli($server, $username, $password, $database);
if ($conn->connect_error) {
die($conn->connect_error);
}
echo "Server Connection Successful";
?>
|
To close connection, use:
$conn->close();
|
3. PDO
|
<?php
$server = "localhost";
$username = "user";
$password = "pwd";
try {
$conn = new PDO("mysql:host=$server; dbname=mydatabase", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Server Connection Successful";
}catch(PDOException $ex) {
echo $ex->getMessage();
}
?>
|
To close connection, use:
$conn = null;
|
Creating Database
//syntax
CREATE DATABASE database_name;
1. MySQLi Procedural
|
<?php
$sql = "CREATE DATABASE mydatabase";
if (mysqli_query($conn, $sql)) {
echo "Database created";
}else{
echo mysqli_error($conn);
}
?>
|
- Uses
mysqli_query() function is used to execute query.
- $conn and
$sql are the two parameters which are passed to the corresponding function.
|
2. MySQLi Object Oriented
|
<?php
$sql = "CREATE DATABASE mydatabase";
if ($conn->query($sql) === TRUE) {
echo "Database created";
}else{
echo $conn->error;
}
?>
|
- Uses
$conn->query() to execute a query.
$sql is passed as a parameter to the corresponding function
|
3. PDO
|
?php
try {
$sql = "CREATE DATABASE mydatabase";
$conn->exec($sql);
echo "Database created";
}catch(PDOException $e) {
echo $e->getMessage();
}
?>
|
- Uses
$conn->exec() to execute a query.
$sql is passed as a parameter to the corresponding function.- If an exception is thrown in a try block, the control flows directly to the catch block.
- In the catch block, an error message is generated and echoed.
|
Creating Table
A table with name ‘employee_details’ will be created and having following column names:
employee_id: will be the primary key of the table i.e. its value will be unique for every record.
firstname, lastname, email, department_id, join_date
1. MySQLi Procedural
|
<?php
$sql = "CREATE TABLE ‘employee_details’ (
employee_id INT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(50) NOT NULL,
lastname VARCHAR(50) NOT NULL,
email VARCHAR(50),
department_id INT(8),
join_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";
if(mysqli_query($conn, $sql)) {
echo "employee_details created successfully.";
}else{
echo mysqli_error($conn);
}
?>
|
2. MySQLi Object Oriented
|
<?php
$sql = "CREATE TABLE ‘employee_details’ (
employee_id INT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(50) NOT NULL,
lastname VARCHAR(50) NOT NULL,
email VARCHAR(50),
department_id INT(8),
join_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";
if ($conn->query($sql) === TRUE) {
echo "employee_details created successfully.";
}else{
echo $conn->error;
}
?>
|
3. PDO
|
<?php
try{
$sql = "CREATE TABLE ‘employee_details’ (
employee_id INT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(50) NOT NULL,
lastname VARCHAR(50) NOT NULL,
email VARCHAR(50),
department_id INT(8),
join_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";
$conn->exec($sql);
echo "employee_details created successfully.";
}catch(PDOException $ex) {
echo $ex->getMessage();
}
?>
|
Insert Data into Table
INSERT INTO statement is used to insert data into MySQL tables. In order to add records to the table you must:
Quote all the string values and no need to quote numeric and NULL values.
AUTO_INCREMENT and TIMESTAMP is not supposed to be mentioned while writing a query.
//syntax
INSERT INTO tablename(column1, column2, … columnn)
VALUES(value1, value2, … valuen);
1. Insert data using MySQLi Procedural
|
<?php
$sql = "INSERT INTO employee_details(firstname, lastname, email, department_id)
VALUES ('Chris', 'Paul' , '[email protected]', 26)";
if (mysqli_query($conn, $sql)) {
echo "Record Inserted";
}else{
echo mysqli_error($conn);
}
?>
|
2. Insert data MySQLi Object Oriented
|
<?php
$sql = "INSERT INTO employee_details(firstname, lastname, email, department_id)
VALUES ('Chris', 'Paul' , '[email protected]', 26)";
if ($conn->query($sql) === TRUE) {
echo "Record Inserted";
}else{
echo $conn->error;
}
?>
|
3. Insert data using PDO
|
<?php
try{
$sql = "INSERT INTO employee_details(firstname, lastname, email, department_id)
VALUES ('Chris', 'Paul' , '[email protected]', 26)";
$conn->exec($sql);
echo "Record Inserted";
}catch(PDOException $ex) {
echo $ex->getMessage();
}
?>
|
Get last inserted ID
You can also fetch the id of the last inserted record immediately after insertion.
1. Fetch last inserted id using MySQLi Procedural
|
<?php
$sql = "INSERT INTO employee_details(firstname, lastname, email, department_id)
VALUES ('Chris', 'Paul' , '[email protected]', 26)";
if (mysqli_query($conn, $sql)) {
$insert_id = mysqli_insert_id($conn);
echo $insert_id. " Record Inserted";
}else{
echo mysqli_error($conn);
}
?>
|
2. Fetch last inserted id using MySQLi Object Oriented
|
<?php
$sql = "INSERT INTO employee_details(firstname, lastname, email, department_id)
VALUES ('Chris', 'Paul' , '[email protected]', 26)";
if ($conn->query($sql) === TRUE) {
$insert_id = $conn->insert_id;
echo $insert_id. " Record Inserted";
}else{
echo $conn->error;
}
?>
|
3. Fetch last inserted id using PDO
|
<?php
try{
$sql = "INSERT INTO employee_details(firstname, lastname, email, department_id)
VALUES ('Chris', 'Paul' , '[email protected]', 26)";
$conn->exec($sql);
$insert_id = $conn->lastInsertId();
echo $insert_id." Record Inserted";
}catch(PDOException $ex) {
echo $ex->getMessage();
}
?>
|
Retrieve Data
SELECT statement is used to retrieve data from one or more tables.
// syntax 1
SELECT column1, column2, column3 FROM tablename;
or
// syntax 2
SELECT * FROM tablename;
Note: ‘*’ is used to fetch all columns.
1. Retrieve using MySQLi Procedural
|
<?php
$sql = "SELECT employee_id, firstname, lastname
FROM employee_details";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
echo "Employee Id: " . $row["employee_id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
}else{
echo "No record found";
}
?>
|
2. Retrieve Object Oriented
|
<?php
$sql = "SELECT employee_id, firstname, lastname
FROM employee_details";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "Employee Id: " . $row["employee_id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
}else{
echo "No record found";
}
?>
|
3. Retrieve using PDO
|
<?php
try{
$sql = "SELECT employee_id, firstname, lastname
FROM employee_details";
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
foreach($result as $row){
echo "Employee Id: " . $row["employee_id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
}catch(PDOException $ex) {
echo $ex->getMessage();
}
?>
|
MySQL clauses
Clauses are used along with queries to specify conditions. Most commonly used clauses are as listed below:
- WHERE Clause
- HAVING Clause
- ORDER BY Clause
- LIKE Clause
- GROUP BY Clause
WHERE Clause
WHERE Clause is used along with SELECT Statement in order to filter out the results. One or more conditions can be specified with this clause using the following operators.
SELECT field1, field2, …., fieldn
FROM table1, table2, ..., table
WHERE condition1 [AND [OR]] condition2….. condition
WHERE Clause is also used with an UPDATE and DELETE statement to specify which record is supposed to update or delete accordingly.
<?php
$sql = "SELECT employee_id, firstname, lastname
FROM employee_details
WHERE department_id = 26 AND join_date > ‘2020-01-01’";
?>
GROUP BY clause
GROUP BY Clause is used to arrange the rows of a table in a group based on specific conditions. It is most commonly used with sum, average, min or max functions.
// syntax
SELECT column1, column2, …. columnn
FROM table
WHERE conditions
GROUP BY column1, ... , columnn
To check for the number of employees working in each department, the query will be as follow:
<?php
SELECT COUNT(employee_id), department_id
FROM employee_details
GROUP BY department_id;
?>
ORDER BY clause
ORDER BY Clause is used to sort the results in ascending or descending order.
// syntax
SELECT column1, column2, … , columnn
FROM tablename
ORDER BY column1, column2, …, columnn ASC|DESC;
To order the employee list in ascending order according to Employee Name.
<?php
SELECT * FROM employee_details ORDER BY firstname;
?>
HAVING clasue
HAVING clause is used to filter the results formed by GROUP Clause.
// syntax
SELECT column1, column2, …., columnn
FROM tablename
WHERE condition(s)
GROUP BY column(s)
HAVING condition;
// Code Example
<?php
SELECT COUNT(employee_id), department_id
FROM employee_details
GROUP BY department_id
HAVING employee_id >100;
?>
Update data
UPDATE statement is used to update the existing records in tables.
// syntax
UPDATE tablename
SET column1 = value1, column2 = value2,..., column = valuen;
Note: ‘*’ is used to fetch all columns.
1. Updating using MySQLi Procedural
|
<?php
$sql = "UPDATE employee_details
SET department_id = '25'
WHERE employee_id = 4";
if (mysqli_query($conn, $sql)) {
echo "Record updated successfully";
}else{
echo mysqli_error($conn);
}
?>
|
2. Updating using Object Oriented
|
<?php
$sql = "UPDATE employee_details
SET department_id = '25'
WHERE employee_id = 4";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
}else{
echo $conn->error;
}
?>
|
3. Updating using PDO
|
<?php
try{
$sql = "UPDATE employee_details
SET department_id = '25'
WHERE employee_id = 4";
$stmt = $conn->prepare($sql);
$stmt->execute();
}catch(PDOException $ex) {
echo $ex->getMessage();
}
?>
|
Update data
DELETE statement is used to delete records from a table.
// syntax
DELETE FROM tablename
WHERE condition;
1. Delete using MySQLi Procedural
|
<?php
$sql = "UPDATE employee_details
SET department_id = '25'
WHERE employee_id = 4";
if (mysqli_query($conn, $sql)) {
echo "Record updated successfully";
}else{
echo mysqli_error($conn);
}
?><?php
$sql = "DELETE FROM employee_details
WHERE employee_id = 4";
if (mysqli_query($conn, $sql)) {
echo "Record deleted successfully";
}else{
echo mysqli_error($conn);
}
?>
|
2. Delete using Object Oriented
|
<?php
$sql = "DELETE FROM employee_details
WHERE employee_id = 4";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
}else{
echo $conn->error;
}
?>
|
3. Delete using PDO
|
<?php
try{
$sql = "DELETE FROM employee_details
WHERE employee_id = 4";
$conn->exec($sql);
}catch(PDOException $ex) {
echo $ex->getMessage();
}
?>
|
