Database queries with bindings and Query Builder essentials in CodeIgniter 4

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 


Author, Copyright and citation

Author

Sylwester Wojnowski

Author of the this article - Sylwester Wojnowski - is a sWWW web developer. He has been writing computer code for the websites and web applications since 1998.

Copyrights

©Copyright, 2022 Sylwester Wojnowski. This article may not be reproduced or published as a whole or in parts without permission from the author. If you share it, please give author credit and do not remove embedded links.

Computer code, if present in the article, is excluded from the above and licensed under GPLv3.

Citation

Cite this article as:

Wojnowski, Sylwester. "Database queries with bindings and Query Builder essentials in CodeIgniter 4." From sWWW - Code For The Web . https://wojnowski.net.pl//main/index/database-queries-with-bindings-and-query-builder-essentials-in-codeigniter-4

Add Comment

Allowed BB Code - style tags: [b][/b], [i][/i], [code=text][/code],[code=javascript][/code],[code=php][/code],[code=bash][/code],[code=css][/code],[code=html][/code]


I constent to processing my data given through this form for purposes of a reply by the administrator of this website.

Recent Comments

Nobody has commented on this post yet. Be first!