[PHP] SQL DATABASE USING PDO

By Ayman Adil (Last source code update: 20/09/2020)

Table of contents

• Description
• SELECT clause
• WHERE clause
• GROUP BY clause
• ORDER BY clause
• AS (alias) keyword
• INSERT INTO clause
• UPDATE clause
• UNION/UNION ALL clauses
• NATURAL JOIN clause
• DELETE clause
• TRUNCATE TABLE clause
• LIMIT clause
• PDO methods

Description

The purpose of this program is to facilitate the creation of SQL queries on PHP.

First create the query with select(), insert(), update(), where(), ... methods then prepare and execute it with result() method.

SELECT clause

Select all columns

// [SQL] SELECT * FROM Users
$db->select('*', 'Users');
$result = $db->result();

Select a single column

// [SQL] SELECT first_name FROM Users
$db->select('first_name', 'Users');
$result = $db->result('fetch'); // i.e using PDOStatement::fetch

Select multiple columns

// [SQL] SELECT first_name, last_name FROM Users
$db->select(['first_name', 'last_name'], 'Users');
$result = $db->result();

WHERE clause

AND/OR OPERATORS

// [SQL] SELECT * FROM Users WHERE id > 10 AND birthdate = '2012-12-12'
$db->select('*', 'Users')->where(['id >' => 10, 'birthdate =' => '2012-12-12']);
$result = $db->result();

// [SQL] SELECT * FROM Users WHERE id > 10 OR birthdate = '2012-12-12'
$db->select('*', 'Users')->where(['id >' => 10, 'birthdate =' => '2012-12-12'], 'OR');
$result = $db->result();

IN/NOT IN OPERATOR

// [SQL] SELECT * FROM Users WHERE birthdate IN ('2011-11-11', '2012-12-12')
$db->select('*', 'Users')->where(['birthdate IN' => ['2011-11-11', '2012-12-12']]);
$result = $db->result();

BETWEEN/NOT BETWEEN OPERATOR

// [SQL] SELECT * FROM Users WHERE id BETWEEN 51 AND 100
$db->select('*', 'Users')->where(['id BETWEEN' => ['51', '100']]);
$result = $db->result();

GROUP BY clause

Group by one column

// [SQL] SELECT id, SUM('orderPrices') FROM 'Orders' GROUP BY id
$db->select(['id', 'SUM(orderPrices)'], 'Orders')->groupBy('id');
$result = $db->result();

Group by multiple columns

// [SQL] SELECT first_name, last_name, SUM('orderPrices') FROM 'Orders' GROUP BY first_name, last_name
$db->select(['first_name', 'last_name', 'SUM(orderPrices)'], 'Orders')->groupBy(['first_name', 'last_name']);
$result = $db->result();

ORDER BY clause

Order by one column

// [SQL] SELECT id, first_name FROM 'Users' ORDER BY id DESC
$db->select(['id', 'first_name'], 'Users')->orderBy('id DESC');
$result = $db->result();

Order by multiple columns

// [SQL] SELECT id, first_name, birthday FROM 'Users' ORDER BY id DESC, first_name
$db->select(['id', 'first_name'], 'Users')->orderBy(['id DESC', 'first_name']);
$result = $db->result();

AS (alias) keyword

Aliases on columns

// [SQL] SELECT id AS identity, first_name AS first_name FROM 'Users'
$db->select(['id AS identity', 'first_name AS firstName'], 'Users');
$result = $db->result();

Alias on table

// [SQL] SELECT * FROM 'Users' AS UsersInformation
$db->select('*', 'Users AS UsersInformation');
$result = $db->result();

INSERT clause

Insert only values

// [SQL] INSERT INTO Users VALUES (5, 'toto', 'moto', '10-10-2010');
$db->insertInto('Users', [5, 'toto', 'moto', '10-10-2010']);
$result = $db->result();

Insert values in only specified columns

// [SQL] INSERT INTO Users ('first_name', 'last_name') VALUES ('toto', 'moto');
$db->insertInto('Users', ['first_name', 'last_name'], ['toto', 'moto']);
$result = $db->result();

UPDATE clause

Update all rows

// [SQL] UPDATE 'Users' SET 'first_name' = 'toto';
$db->update('Users', ['first_name' => 'toto']);
$result = $db->result();

Update a specified row

// [SQL] UPDATE 'Users' SET 'first_name' = 'toto' WHERE id = 10;
$db->update('Users', ['first_name' => 'toto'])->where(['id =' => 10]);
$result = $db->result();

UNION/UNION ALL clauses

UNION clause

// [SQL] SELECT * FROM 'Orders1' UNION SELECT * FROM 'Orders2'
$db->select('*', 'Orders1')->unionSelect('*', 'Orders2');
$result = $db->result();

UNION ALL clause

// [SQL] SELECT * FROM 'Orders1' UNION ALL SELECT * FROM 'Orders2'
$db->select('*', 'Orders1')->unionAllSelect('*', 'Orders2');
$result = $db->result();

NATURAL JOIN clause

// [SQL] SELECT * FROM Users NATURAL JOIN Country
$db->select('*', 'Users')->naturalJoin('Country');
$result = $db->result();

DELETE clause

Delete all rows

// [SQL] DELETE FROM 'Users'
$db->delete('Users');
$result = $db->result();

Delete specific rows

// [SQL] DELETE FROM 'Users' WHERE id = 10
$db->delete('Users')->where(['id =' => 10]);
$result = $db->result();

TRUNCATE TABLE clause

// [SQL] TRUNCATE TABLE 'Users'
$db->truncate('Users');
$result = $db->result();

LIMIT clause

// [SQL] SELECT * FROM Users LIMIT 2
$db->select('*', 'Users')->limit(2);
$result = $db->result();

// [SQL] SELECT * FROM Users LIMIT 3, 2
$db->select('*', 'Users')->limit(3, 2);
$result = $db->result();

Get results

To prepare and execute the query that you had build, you have to use result() method.

If you want to fetch data, you have to specify the fetch method you want to use (by default, it is PDOStatement::fetchAll).

For example, if you want to use PDOStatement::fetchColumn, result() argument must be 'fetchColumn'. See another example.

PDO methods

You can also use any PDO methods.