query insert only one row instead multiple rows

Question

I was trying to insert multiple values from the excel sheet, I got all data from excel sheet to an array variable. but when I insert it always inserts the 1st row only, I have a method to execute insert query by using PDO,

My Data

array (size=4)
  1 => 
    array (size=5)
      'A' => string '*' (length=1)
      'B' => string 'Title' (length=5)
      'C' => string 'Author' (length=6)
      'D' => string 'Publication ' (length=12)
      'E' => string 'Container' (length=9)
  2 => 
    array (size=5)
      'A' => float 1
      'B' => string 'Test' (length=4)
      'C' => string 'one' (length=3)
      'D' => string 'two' (length=3)
      'E' => string 'X1' (length=2)
  3 => 
    array (size=5)
      'A' => float 2
      'B' => string 'Test' (length=4)
      'C' => string 'three' (length=5)
      'D' => string 'four' (length=4)
      'E' => string 'X2' (length=2)
  4 => 
    array (size=5)
      'A' => float 3
      'B' => string 'Test' (length=4)
      'C' => string 'five' (length=4)
      'D' => string 'six' (length=3)
      'E' => string 'X3' (length=2)

This my method

public function importBooks($data, $nr)
{
    // Init query
    $this->db->query('INSERT INTO books_pre (title, author, publication, container, created_by, created_at) VALUES (:title, :author, :publication, :container, :created_by, now())');

    for ($i=2; $i<$nr; $i++) {
        // Bind values
        $this->db->bind(':title', $data[$i]['B']);
        $this->db->bind(':author', $data[$i]['C']);
        $this->db->bind(':publication', $data[$i]['D']);
        $this->db->bind(':container', $data[$i]['E']);
        $this->db->bind(':created_by', $_SESSION['user_id']);

        // Execute query
        if ($this->db->execute()) {
            return true;
        } else {
            return false;
        }
    }
}

And what have you tried to debug this? Is the loop running properly from 2 till the number you expect? Is the data used in the bind call the one you expect? — Nico Haase
– Nico Haase, Commented Jul 1, 2018 at 7:26
yes, I checked the values, I am looping the number of rows from excel sheet its correct, and the values on binding method also correct — Sam
– Sam, Commented Jul 1, 2018 at 7:28
You are exiting the function after the first insert with "return" — NoBBy
– NoBBy, Commented Jul 1, 2018 at 7:29

Niellles · Accepted Answer · 2018-07-01 07:40:05Z

1

The return statement will end the execution of your method -- and exit the loop. To quote the manual:

If called from within a function, the return statement immediately ends execution of the current function, and returns its argument as the value of the function call.

To make this work, with minimal effort, I'd only return false when $this->db->execute() fails and return true at the end of your method, like so:

public function importBooks($data, $nr)
{
    // Init query
    $this->db->query('INSERT INTO books_pre (title, author, publication, container, created_by, created_at) VALUES (:title, :author, :publication, :container, :created_by, now())');

    for ($i=2; $i<$nr; $i++) {
        // Bind values
        $this->db->bind(':title', $data[$i]['B']);
        $this->db->bind(':author', $data[$i]['C']);
        $this->db->bind(':publication', $data[$i]['D']);
        $this->db->bind(':container', $data[$i]['E']);
        $this->db->bind(':created_by', $_SESSION['user_id']);

        // Execute query
        if (!$this->db->execute()) {
            return false;
        }
    }

    return true;
}

However, if I had to rewrite this code, I'd personally probably implode the data array and insert it all in one query.

answered Jul 1, 2018 at 7:40

Niellles

8781 gold badge10 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sam Over a year ago

why you saying that you will implode the data, is it improve performance?

Niellles Over a year ago

@Looper It may improve performance, although it won't be noticeable in this example. However, if you had a bigger dataset (let's say 10,000 rows) you'll be sending a lot of queries -- probably too many, too fast. In that case it would be better practice, if you'd ask me, to insert it in several larger chunks.

Sam Over a year ago

i have more than 5000 rows, so is there any better way to insert this much properly?

Niellles Over a year ago

Yeah, I'd insert it in a couple of large chunks. Does this post help you out?

Niellles Over a year ago

That's probably worth a new question, since your now running into another problem. Unless you've already figured it out ofc.

|

Collectives™ on Stack Overflow

query insert only one row instead multiple rows

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related