Best way to insert multiple values in to DB - PHP MySQL

Question

Problem solved. According to the answer of Zerquix18, I made small changes and it worked like a charm.

$count = count($book_array);
$query = 'INSERT INTO book_exchange (class, book_title, isbn) VALUES ';
for ($i=0; $i<$count; $i++){
    $query .= '( \''.$book_array[$i][0].'\',\''.$book_array[$i][1].'\',\''.$book_array[$i][2].'\' )';
    if ($i != ($count-1)) {
        $query .= ','; 
    }
}

Question:

I have a book table and and let users put information in the fields below. I want to insert the values into my book table. I use "for" loop to get the values each row and add it into an array. I'm gonna use "for" again to add these array into database.

For example if use fill value into the fields. I have 3 array: book_array[0] , book_array[1] , book_array[2] . Each of them contains 3 elements. I'm gonna write a for loop to insert 3 arrays into the table.

On the other hand, I think I can add all element with the same type into an array. For example I have an array for class, an array for book title and an array for isbn. then I add these array into each column in database. I think this way is faster. But I'm not sure it works. Please suggest me the best way to insert data into DB in this case. ALso please give me an example. Thanks.

for ($i=0; $i<$n; $i++) {
    $book_array[$i] = array($_POST['field_class_'.$i.''],$_POST['field_book_title_'.$i.''],$_POST['field_isbn_'.$i.'']);
}

you can insert aas many wors as you like in one query `INSERT INTO tbl_name (a,b,c) VALUES (1,2,3), (4,5,6), (7,8,9);` — , Feb 07 '16 at 01:50
Note you are now creating a SQL injection unless you escaped the variables in the $book_array. http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60496 this tells you how to solve this. — LEDfan, Feb 07 '16 at 07:52

score 2 · Accepted Answer · answered Feb 07 '16 at 01:50

2

In this example I try to make a query to insert all the values of your variable $book_array

<?php
$q = 'INSERT INTO table (col1,col2,col3) VALUES ';
$count = count($book_array);
for($i = 0; $i < $count; $i++) {
    $q .= "( {$book_array[$i][0]}, {$book_array[1]}, {$book_array[2]} )";
    // add a comma if it's not the last
    if( $i !== ($count-1) )
        $q .= ',';
}
// now $q is your query.

I did not test it.

answered Feb 07 '16 at 01:50

Zerquix18

769
6
19

Thank you, your code just needs couple small changes. I edited your code and put it on top of my question. Thank you. – Louis Tran Feb 07 '16 at 04:30

score 0 · Answer 2 · answered Feb 07 '16 at 01:59

0

You use this type of syntax for this problem:

INSERT INTO <table_name> ( Column1, Column2 ) VALUES ( Value1, Value2 ), ( Value3, Value4 )

answered Feb 07 '16 at 01:59

Lawakush Kurmi · Answer 3 · 2016-02-07T07:35:29.653

You can use query as like

INSERT INTO TABLE_NAME (1, 2) VALUES (v1, v2),(v1, v2),(v1, v2),..............
1,2 are representing column index.

In the above query i have mentioned the column index value (int) instead of column-label (String). The benefit of using index is, in-case you have change any column name then you don't need to change your query. Otherwise if you are using column label you must need to change your query also.

Best way to insert multiple values in to DB - PHP MySQL

3 Answers3