How To Build SELECT Queries

As we have discussed in the Understanding PHPJabbers.com Framework, our products are built using the MVC model and the PHP code is object oriented (OOP). 

 

To make it easier to construct different SQL queries we’ve made a special PHP class which handles MySQL. To manipulate MySQL tables you need to create an object and then using this class to manipulate the data by calling different predefined functions.

 

To illustrate how easy it is to use our framework to build SQL queries lets compare a basic PHP code to execute an SQL query and how the same can be done using our framework. Assume that you have a table Users.

 

CREATE TABLE `users` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`role_id` int(10) unsigned NOT NULL,

`email` varchar(255) DEFAULT NULL,

`password`varchar(255) DEFAULT NULL, 

`name` varchar(255) DEFAULT NULL,

`phone` varchar(255) DEFAULT NULL,

`department` varchar(255) DEFAULT NULL,

`created` datetime NOT NULL,

`last_login` datetime DEFAULT NULL,

`status` enum('T','F') NOT NULL DEFAULT 'T',

`is_active` enum('T','F') NOT NULL DEFAULT 'F',

`ip` varchar(15) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `email` (`email`),

KEY `role_id` (`role_id`),

KEY `status` (`status`)

);

 

Let’s say you want to select all the users from that table and have the data stored in an array. Using a plain PHP code you can do it like this

$sql = 'SELECT * FROM users';

$sql_result = mysql_query ($sql, $connection );

while ($data = mysql_fetch_assoc($sql_result)) {

$users[] = $data;

}

 

You first construct SELECT SQL query, then you execute it, then you have a “while” cycle which reads all the data and generates $users array. With our framework this can be done with a lot less code.

$users = $pjUserModel->findAll()->getData();

 

As you can see there is no need to write SQL queries. To construct a query you need the following components

- object - this an object which you want to work with  - $pjUserModel

- SQL parameters such as where, limit, order, etc.. 

- SQL command generation function - findAll()

- Data receiver - getData()

 

model name - or in any words the data you need to manipulate. Depending on the product that you use this could be users, reservations, comments, prices, discounts, images, etc… Each of our products has pjUserModel so below we will use it for all of the example queries

 

So now lets look at some real SELECT examples which manipulate mysql users table via the pjUserModel model:

I. SELECT queries

As we we said above we can split the process of running a mysql SELECT query using our query builder in 4 steps:

1. Creating an object

There are 2 ways to do that:

1.1. Using factory method

$pjUserModel = pjUserModel::factory();

 

1.2. Using constructor

$pjUserModel = new pjUserModel();

 

Both methods do the same - create a new object. However:

- using the static "factory" method allows you to run a query with a single line of code

- using constructor will store the object into a variable and then you can reuse the object for other queries

 

Sample of using the factory() method (1.1) to get all users looks like this:

$users =pjUserModel::factory()->findAll()->getData();

** SELECT * FROM `users` 

 

Another sample of how to get number of users using just the factory() method

$count =pjUserModel::factory()->findCount()->getData();

** SELECT COUNT(*) FROM `users` 

 

As you can see for both queries above we call pjUserModel::factory() first to initiate the database connection to this table and get the data from it. 

 

Now the same two samples, but when we create object, which can be used in both cases to execute the different queries and return the same result.

 

First we create our object from the class we need:

$pjUserModel = new pjUserModel();

$data = $pjUserModel->findAll()->getData();

** SELECT * FROM `users`

 

And we call the same object with the other method that returns number of records

$count = $pjUserModel->findCount()->getData();

** SELECT COUNT(*) FROM `users`

 

2. Building queries

In this section we will take a look at the different parameters that can be passed to our SQL query builder to get you the results that you need.

2.1. select

This method is used to define if you want only specific columns from the table to be returned and not all. For example if you want to select only the names, emails and phone numbers of the users

$users = $pjUserModel->select("name, email, phone")->findAll()->getData(); 

** SELECT name, email, phone FROM `users`

 

Skipping the ‘select’ method will select all table columns (equal to SELECT *)

 

You can also use ‘select’ to write subqueries. For example to get only the date when user has been created

$users = $pjUserModel->select("t1.*, DATE_FORMAT(t1.created, '%Y-%m-%d') AS `iso_date`")->findAll()->getData(); 

** SELECT t1.*, DATE_FORMAT(t1.created, '%Y-%m-%d') AS `iso_date` FROM eventbooking_users AS t1

 

2.2. distinct

Sets a flag which tells the query string compiler to add DISTINCT. Let’s say you want to get all users  departments, because you want to make a filter by them. 

$departments = $pjUserModel->select("department")->distinct(true)->findAll()->getData(); 

** SELECT DISTINCT t1.department FROM eventbooking_users AS t1

 

2.3. from

It allows you to change the FROM table. There is no need to use it in your queries at the moment, but we have added this as option for future compatibility. We mention it here so that our documentation if full. You can use this only if you want to use table that are not binded to the model, but this also required that you adjust the field names. Generally you will just use another object to execute this query. 

$roles = $pjUserModel->select("role")->from(pjRoleModel::factory()->getTable())->findAll()->getData(); 

** SELECT t1.role FROM eventbooking_roles AS t1

 

2.4. join

Join another table. There are 3 parameters that join method accepts

First we define the joined table model, then we pass the rule by which we will join the two tables and  third parameter is the join type. All JOIN types supported by MySQL can be used

$user_roles = $pjUserModel->select("t1.name, t2.role")->join('pjRole', 't2.id=t1.role_id', 'left outer')->findAll()->getData();

** SELECT t1.name, t2.role FROM eventbooking_users AS t1 LEFT OUTER JOIN eventbooking_roles AS t2 ON t2.id=t1.role_id

 

2.5. where, whereIn, whereNotIn, orWhere, orWhereIn, orWhereNotIn

Lots of options are supported when we build queries using the WHERE clause

We will only provide different samples and the actual SQL query executed without additional details

 

2.5.1 // Value for second parameter is automatically escaped and concatenated with ‘=’ if other operator is not specified

$users = $pjUserModel->where(‘t1.role_id’, 2)->findAll()->getData(); 

** SELECT t1.* FROM eventbooking_users AS t1 WHERE  t1.role_id = 2

 

2.5.2 // Custom operator

$users = $pjUserModel->where('t1.role_id != 2')->findAll()->getData(); 

** SELECT t1.* FROM eventbooking_users AS t1 WHERE  t1.role_id != 2

 

2.5.3 // Without table alias 

$users = $pjUserModel->where('role_id', 2)->findAll()->getData(); 

** SELECT t1.* FROM eventbooking_users AS t1 WHERE role_id = 2

 

2.5.4 // Using IN operator

$users = $pjUserModel->whereIn('t1.role_id', array(1,2))->findAll()->getData(); 

** SELECT * FROM eventbooking_users AS t1 WHERE t1.role_id IN (1, 2)

 

2.5.5 // Results NOT IN set

$users = $pjUserModel->whereNotIn('t1.role_id', array(1,3))->findAll()->getData(); 

** SELECT t1.* FROM eventbooking_users AS t1 WHERE t1.role_id NOT IN (1, 3)

 

2.5.6 // Additional WHERE clause with OR (concatenate previous condition with OR instead with AND which is by default)

$users = $pjUserModel->whereNotIn('t1.role_id', array(1,3))->orWhere('t1.status', 'F')->findAll()->getData(); 

** SELECT t1.* FROM eventbooking_users AS t1 WHERE t1.role_id NOT IN (1, 3) OR t1.status = 'F'

 

2.5.7 // Additional WHERE clause with OR (concatenate previous condition with OR instead with AND which is by default and search IN set)

$users = $pjUserModel->whereNotIn('t1.role_id', array(1,3))->orWhereIn('t1.department, array('IT','Finance'))->findAll()->getData(); 

** SELECT t1.* FROM eventbooking_users AS t1 WHERE  t1.role_id NOT IN (1, 3) 

OR t1.department IN ('IT', 'Finance')

 

2.5.8 // Additional WHERE clause with OR (concatenate previous condition with OR instead with AND which is by default and search for values NOT IN set)

$users = $pjUserModel->whereNotIn('t1.role_id', array(1,3))->orWhereNotIn('t1.department, array('IT','Finance'))->findAll()->getData(); 

** SELECT t1.* FROM eventbooking_users AS t1 WHERE  t1.role_id NOT IN (1, 3) 

OR t1.department NOT IN ('IT', 'Finance')

 

2.6. groupBy

Group results by value. This parameter sets the GROUP BY value. Let’s see how to get the number of users per department

$users =  $pjUserModel->select('department,COUNT(*)')->groupBy('t1.department')->findAll()->getData();  

** SELECT department, COUNT(*) FROM eventbooking_users AS t1 GROUP BY t1.department

 

2.7. having

Using “having” in SQL query. This is a bit complex example, and refers to another table, but lets assume we have table bookings where each user bookings are saved and we want to get all users that have made at least one booking. 

$user_bookings = $pjUserModel

->select(sprintf("t1.*, (SELECT COUNT(*) FROM `%s` WHERE user_id=t1.id) AS `bookings`", pjBookingModel::factory()->getTable()))

->having('bookings > 0')

->findAll()->getData(); 

** SELECT t1.*, (SELECT COUNT(*) FROM `eventbooking_bookings` WHERE user_id=t1.id) AS `bookings` FROM eventbooking_users AS t1 HAVING bookings > 0

 

2.8. orderBy

Order results by some column. Let’s order all users by name:

$users = $pjUserModel->orderBy(‘t1.name ASC’)->findAll()->getData(); 

** SELECT t1.* FROM eventbooking_users AS t1 ORDER BY t1.name ASC

 

2.9. limit

Limit the number of results returned by the query

 

2.9.1 Get the first 5 users ordered by name

$users =$pjUserModel->orderBy('t1.name ASC')->limit(5)->findAll()->getData();

** SELECT t1.* FROM eventbooking_users AS t1 ORDER BY t1.name ASC LIMIT 0, 5

 

2.9.1. Get 5 users from the ordered by name table starting from 10

$users =$pjUserModel->orderBy('t1.name ASC')->limit(5,10)->findAll()->getData();

** SELECT t1.* FROM eventbooking_users AS t1 ORDER BY t1.name ASC LIMIT 10, 5

 

2.10. offset

Get 10 rows after the first 10

$users =$pjUserModel->limit(10)->offset(10)->findAll()->getData();

** SELECT t1.* FROM eventbooking_users AS t1 ORDER BY t1.name ASC LIMIT 10, 10

 

3. Run query

In this section we will review the possible ways to get results from a query

3.1. find

This method will return only one result and is used to get data from only one row. For example lets get all details for user with ID 3. This query without additional parameters  searches by the primary key of the table. 

$user_details = $pjUserModel->find(3);

** SELECT t1.* FROM eventbooking_users AS t1 WHERE  t1.id = 3 LIMIT 0, 1

 

3.2. findAll

This method is used to get array with data. When you expect that multiple rows will be returned you can use this method

$all_users_details = $pjUserModel->findAll();

** SELECT t1.* FROM eventbooking_users AS t1

 

3.3. findCount

If you want to find out the number of rows in a result set you can use this method

$count_users = $pjUserModel->findCount(); 

** SELECT COUNT(*) AS `cnt` FROM `eventbooking_users` AS t1 LIMIT 1

 

4. Get the result

Here we will define the set of functions that are used to get the data from the result set. Please note that the SQL query is not affected by the data method you use. The query returns all the data from the database (of course this is according to the “3. Run query” method used - see above) and then manipulates it in the PHP itself to return the result we need. 

4.1. getData

Get all data from the result set and add it to array

This sample will return a single record by primary key 

$user_details = $pjUserModel->find(3)->getData();

This sample will result in all data from table Users to be added to $all_users_details 

$all_users_details = $pjUserModel->findAll()->getData(); 

 

4.2. getDataPair

This is used when we want to return custom defined array with the data we need. The first parameter of this function is used for the array key and the second parameter is the array value

 

4.2.1 This sample will return only the data from column ‘id’ to the array.

The fierst parameter si specified as NULL, which means we do not want to have key for the array and it will be automatically set to 0,1,2...

$data = pjUserModel::factory()->findAll()->getDataPair(NULL, ‘id’);

 

4.2.2 What we will have in $data as a result from this is `id` from the database set as key of the array and `status` as value for the array. With real example it will look like this: array(1 => ‘T’, 2 => ‘T’, 3 => ‘F’....)

$data = pjUserModel::factory()->findAll()->getDataPair(‘id’, ‘status’);

 

4.3. getDataIndex

This is used when you want to get only a single record from the result data. This is an array, so its index starts from 0. The example bellow will return the third element of the array if such exists

$data = pjUserModel::factory()->findAll()->getDataIndex(2); 

 

4.4. getDataSlice

Get only a set from the result data. Thare are different options supported by this method

4.4.1 Returns all the records after the 4th record

$data = pjUserModel::factory()->findAll()->getDataSlice(3);

 

4.4.2 Returns all the records as sequence will start at 4th record, and will return up to 2 records i.e. if there are more thatn two records after the fourth they will be returned, otherwise it will return only one or null

$data = pjUserModel::factory()->findAll()->getDataSlice(3, 2);

 

4.4.3 Returns all the records as sequence will start at 4th record, up to 2 records, and will also preserve the keys of the array

$data = pjUserModel::factory()->findAll()->getDataSlice(4, 2, true);

 

4.5. getResult

Get mysql result resource

 

5. Testing

In case you experience any difficulties trying to adjust the SQL query and return the results you need using our framework we have added one method that will help you to track the result of your code.

5.1. debug

This method accepts one parameter: true/false By the default it is see to false and there is no output for the SQL queries, but if you want to enable it, all you have to do is call it before the “#3 Run query” method. 

$all_users_roles_details = $pjRoleModel->debug(true)->findAll()->getData(); 

 

This will print the SQL query that you try to execute in your browser. Here is the sample output of the above:

SELECT t1.id, t1.role, t1.status FROM eventbooking_roles AS t1

 

You will notice that for every table, all columns that will be returned are listed one after another.This is how it actually works, so for all samples above where you have ** SELECT t1.*  this is actually a list with all columns. This is only for your information and does not have effect on the result sets. 

 

At the end we have provided a full featured example of all we have explained above

It includes: subquery, join, group, sort, pagination, multiple conditions

$data = pjUserModel::factory()

->select('t1.id, t1.email, t1.name, t2.role, (SELECT COUNT(*) FROM `bookings` WHERE `user_id` = `t1`.`id` LIMIT 1) AS `cnt`')

->join('pjRole', 't2.id=t1.role_id', 'left outer')

->where('t1.id > 0')

->where('t1.status', 'T')

->groupBy('t1.id')

->having('cnt > 5')

->orderBy('t1.name ASC')

->limit(20, 10)

->findAll()

->getData();

 

** SELECT t1.id, t1.email, t1.name, t2.role, (SELECT COUNT(*) FROM `bookings` WHERE `user_id` = `t1`.`id` LIMIT 1) AS `cnt` 

FROM eventbooking_users AS t1

LEFT OUTER JOIN eventbooking_roles AS t2 ON t2.id=t1.role_id

WHERE  t1.id > 0 

AND t1.status = 'T'

GROUP BY t1.id

HAVING cnt > 5

ORDER BY t1.name ASC

LIMIT 10, 20

 

I hope that you will find this article helpful and easy to use. With this framework we have tried to make the development process easier for our developers and we hope that it will be easy for you too.

 

Happy coding :)