Welcome Guest, Not a member yet? Register   Sign In
Multiple MySql insert problem
#1

[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???
#2

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

[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.
#4

[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???
#5

[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.
#6

[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);
    }
#7

[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;
#8

[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!!!
#9

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




Theme © iAndrew 2016 - Forum software by © MyBB