0

That's my queries below:

$result4 = $this->db->query('SELECT team_leader_id FROM teams WHERE team_money < 0')->result_array();
$this->db->insert( 'd_teams', array( 'leader_id' => $result4[0]['team_leader_id'] ) );

Yes, it works but it works for only 1 row and there can be multiple or even 50 for example. How I can edit that so it will support all available rows that the select query return? So, it will insert them all in separate rows in the d_teams table. Thanks a lot!

andrew
  • 9,313
  • 7
  • 30
  • 61
MobEn
  • 65
  • 9
  • So, basically, you are trying to turn all `team_leader_id` listings from the `teams` table into new `leader_id` listings in the `d_teams` table (where `team_money` is negative)? – wavemode Sep 27 '14 at 22:19
  • you could use `insert_batch` see http://stackoverflow.com/questions/3849414/codeigniter-insert-multiple-rows-in-sql#answer-14332078 – andrew Sep 27 '14 at 22:19
  • Exactly, on the condition from `teams` table `WHERE team_money <0` – MobEn Sep 27 '14 at 22:20

3 Answers3

2

I believe what you're trying to do is possible with a subquery:

INSERT INTO d_teams (leader_id) (SELECT team_leader_id FROM teams WHERE team_money < 0);

So, in your code:

$this->db->query('INSERT INTO d_teams (leader_id) (SELECT team_leader_id FROM teams WHERE team_money < 0)');
wavemode
  • 2,076
  • 1
  • 19
  • 24
  • I believe it will work yeah .. but isn't it better to use foreach as this dude suggested below? – MobEn Sep 27 '14 at 22:39
  • I mean in terms of server load, which one is better mostly ? – MobEn Sep 27 '14 at 22:45
  • 2
    This one is the best in terms of server load as it generates the least amount of communication between your web service and your database instance. The foreach-method generates most traffic of all suggestions. Every time you use any command related to MySQL, messages are sent back an forth between the web service and database. The more data you can send in each message, the less of an overhead is the message passing. – Emanuel Sep 27 '14 at 22:50
0

You can try to use the insert function inside a loop that runs through the $result4:

$result4 = $this->db->query('SELECT team_leader_id FROM teams WHERE team_money < 0')->result_array();

foreach($result4 as $result) {
    $this->db->insert( 'd_teams', array( 'leader_id' => $result['team_leader_id'] ) );
}

Perhaps there is another way to insert multiple lines. But this should work.

pedrofs
  • 612
  • 1
  • 6
  • 14
0

This SO answer explains how to perform bulk inserts using CodeIgniter: https://stackoverflow.com/a/17875754/365296

In your case, I guess applying that would look something like this:

$result4 = $this->db->query('SELECT ... ')->result_array();
$teams = array_map(function ($dbRow) {
    return array('leader_id' => $dbRow['team_leader_id']);
}, $result4);
$this->db->insert_batch('d_teams', $teams);
Community
  • 1
  • 1
Emanuel
  • 831
  • 6
  • 14
  • isn't foreach a better method than that actually or even directly subquery? – MobEn Sep 27 '14 at 22:40
  • In PHP everying is slow, so if performance is an issue, don't use it. ;) But if you have to, the faster approach would be to have your MySQL server do as much work as possible, which in this case would be to use a subquery. – Emanuel Sep 27 '14 at 22:45
  • so the subquery as someone suggested will be better approach in terms of server load and etc right otherwise it doesn't matter mostly, right? – MobEn Sep 27 '14 at 22:46
  • It could matter if the web service and the database instance are far away from each other physically, or there is some other performance issue present in their communication, as each query generates a communication dealt with serially. If the message passing overhead is about 10 ms, and you use the foreach-method with 4000 teams, just passing messages would take 10 ms * 4000 = 40 seconds. That's a long time to wait for a web page to load. – Emanuel Sep 27 '14 at 22:54