During my professional career I’ve reviewed a lot of code, and in this post I want to share one common mistake that a lot of programmers do while programming. I will concentrate here on the PHP programming language and some of the popular PHP frameworks like Yii, and Zend.

Four most frequent operations performed on a database are: select, count, insert, update and delete. Usually, programmers are very careful when executing insert, update and delete, but when it comes to select and count, there are a lot of mistakes.

Here I want to concentrate on the count operation, and the problems you might encounter when using it, and how some language constructions can be badly interpreted by beginners.

For example, let’s say that tables Department and Employee exist in a database. If you want to count all employees which belongs to Department with id=1 you will write the following query:

As result, the query above  will return integer number which is the number of rows which fulfill the where clause.

Count database rows using Zend Framework

Now some beginners who develop using Zend framework counts database rows using this construction:

Let’s first examine what will happen when the code snippet above executes:

  1. All employees from the Employee table with department_id column equal to 1 will be saved in the variable $employees.
  2. Then, number of the elements saved in the variable $employees will be saved in the variable $numberOfEmployees.
  3. If the number of employees is greater than zero, then the following text will be displayed “There are some employees working in the department 1″, otherwise the text “Nobody is working for department 1″ will be displayed.

The code above is not problematic if table Employees counts less than 100 or even 1000 rows (even in this case, this is not recommended way for counting the rows), but what if there are more than 1 000 000 or 10 000 000 users in the Department with id=1. In this case, you might end up with out of memory fatal error like this one:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 54 bytes)

Why is this happening? The answer is hidden in the first line of the code snippet above. You first retrieve all employees from the Employee table with department_id column equal to 1, and then in the second step you want to count how many objects database returned.

So in the first line the following query will be executed:

this means that you will retrieve all employees from the database, and will save them in the memory in variable $employees. This will eat your memory, and for massive tables may produce fatal errors like the one above.

If you encounter this problem, DO NOT use the following (or similar) line to resolve your issue ini_set(“meomory_limit”,”1024M”), because this may temporarily resolve the issue, but the issue may reoccur in future.

Instead, you should fix your badly written code. You can add the following method to desired class:

Code above will produce the desired SQL query:

which will return single integer instead of thousands of rows.

One should be very careful when browsing for this question on Internet. On this StackOverflow link, the first answer is is correct, but there is no note about the consequences of the provided code, and the author of the question stated that he/her is beginner, and he/her is unaware of the bad performance. There are more examples like this one available on several other websites.

Count database rows using Yii Framework

We’ve seen how to properly count objects using Zend framework. Let’s see how to perform the same task in the Yii framework. If you want to get number of workers who work for department with id=1 then you can do it using the following statement:

or if you have department id as variable then like:

Autor’s opinion is that Yii framework construction is much more intuitive and much easier when compared with the same construction in the Zend Framework.

If you are counting objects using the following code in Yii framework, then you will probably encounter some performance and memory consuption issues as your database is growing.

How to properly count database rows using Zend or Yii frameworkhttp://code-epicenter.com/wp-content/uploads/2015/02/database.jpghttp://code-epicenter.com/wp-content/uploads/2015/02/database-150x150.jpgAmir DuranPHPDatabase,PHP,Yii framework,Zend framework
During my professional career I've reviewed a lot of code, and in this post I want to share one common mistake that a lot of programmers do while programming. I will concentrate here on the PHP programming language and some of the popular PHP frameworks like Yii, and Zend. Four most...