How to write database queries in helper in CodeIgniter 4

Question

I am using CodeIgniter 4. First I write this to get records from the database, but this shows me an error ( Call to a member function table() on null)

$CI = & get_instance();
$CI -> db -> select('*');
$CI -> db -> from($table_name);

Then I read from documentation and write this

$db->table("tablename");

But this method also failed.

Answer 1

The Query Builder is loaded through the table() method on the database connection. This sets the FROM portion of the query for you and returns a new instance of the Query Builder class:

$db      = \Config\Database::connect();
$builder = $db->table('users');
//loading query builder

$output = $builder->table('table_name')
        ->get();
// Produces: SELECT * FROM table_name

To get result as array you can add one more line of code.

$output = $output->getResultArray();

For selecting particular fileds.

$db = \Config\Database::connect();
$builder = $db->table('users');
//loading query builder

$output = $builder->table('table_name')
        ->select('filedname2, fieldname2, fieldname3,..')
        ->get();
$output = $output->getResultArray();

You can use where clause also for more details refer to codeigniter4 documentation page. https://codeigniter4.github.io/userguide/database/query_builder.html#looking-for-specific-data