DB Update with multiple inputs in a loop

[eluser]Ludovic-r[/eluser]
If I do that and look in the source code it only shows up this : <input type="text" name="category_name[]" value="db_result" /> and I can't do anything with that no?

I tried the :

foreach($_POST[‘category_name’] as $key => $value)
{
$i = 0;
foreach ($value as $item)
{
$data[$i][$key] = $item;
$i++;
}
}

In controller but the foreach says "invalid argument" :/

Thanks for help!!

[eluser]oneos[/eluser]
I think you're looking for this:

Code:

echo form_open('admin/update_categories/');
    if (isset($result)
        foreach ($result->result() as $row)
            echo form_input('category_name[]', $row->category_name);
            // category_name[]: Fields will build an array in POST
    else
        echo '<p>No Categories</p>';

    echo form_submit('','Save');
echo form_close();

And then, as noted earlier by pmoroom, in your controller:

Code:

$i = 0;
foreach ($this->input->post('category_name') as $cat)
    $data[$i++]['category_name'] = $cat;
    // The $i++ creates a multi-dimensional array to insert
    // multiple rows into the DB.

$this->db->insert_batch('categories', $data);

Or somesuch. Although, since you're updating you'd have to tweak that to use a custom UPDATE query, I think.

[eluser]Ludovic-r[/eluser]
Thanks so much! That did the trick Sorry to doubt, for me the 'category_name' with [] was a bit strange.

But for the DB update I've done this :

Code:

function update_categories($data) {
        $this->db->empty_table('ft_categories');
        $this->db->insert_batch('ft_categories', $data);

}

Ok actually, I empty the table to re insert the data, it's not a real UPDATE but that did the trick!
Is it okay to do this like that or should I try a real UPDATE query?

[eluser]Ludovic-r[/eluser]
One more question please :

I have the field 'category_name' in the table called 'ft_categories' AND in another table called 'ft_data' and I want to UPDATE the 'category_name' field in the both tables. Maybe with JOIN but I don't know where to start...

Could you indicate me the way for that? It will be very very appreciated! Thanks!

[eluser]oneos[/eluser]

Code:

function update_categories($data)
{
    $this->db->truncate('ft_categories');
    $this->db->insert_batch('ft_categories', $data);
    
    $this->db->from('ft_data dat');
    $this->db->join('ft_categories cat', 'cat.id = dat.id');
    $this->db->set('dat.category_name', 'cat.category_name');
    $this->db->update();
}

Something like this might work depending on your table structure, I'm not sure. I haven't used the Active Record class to do quite this sort of thing, so I'm not positive it would building the right query.

Hope it helps in actually figuring it out though.

(Also, there is an update_batch function, though it's not documented, and I'm not sure that it's complete.)

[eluser]Ludovic-r[/eluser]
Thanks for your time so much!

Let me provide you more informations :

The DB :

Code:

TABLE 1 called 'ft_categories' with 2 fields : 'id', 'category_name'
TABLE 2 called 'ft_upload_data' with 2 fields : 'id', 'category'

I just need to update my fields (from the form) in the TABLE 1 in the field called 'category_name' AND in the TABLE 2 in the field called 'category'

I can't reach this goal with your code, it works perfectly but with the '$this->db->set('dat.category_name', 'cat.category_name');' the field 'category' in the TABLE 2 won't update with the right data.

Any ideas?

I'm sorry to waste your time

[eluser]oneos[/eluser]
This is the query I was attempting to build, which we could just run as such:

This will do what you want, assuming the `id` fields in the two tables are related.

Code:

$sql = 'UPDATE `ft_categories` `cat`, `ft_upload_data` `dat`'
    . 'SET `dat`.`category` = `cat`.`category_name`'
    . 'WHERE `dat`.`id` = `cat`.`id`;';

$query = $this->db->query($sql);

After you've reloaded your categories table, it'll update your data table, setting the category names to eachother where the id's match.

[eluser]Ludovic-r[/eluser]
Well, the JOIN works well and update the both Tables. But there's a new problem now :

When I update my "category names" I do this in the MODEL :

Code:

$this->db->truncate('ft_categories');
        $this->db->insert_batch('ft_categories', $data);
        
        
        $query = $this->db->query("UPDATE ft_categories, ft_upload_data
                 SET category = ft_categories.category_name
                 WHERE test_id = category_id");

Another thing, to create a Category I have this :

Code:

function create_category($data) {
        
        $this->db->insert('ft_categories', $data);
        $query = $this->db->query("UPDATE ft_categories
          SET category_id = category_id + 1
          ");
}

This will increment the 'cat.id' field in the Table 1 to do the JOIN with the 'dat.id' in the Table 2

The problem is that "truncate" will empty the Table 1 ('ft_categories') so I lost my JOINED ids when I UPDATE the fields and the 'cat.id' in the Table 1 does not match anymore with the 'dat.id' in the Table 2

I'm a bit lost at this state :/ any help would be very appreciated.

[eluser]Ludovic-r[/eluser]
No ideas? Any help would be very appreciated, I'm stuck with that