CodeIgniter Forums
Multiple MySql insert problem - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Multiple MySql insert problem (/showthread.php?tid=19774)

Multiple MySql insert problem - El Forum - 06-18-2009

[eluser]polish[/eluser]
Hello All CI users! I need your help! I am writing my blog and at now I have a problem with adding tags to my news! When I will choose one tag all is ok, but if I will choose more then I have an error:

Code:
Error Number: 1054
Unknown column 'Array' in 'field list'
INSERT INTO 'newstags' ('ttags_id', 'nnews_id') VALUES (Array, '27')


I am using the model function for adding tags to news:

Code:
function tagi_add($data) {
return $this->db->insert('newstags', $data);
}

I need something like:
Code:
INSERT INTO 'newstags' ('ttags_id', 'nnews_id') VALUES ('1', '27')
INSERT INTO 'newstags' ('ttags_id', 'nnews_id') VALUES ('2', '27')
INSERT INTO 'newstags' ('ttags_id', 'nnews_id') VALUES ('5', '27')

for adding more than one tag with only one (normal or active records) query!

Any ideas???

Multiple MySql insert problem - El Forum - 06-18-2009

[eluser]n0xie[/eluser]
Code:
INSERT INTO newstags (ttags_id, nnews_id) VALUES (1, 27), (2, 27), (5, 27)......


Multiple MySql insert problem - El Forum - 06-18-2009

[eluser]gtech[/eluser]
to add to N0xies post I believe there is not an active record function to do this unfortunatly.

[url="http://ellislab.com/forums/viewthread/110436/"]there are some other solutions here[/url] with a nifty function to write n0xies SQL method.

A poster also points out the multiple inserts are only available in mysql, so maybe thats why it has not been added to AR as its supposed to be database independant.

Multiple MySql insert problem - El Forum - 06-18-2009

[eluser]polish[/eluser]
I know that there is one solution like:

Code:
INSERT INTO newstags (ttags_id, nnews_id) VALUES (1, 27), (2, 27), (5, 27)......

but I am sending ttags_id, nnews_id from view by the post method and this solution is bad!

I tested solution porposed by gtech and they aren't working!

Any other ideas???

Multiple MySql insert problem - El Forum - 06-18-2009

[eluser]gtech[/eluser]
give us a print_r of the exact array that you would pass to tagi_add()

eg
Code:
function tagi_add($data) {
  // just for test
  print_r($data);
  //return $this->db->insert('newstags', $data);
}


If the array is very large, reduce some of the data, and then myself or someone else can take a quick ganders if you want.

Multiple MySql insert problem - El Forum - 06-18-2009

[eluser]polish[/eluser]
When I add 3 tags print_r($data); give me:

Code:
Array ( [ttags_id] => Array ( [0] => 1 [1] => 2 [2] => 3 ) [nnews_id] => 27 )

When I add 1 tag:

Code:
Array ( [ttags_id] => 1 [nnews_id] => 27 )

For help this is my controller function:

Code:
function add_tags() {
    $this->load->helper('form1');            
    $news_id = $this->uri->segment(3);
    IF(isset($news_id) and is_numeric($news_id))
    {
    $data["tagi"] = array('name' => 'tagi');
    
    $rules['tagi'] = "required|xss_clean";
    $this->validation->set_rules($rules);
    
    if ($this->validation->run() == FALSE)
        {
        $data['tagi']['value'] = $this->input->post('tagi');
        $this->response['content'] = $this->load->view('tagi/tagi_add', $data, True);
        }
    else
        {
        $this->Tagi->tagi_add(array('ttags_id' => $this->input->post('tagi'), 'nnews_id' => $news_id));
        $this->response['content'] = '<h1><center>Tag(i) przypisano</center></h1>&lt;META HTTP-EQUIV="Refresh" CONTENT="1; URL='.site_url('comment/show/'.$news_id).'"&gt;';
        }
    }
    else
        {
        $this->response['content'] = '<h1><center>Błędny Link</center></h1>&lt;META HTTP-EQUIV="Refresh" CONTENT="1; URL='.site_url().'"&gt;';
        }    
    $this->response['right'] = $this->load->view('tagi/tagi_right', '', True);
    $this->load->view('index', $this->response);
    }


Multiple MySql insert problem - El Forum - 06-18-2009

[eluser]n0xie[/eluser]
[quote author="polish" date="1245358464"]

Code:
INSERT INTO newstags (ttags_id, nnews_id) VALUES (1, 27), (2, 27), (5, 27)......

this solution is bad!
[/quote]

What's bad about this solution exactly? Sounds to me you're just lazy.

Fine I'll SPELL it out for you.

Code:
$data = array(
                'ttags_id' => array(
                    0    =>  1,
                    1    =>    2,
                    2   =>    5),
                'nnews_id' => 27
                );
        $sql = 'INSERT INTO newstags (ttags_id, nnews_id) VALUES ';
        if (is_array($data['ttags_id']))
        {
            foreach($data['ttags_id'] as $key=>$value)
            {
                $sql .= "($value, $data[nnews_id]),";
            }
            $sql = substr($sql,0,-1);
        }
        else
        {
            $sql .= "($data[ttags_id], $data[nnews_id])";
        }

        echo $sql;


Multiple MySql insert problem - El Forum - 06-18-2009

[eluser]polish[/eluser]
I am not lazy n0xie but you are great!!! Yours solution was in 99 % good and this is the working solution:

Code:
function tagi_add($data)
{
        $sql = 'INSERT INTO newstags (ttags_id, nnews_id) VALUES ';
        if (is_array($data['ttags_id']))
        {
            foreach($data['ttags_id'] as $key=>$value)
            {
                $sql .= "($value, $data[nnews_id]),";
            }
            $sql = substr($sql,0,-1);
        }
        else
        {
            $sql .= "($data[ttags_id], $data[nnews_id])";
        }
        $query = $this->db->query($sql);
}



Topic solvedSmile!!!

Multiple MySql insert problem - El Forum - 06-18-2009

[eluser]gtech[/eluser]
Undecided :zip: