Database queries with bindings and Query Builder essentials in CodeIgniter 4

Created:23 Jan 2022 01:25:58 , in  Web development

CodeIgniter 4 offers a good range of tools for fetching data from database. There are simple queries, queries with bindings and a very good Query Builder class. I do not use simple queries, so leave them aside, and focus on the later two. A couple of (almost) self-explanatory pieces of code in this article should get you up and running in a matter of minutes.

Database queries with bindings

Firstly, let's look at a database query which uses bindings. Imagine you have a model called Post and a method, which looks like one below in it:

public function getPostByName($postName){
  $sql = 'SELECT * FROM `posts` WHERE status = "1" AND type = "product" AND name = ?';
  return $this -> db -> query($sql, [$postName]) -> getResult();
}

What happens here is, you write a standard SQL query with all the variables replaced with ? sign, then pass the SQL to the query() as the first argument. The second argument is an array of variables, which should be used to replace ? signs in the SQL query. To get the result you call getResult(). Very simple and very secure.

Query Builder class

Now a short example, which uses Query Builder class methods.

If you have a method like the one below in your Post model class, and want to fetch a single post, which has id greater than $currentPostId from your posts database table, you can achieve it as follows:

public function nextPost($currentPostId){
  return $this -> where(['id >' => $currentPostId,'type' => 'post']) -> first();
} 

You specify the where part of your SQL query with where(), then call first() and you are done. If you need more items, just replace first with findAll(). Follow this simple pattern and more complicated queries should become intuitive quickly.

Now, if you really need that, Query Builder queries can be constructed and run from your controller, just make your model available there, and replace $this with the variable which points to your model, i.e. $postModel:

$postModel -> where(['id' => $id]) -> first();

Code igniter makes working with database a very pleasant and, if you follow techniques above, secure experience. I'd very much encourage you to look closely at Query Builder Class section in documentation to get a better taste how good the class is. Also see Running Queries section, you will find more info on queries with bindings there. Have fun.

This post was updated on 23 Jan 2022 01:43:54

Tags:  CodeIgniter ,  mysql ,  php