Simple Array Problem

[eluser]markanderson993[/eluser]
Hello there codeIgniter experts, I have a small array problem. I am trying to gather all the rows of one table, and update another, however, when I use the code below it only executes for the first row and not all 198. Why is this?

Code:

$image_dir_rows = $this->db->get('image_dir');
            
            foreach($image_dir_rows->result() as $audio_dir_row);
            {
                $this->db->where('image_id',$audio_dir_row->image_id);
                $this->db->update('image_info',array('user_id'=>$audio_dir_row->id));
            }

Any help is greatly appreciated!!!
- Pianoman993

[eluser]bretticus[/eluser]
Probably because when you call update on the db object, the object is cleared. Instead of using active record in this instance why not just send a single INSERT ... SELECT statement?

[eluser]markanderson993[/eluser]
Thanks for the reply. Could you show me an example of what you're talking about? I am trying to update an existing row not create a whole new one.

[eluser]bretticus[/eluser]
Ah yes, an update not an insert. You could still probably do that with one query however. if you're using mysql, your query might look something like...

Code:

UPDATE image_dir,image_info SET image_info.user_id=image_dir.id
WHERE image_info.image_id=image_info.image_id;

[eluser]markanderson993[/eluser]
I tried your method out and it filled in a 1 for every user_id in the image_info table... which is not supposed to happen.

I would like to stick with codeIgniter's approach to DB active records and I still can't figure out why this doesn't work

Code:

$image_dir_rows = $this->db->get('image_dir');
        
        foreach($image_dir_rows->result() as $audio_dir_row);
        {
                $this->db->where('image_id',$audio_dir_row->image_id);
                $this->db->update('image_info',array('user_id'=>$audio_dir_row->id));
        }

[eluser]jedd[/eluser]

Code:

WHERE image_info.image_id=image_info.image_id;

This line would be slightly problematic .. since it'll resolve to all records.

I suspect it's meant to be:

Code:

WHERE image_info.image_id=image_dir.image_id;

BUT you really need to look at your schema and make sure. You could post your schema if you want a second opinion.

Note that doing $query-> stuff is 'the CI way', also. I think the problem Bretticus identified was that when using AR as you've done, it snots any existing results as soon as you do another query - so you can't nest sub-queries as you've tried to. I suspect this is a common problem, though, so there's probably a workaround for it.

[eluser]bretticus[/eluser]
[quote author="pianoman993" date="1237269032"]I tried your method out and it filled in a 1 for every user_id in the image_info table... which is not supposed to happen.[/quote]

It all depends on your specific database, version and capabilities. I don't have the luxury of playing around with your database. Thus, my examples are just examples. It's up to you to tweak them.

[quote author="pianoman993" date="1237269032"]
I would like to stick with codeIgniter's approach to DB active records and I still can't figure out why this doesn't work
[/quote]

You can do this with CI, that's your decision, but I still believe your code doesn't work because this isn't the way AR is supposed to work. You could try using result_array() to loop through the resulting array instead of calling result() from the db object.

[eluser]bretticus[/eluser]
CI way or not, it's still much more efficient to send one SQL query instead of 198! If you care about optimization, you may want to reconsider. If this is for you a few others (not the whole Internet) and your records will stay around 198, then using the "CI way" should be no big deal.

[eluser]markanderson993[/eluser]
Thank you everyone for your thorough replies. My code now works and you have been extremely helpful. Again, Thank you!