+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: CI4 $this-db->Table() inside foreach or outside (/showthread.php?tid=80466)
CI4 $this-db->Table() inside foreach or outside - objecttothis - 11-04-2021
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);
}
RE: CI4 $this-db->Table() inside foreach or outside - manager - 11-04-2021
Second variant is right
RE: CI4 $this-db->Table() inside foreach or outside - objecttothis - 11-04-2021
@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();
}
RE: CI4 $this-db->Table() inside foreach or outside - manager - 11-04-2021
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.
RE: CI4 $this-db->Table() inside foreach or outside - objecttothis - 11-04-2021
(11-04-2021, 06:24 AM)manager Wrote: Second variant is right
(11-04-2021, 11:10 AM)manager Wrote: 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()?
RE: CI4 $this-db->Table() inside foreach or outside - manager - 11-04-2021
(11-04-2021, 11:31 AM)objecttothis Wrote: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.
(11-04-2021, 11:31 AM)objecttothis Wrote: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.
(11-04-2021, 11:31 AM)objecttothis Wrote: 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');
RE: CI4 $this-db->Table() inside foreach or outside - objecttothis - 11-04-2021
(11-04-2021, 10:19 PM)manager Wrote: 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/database/query_builder.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.
RE: CI4 $this-db->Table() inside foreach or outside - manager - 11-05-2021
(11-04-2021, 10:45 PM)objecttothis Wrote: According to the QueryBuilder reference, the second parameter is optional.
...
It produces the SQL "WHERE item_id IS NULL"
You are right. My fault.
RE: CI4 $this-db->Table() inside foreach or outside - includebeer - 11-05-2021
(11-04-2021, 10:45 PM)objecttothis Wrote:(11-04-2021, 10:19 PM)manager Wrote: 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/database/query_builder.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.
RE: CI4 $this-db->Table() inside foreach or outside - InsiteFX - 11-06-2021
The where caluse can also accept an associated array or a custom string.