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, Jul 01 '18 at 07: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, Jul 01 '18 at 07:28
You are exiting the function after the first insert with "return" — NoBBy, Jul 01 '18 at 07:29

score 1 · Accepted Answer · answered Jul 01 '18 at 07:40

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 01 '18 at 07:40

Niellles

868
10
27

why you saying that you will implode the data, is it improve performance? – Sam Jul 01 '18 at 07:45
@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. – Niellles Jul 01 '18 at 07:56
i have more than 5000 rows, so is there any better way to insert this much properly? – Sam Jul 01 '18 at 08:06
Yeah, I'd insert it in a couple of large chunks. Does this [post](https://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query) help you out? – Niellles Jul 01 '18 at 08:31
No that post really confusing me, I don't know what to do on that, I was trying insert by my method its giving error as its taking more than 30 sec, please help with this – Sam Jul 01 '18 at 10:09
1

That's probably worth a new question, since your now running into another problem. Unless you've already figured it out ofc. – Niellles Jul 03 '18 at 06:15

query insert only one row instead multiple rows

1 Answers1