Ten steps to writing SQL queries with the Zend framework

lessonsphpprogrammingtutorialtutorialzend framework

Written by:

Reading Time: 5 minutes

In every project, it gets to a point where you have to communicate with a table in order to retrieve data. Here is how to do it in the Zend framework. Getting started with queries in Zend framework is not so scary. I hope to give you a very soft introduction to make what may seem difficult look ridiculously simple.

You can do this in ten steps. For this post we will focus on the simplest of queries, the select statements. We will create the database in a MySQL DBMS.

Here are the ten steps we will take;

 

Contents

  1. Create a sample table and database: 1
  2. Know how creating projects in the Zend framework is done: 1
  3. Visualize the output and write raw SQL queries: 1
  4. Import the database module: 2
  5. Define the database connection: 2
  6. Create the model and the model table: 3
  7. Call the required classes within the module file: 3
  8. Import ( Use ) the model tables in the in the controller file: 3
  9. Write your SQL in the Zend framework ORM: 4
  10. How do we display the output?. 5

 

 

1. Create a sample table and database:

To retrieve data we must first have a database table from which to retrieve data from. In this demo, we will retrieve details from a people table. Use the .sql file in sql folder on this GitHub repo to create the people table.

Another, and better, way would have been to use database migrations. However we talk all about migrations in a later blog post. For today however, simply download the .sql file and run it in a schema you create.

2. Know how creating projects in the Zend framework is done:

At this stage, we should be working on the Zend framework side of things.

  • How do we connect to the database?
  • How can we display the results?

No need to sweat it. It is all possible within the Zend framework. I have already spent an entire blog post on the subject of creating the Zend framework project.

3. Visualize the output and write raw SQL queries:

  • What should the output be like?
  • What results would you want to see?
  • What results have you visualized?

Once you know the output you expect to see, you can get closer to making that a reality. You should be comfortable with writing SQL statements. Writing SQL is useful to you and is used in several fields. With our example, our select statement in raw sql would look like this

Example 1:

Select * from people

Example 2:

Select * from people where first_name = ‘Anthony’ and last_name=’Agbenu’;

Run or execute this statement in your editor. After you run or execute this query, does it return what you expected? If so, then that is good. If it did not, then keep re-writing your query and continuing to execute your select query until it returns the results you wanted.

4.  Import the database module:

The database module, specifically the”Zend\Db” makes it possible for you to communicate with the database. You can find that it has been done at the path  /config/modules.config.php.

Just to let you know, I chose to write my connection to the database through a gateway.

5. Define the database connection:

We define the connection to the database in the file config/autoload/global.php.

The connection might be defined like so;

 

return [

‘db’ => array(

‘driver’ => ‘PdoMysql’,

‘dsn’            => ‘mysql:dbname=demo;host=localhost;charset=utf8’,

‘username’       => ‘root’,

‘password’       => ”,

 

),

];

 

 

6.  Create the model and the model table:

Every table in Zend can have a model via which you use to speak to the actual tables at the database level. The model tables go hand in hand in making this communication easier. We will go ahead and create the model and model tables at the default Module (that is the Application module). The model and model table we created was the People and PeopleTable classes respectively.

7.  Call the required classes within the module file:

If you would want specific models and model tables to be seen in the module, make sure these are already initialized in the module file.

You can find both the service and controller configurations at /module/Application/src/Module.php.

8. Import (Use) the model tables in the in the controller file:

The controller acts as the brain of the app. If the brain does not know of the existence of a model and the model tables, then it is undefined. In order to import those classes, use the keyword use. It would look just like below;

use Application\Model\PeopleTable;

We will create action within the Index controller (IndexController) of the Application module. I have two actions for our examples. These are the example one and example two actions. Read through the example project to for the skeletal approach in which those actions were written.

9. Write your SQL in the Zend framework ORM:

Let us know speak to Zend framework to return an output. We do this through the ORM. Go on to read on the documentation on the Zend framework ORM.  All the basics you need are in there. You do not need to know all of it, just something to get started with.

However to write our examples in Zend framework three( zf-3 ), it is as simple as

Example 1:

In the controller action;

$this->peopleTable->fetchAll();

In the model table;

public function fetchAll()

{

$select = $this->tableGateway->getSql()->select();

$resultSet = $this->tableGateway->select(function ($select) {

$select->order(‘id DESC’);

});

return $resultSet;

 

}

Example 2:

In the controller action;

$this->peopleTable->fetchSpecificUser(“Anthony”,”Agbenu”);

In the model table;

public function  fetchSpecificUser($first_name,$last_name){

return $this->tableGateway->select([‘first_name’=>$first_name, ‘last_name’=>$last_name ]);

}

10. How do we display the output?

Displaying the results of the query is the crowning moment. It is where we get to see the results from the database table. Create a view file where the output would be displayed. The view names follow the names for the actions.

  • Since we have the action exampleOneAction, then we have the view file being saved as example-one.phtml
  • Since we have the action exampleTwoAction, then we have the view file being saved as example-two.phtml

Similarly the URL via which to view the results would be as follows;

  • Since we have the action exampleOneAction, then we have the http://127.0.0.1/test-zf/public/application/exampleOne/
  • Since we have the action exampleTwoAction, then we have the http://127.0.0.1/test-zf/public/application/exampleTwo/

The URLs are constructed at the module config file. This approach is the same as in most other frameworks. For example, in python it is the urls file.

If you happen to have an error that seems to suggest your route cannot be found it may be because you forgot to uncomment these lines in   /test-zf/module/Application/config/module.config.php.

/*

‘controllers’ => [

‘factories’ => [

Controller\IndexController::class => InvokableFactory::class,

],

],*/

 

And that is it! Those are the simple ten steps you need to start you on the road to writing Zend framework SQL queries.

I will write another post soon on the Zend framework. In the meantime, make sure to keep reading, and sharing.

Best regards.

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll Up