CI4 $this-db->Table() inside foreach or outside

I have the following code:

PHP Code:

foreach($sales_taxes as $line => $sales_tax)
{
    $builder = $this->db->Table('sales_taxes');
    $builder->insert($sales_tax);
} 


This will reassign the $builder in each iteration of the loop. Is that needed? I don't fully understand how and when the $builder is reset. If I were to call the below code instead would it still run an insert in each iteration?

PHP Code:

$builder = $this->db->Table('sales_taxes');

foreach($sales_taxes as $line => $sales_tax)
{
    $builder->insert($sales_tax);
} 


Second variant is right

@manager, a quick followup. Am I correct in my understanding then that the $builder does not need to be reinstantiated unless the table that the operation is being performed against is being changed?

For example the following code doesn't need a new `$builder = $this->db->table();` reference because the delete() is being run against the same attribute_links table?

PHP Code:

        $builder = $this->db->table('attribute_links');
        $builder->select('definition_type');
        $builder->where('definition_id', $definition_id);

        $definition = $builder->get('attribute_definitions')->getRow();

        if($definition->definition_type != DROPDOWN)
        {
            $this->db->transStart();

            $builder->where('item_id');
            $builder->where('definition_id', $definition_id);
            $builder->delete();

            $this->db->transComplete();

            return $this->db->transStatus();
        } 


I don't understand your code , but what i can say (if i understood you correctly) is when you execute insert, update and delete operations in query builder, by default after it resets all your applied data (for example join, where, orderBy, ignore).
All this operation (delete, insert ...) will return builder instance or false if failed.

Second variant is right

I don't understand your code , but what i can say (if i understood you correctly) is when you execute insert, update and delete operations in query builder, by default after it resets all your applied data (for example join, where, orderBy, ignore).
All this operation (delete, insert ...) will return builder instance or false if failed.

Sorry, it probably doesn't help that there's an error in the code. I'm converting this open source application from CI3 to 4. Let me simplify it a little.

PHP Code:

$builder = $this->db->table('foo_table');
$result = $builder->get();    //After this get() call the $builder instance is "reset" and free to use again?

$builder->where('item_id');
$builder->where('definition_id', $definition_id);
$builder->delete();    //This will work just fine as long as I'm wanting to run the delete against the same table? 


Am I correct that if I wanted to run the $builder->delete() against another table I would just need to place $builder = $this->db->table('bar_table'); after the $builder->get() call and before the $builder->where()?

PHP Code:

$builder = $this->db->table('foo_table');
$result = $builder->get();    //After this get() call the $builder instance is "reset" and free to use again? 


Yes. It's default behavior.
From your example: after get() method executed "select" part is clear again, BUT "from" part of your builder is still "foo_table".
So if you planning to continue to work with foo_table, you can.

PHP Code:

$builder->where('item_id');
$builder->where('definition_id', $definition_id);
$builder->delete();    //This will work just fine as long as I'm wanting to run the delete against the same table? 


YES.
In your example "where" is a clause, so it's NEED key/value pair. So the first row of code is wrong.
After "where" clause and delete() method execution builder will reset "where" clause part of query.

Am I correct that if I wanted to run the $builder->delete() against another table I would just need to place $builder = $this->db->table('bar_table'); after the $builder->get() call and before the $builder->where()?

If you need to work with another table, get query builder with another name of table like
$barTableBuilder = $this->db->table('bar_table');

In your example "where" is a clause, so it's NEED key/value pair. So the first row of code is wrong.

https://codeigniter.com/user_guide/datab...html#where

According to the QueryBuilder reference, the second parameter is optional. What I have is corrected and it's the equivalent of:

PHP Code:

$builder->where('item_id', null); 


It produces the SQL "WHERE item_id IS NULL" The only downside to using it as I have it, is that it's somewhat counter-intuitive that $builder->where('item_id') produces IS NULL and not IS NOT NULL.

According to the QueryBuilder reference, the second parameter is optional. 
...
It produces the SQL "WHERE item_id IS NULL" 

You are right. My fault.

In your example "where" is a clause, so it's NEED key/value pair. So the first row of code is wrong.

https://codeigniter.com/user_guide/datab...html#where

According to the QueryBuilder reference, the second parameter is optional.  What I have is corrected and it's the equivalent of:

PHP Code:

$builder->where('item_id', null); 


It produces the SQL "WHERE item_id IS NULL"  The only downside to using it as I have it, is that it's somewhat counter-intuitive that $builder->where('item_id') produces IS NULL and not IS NOT NULL.

It works but I would personally add the null parameter just for clarity of your intention. Because like @manager said, it looks like it's missing a parameter.

CodeIgniter 4 tutorials (EN/FR) - https://includebeer.com
/*** NO support in private message - Use the forum! ***/

The where caluse can also accept an associated array or a custom string.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )