How to insert multiple row data into database from PHP

Question

am currently creating a table in PHP that display all row of data from one of the table in my database. After that, I want to save all row from the PHP's table into new database table. But the problem is, after save, only the last row will be stored into the new table in database, the other row from early will not be save. Can someone help me. I want to try the foreach but can someone teach me.

<table border="1" align="center" cellspacing="0">
<tr>
  <td colspan="12"><div align="center">
      <h3><strong>BOOKS</strong></h3>
  </div></td>
</tr>
<tr>
  <td width="80"><div align="center">Code</div></td>
  <td width="40"><div align="center">No</div></td>
  <td width="250"><div align="center">Name</div></td>
  <td width="100"><div align="center">Publisher</div></td>
  <td width="80"><div align="center">Price</div></td>
  <td width="60"><div align="center">Quantity</div></td>
</tr>

<?php do { ?>
  <tr>
    <td><div align="center">
        <input name="student_no" type="hidden" id="student_no" value="<?php echo $row_UserDetails['student_no']; ?>" />
        <input name="book_code" type="text" id="book_code" value="<?php echo $row_book_booking_list['book_code']; ?>" size="8" readonly="readonly" />
    </div></td>
    <td><div align="center">
        <input name="book_no" type="text" id="book_no" value="<?php echo $row_book_booking_list['book_no']; ?>" size="1" readonly="readonly" />
    </div></td>
    <td><div align="center">
        <input name="book_name" type="text" id="book_name" value="<?php echo $row_book_booking_list['book_name']; ?>" size="31" readonly="readonly" />
    </div></td>
    <td><div align="center">
        <input name="book_publisher" type="text" id="book_publisher" value="<?php echo $row_book_booking_list['book_publisher']; ?>" size="10" readonly="readonly" />
    </div></td>
    <td><div align="center">
        <input name="book_price" type="text" id="book_price" value="<?php echo $row_book_booking_list['book_price']; ?>" size="7" readonly="readonly" />
    </div></td>
    <td><div align="center">
      <select name="book_quantity" id="book_quantity">
        <option value="0">0</option>
        <option value="1">1</option>
        <option value="2">2</option>
        <option value="3">3</option>
        <option value="4">4</option>
        <option value="5">5</option>
        <option value="6">6</option>
        <option value="7">7</option>
        <option value="8">8</option>
        <option value="9">9</option>
      </select>
    </div></td>
  </tr>
<?php } while ($row_book_booking_list = mysql_fetch_assoc($book_booking_list)); ?>
</table>

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO book_booked_list (student_no, book_code, book_no, book_name, book_publisher, book_price, book_quantity) VALUES (%s, %s, %s, %s, %s, %s, %s)",
                   GetSQLValueString($_POST['student_no'], "int"),
                   GetSQLValueString($_POST['book_code'], "text"),
                   GetSQLValueString($_POST['book_no'], "text"),
                   GetSQLValueString($_POST['book_name'], "text"),
                   GetSQLValueString($_POST['book_publisher'], "text"),
                   GetSQLValueString($_POST['book_price'], "double"),
                   GetSQLValueString($_POST['book_quantity'], "int"));

mysql_select_db($database_book_con, $book_con);
$Result1 = mysql_query($insertSQL, $book_con) or die(mysql_error());
}

So are you trying to copy rows from one database into another? If that is the case, I would maybe look here: stackoverflow.com/questions/2821517/… — Ignatius_Gim
– Ignatius_Gim, Commented Aug 18, 2016 at 3:45
actually, copy the row from one into another but with the updation of the data. To be easy like this, first database table is book table, second database table is for the book that has been booked. 1st table only display what books need to be booked. 2nd table will have all the booked books from each student differentiate by their student number.. — black
– black, Commented Aug 18, 2016 at 3:57
I just need help on the query to allow multiple row saving into database because currently right now only the last row will be saved. — black
– black, Commented Aug 18, 2016 at 3:59
WARNING: If you're just learning PHP, please, do not learn the obsolete mysql_query interface. It's awful and has been removed in PHP 7. A replacement like PDO is not hard to learn and a guide like PHP The Right Way helps explain best practices. Make sure your user parameters are properly escaped or you will end up with severe SQL injection bugs. — tadman
– tadman, Commented Aug 18, 2016 at 5:54

BeetleJuice · Accepted Answer · 2016-08-18 04:01:38Z

3

To copy all rows from source_table to book_booked_list table, just execute the query below:

INSERT INTO book_booked_list 
    (student_no, book_code, book_no, book_name, book_publisher,
       book_price, book_quantity)
SELECT 
    student_no, book_code, book_no, book_name, book_publisher, 
       book_price, book_quantity
FROM source_table

Replace source_table with the name of the table you copy data from.

answered Aug 18, 2016 at 4:01

BeetleJuice

41.1k24 gold badges120 silver badges181 bronze badges

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

3 Comments

black Over a year ago

do I need to change something in here to allow multiple row of saving into databse?

black Over a year ago

$insertSQL = sprintf("INSERT INTO book_booked_list (student_no,book_code, book_no, book_name, book_publisher,book_price, book_quantity) VALUES (%s, %s, %s, %s,%s, %s, %s)", GetSQLValueString($_POST['student_no'], "int"), GetSQLValueString($_POST['book_code'], "text"), GetSQLValueString($_POST['book_no'], "text"), GetSQLValueString($_POST['book_name'], "text"),

BeetleJuice Over a year ago

@black No you don't need to specify values and you don't need to use $_POST. Just run the query I gave you after replacing source_table with the table you copy from. The DB will handle all everything for you; it will copy all rows from one table to the other.

Webomatik · Accepted Answer · 2016-08-18 04:18:40Z

-1

Your DO...WHILE loop generates input fields with identical names: all student number fields are named "student_no", so only the last set of form values will be kept and sent for processing.

If you want to insert multiple rows, you need to use arrays instead: replace "student_no" with "student_no[]", "book_code" with "book_code[]", etc. Then, on the server side, loop through the arrays to insert each row separately:

$stuno = $_POST['student_no'];
$bookcode = $_POST['book_code'];
.... etc...
for($i=0; $i < count($stuno); $i++){
    $sql = "INSERT INTO book_booked_list (student_no, ....) 
    VALUES ($stuno[$i])"
}

Of course, there are more efficient ways to do that, and you should sanitize your form values before inserting into your database. This is only to get you started with arrays.

answered Aug 18, 2016 at 4:18

Webomatik

8367 silver badges7 bronze badges

2 Comments

black Over a year ago

for($i=0; $i < count($stuno); $i++){ $sql = "INSERT INTO book_booked_list (student_no, book_code, book_no, book_name, book_publisher, book_price, book_quantity) VALUES ($studentno[$i],$book_code[$i]$book_no[$i]$book_name[$i]$book_publisher[$i]$book_price[$i]$book_quantity[$i])" mysql_select_db($database_book_con, $book_con); $Result1 = mysql_query($sql, $book_con) or die(mysql_error()); }

black Over a year ago

change to this one but error said "unexpected 'mysql_select_db'"

Collectives™ on Stack Overflow

How to insert multiple row data into database from PHP

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related