Muliple insert into DB Loop

[eluser]RaZoR LeGaCy[/eluser]
Hello everyone,

What I am trying to do is take on form with three values and create a loop. Then pass the loop values into an insert into DB.

Code:

<center>
<p>&lt;?=$this->session->flashdata('status');?&gt;</p>

&lt;?=$this->validation->error_string; ?&gt;

&lt;?=form_open('razor/movies/bulk_submit');?&gt;

<table>
&lt;?php for ( $i = 0; $i <= 5; $i++) { ?&gt;
<tr>
<td class="left"><strong class="gold">Title:</strong></td>
<td>&lt;?
$data = array('name' => 'title['.$i.']', 'id' => 'title['.$i.']', 'value' => 'test', 'maxlength' => '255', 'size' => '50',);
echo form_input($data);
?&gt;</td>
<td colspan="3"><strong class="gold">Year:</strong></td>
<td>&lt;?
$data = array('name' => 'year['.$i.']', 'id' => 'year['.$i.']', 'value' => '1905', 'maxlength' => '4', 'size' => '4');
echo form_input($data);
?&gt;</td>
</tr>
<tr>
  <td nowrap="nowrap"><strong class="gold">Amazon Link:</strong></td>
  <td colspan="5">&lt;?
$data = array('name' => 'r_link['.$i.']', 'id' => 'r_link['.$i.']', 'value' => '123456', 'maxlength' => '80', 'size' => '50', 'style' => 'width:90%',);
echo form_input($data);
?&gt;</td>
</tr>
&lt;?php } ?&gt;
<tr valign="baseline">
<td colspan="6" align="center">
&lt;input type="submit" class="wymupdate" /&gt;</td></tr>
</table>
&lt;/form&gt;
</center>

So that should creat an array. Now I have my insert like this but there is something that I am missing.

Code:

for ( $i = 0; $i <= 5; $i++) {

$title =     htmlspecialchars(trim($this->input->post('title['.$i.']', TRUE)));
$year =     trim($this->input->post('year['.$i.']', TRUE));
$r_link =     trim($this->input->post('r_link['.$i.']', TRUE));

$this->db->like(array('cid' => '1', 'title' => $title, 'year' => $year));
$this->db->from('hellhorror_reviews');
$namecheck = $this->db->count_all_results();

    if ($namecheck = '0') {
        $today_date = date("Y-m-d");

            $data = array(
               'cid' => '1' ,
               'uid' => '1' ,
               'date' => $today_date,
               'title' => $title,
               'author' => 'Admin' ,
               'author_email' => '[email protected]' ,
               'r_link' => $r_link,
               'year' => $year,
            );

        $this->db->insert('hellhorror_reviews', $data);

        $this->session->set_flashdata('status', 'Reviews Added');
        
        redirect('razor/movies/bulk_insert');
    }
}

In other words I want to insert the values into the DB using a for loop or a foreach. I am confusing myself on which to use and how to implement it.

Thanks everyone

[eluser]GSV Sleeper Service[/eluser]
doing 5 separate inserts isn't very efficient, you could do this using just one insert. something like

Code:

INSERT INTO table (foo, bar)
VALUES
('foo1','bar1'),
('foo2','bar2'),
...
('foo5','bar5');

..and I've just spotted that you've got a redirect within your loop!

[eluser]RaZoR LeGaCy[/eluser]
I want to check if there are any duplicates and if not, then insert the values

[eluser]charlie spider[/eluser]
as GSV Sleeper Service said, your loop won't work because of the redirect.

but on top of that, you may find that your php loop will outpace your db's ability to check for existing records and then perform inserts.

do you really think people are going to submit 5 movie reviews at a time ?
how many horror flicks can a person watch in a night ?
hee hee just kidding...

another thing i noticed is that the line:

if ($namecheck = '0')

should prolly be:

Code:

if ($namecheck == '0')

else your just setting a value for $namecheck

but a better way to do it would be to check for the existing movie using a select instead of using COUNT, then just evaluate whether the query returned false or not. so:

Code:

$this->db->like(array('cid' => '1', 'title' => $title, 'year' => $year));
$this->db->from('hellhorror_reviews');
if ( !$this->db->get() ){

instead of

$namecheck = $this->db->count_all_results();

if ($namecheck = '0') {

i think that might be faster.

[eluser]GSV Sleeper Service[/eluser]
[quote author="RaZoR LeGaCy" date="1206774901"]I want to check if there are any duplicates and if not, then insert the values[/quote]

ok, in the the example below foo is your primary key

Code:

INSERT INTO table (foo, bar)
VALUES
('foo1','bar1'),
('foo2','bar2'),
...
('foo5','bar5')
ON DUPLICATE KEY UPDATE
bar = VALUES(bar);

ON DUPLICATE KEY UPDATE is a most useful construct.

http://dev.mysql.com/doc/refman/5.0/en/i...icate.html

[eluser]RaZoR LeGaCy[/eluser]
[quote author="charlie spider" date="1206783602"]as GSV Sleeper Service said, your loop won't work because of the redirect.

but on top of that, you may find that your php loop will outpace your db's ability to check for existing records and then perform inserts.

do you really think people are going to submit 5 movie reviews at a time ?
how many horror flicks can a person watch in a night ?
hee hee just kidding...

another thing i noticed is that the line:

if ($namecheck = '0')

should prolly be:

Code:

if ($namecheck == '0')

else your just setting a value for $namecheck

but a better way to do it would be to check for the existing movie using a select instead of using COUNT, then just evaluate whether the query returned false or not. so:

Code:

$this->db->like(array('cid' => '1', 'title' => $title, 'year' => $year));
$this->db->from('hellhorror_reviews');
if ( !$this->db->get() ){

instead of

$namecheck = $this->db->count_all_results();

if ($namecheck = '0') {

i think that might be faster.[/quote]

That didn't work because it was a fuzzy match but this worked

Code:

$query = $this->db->get_where('hellhorror_reviews', array('cid' => '1', 'title' => $title, 'year' => $year));
if ($query->num_rows() < 1) {

further more ( !$this->db->get() ){ returned errors but it would have been cool if I could just determine if there were rows then return true then continue with loop if true

[eluser]RaZoR LeGaCy[/eluser]
[quote author="GSV Sleeper Service" date="1206796661"][quote author="RaZoR LeGaCy" date="1206774901"]I want to check if there are any duplicates and if not, then insert the values[/quote]

ok, in the the example below foo is your primary key

Code:

INSERT INTO table (foo, bar)
VALUES
('foo1','bar1'),
('foo2','bar2'),
...
('foo5','bar5')
ON DUPLICATE KEY UPDATE
bar = VALUES(bar);

ON DUPLICATE KEY UPDATE is a most useful construct.

http://dev.mysql.com/doc/refman/5.0/en/i...icate.html[/quote]

I read this but I have three fields in table where they must all equal the same in order to signify as a duplicate
ex. if (cid = 1 and title = title and year = year) then is duplicate

[eluser]GSV Sleeper Service[/eluser]
make a unique index on your table using those three fields, something like

Code:

CREATE UNIQUE INDEX myindex ON your_table (cid, title, year)