How to insert multiple data into database with php

Question

UPDATE:

How to insert multiple data into database? I have Product tabel with 3 fields. status int 1, id_product varchar 10, and name_product varchar 30. Here is my code:

<table border='1' cellpadding='2' cellspacing='5'>
<tr>
  <th>STATUS</th>
  <th>PRODUCT ID</th>
  <th>PRODUCT NAME</th>
  <th>CHOOSE</th>
</tr>

<?php
  include "connect.php";
  $sql = "SELECT * FROM PRODUCT WHERE IDCUSTOMER = $_SESSION[IDCUSTOMER]";
  $query = mysql_query($sql);
  while($result = mysql_fetch_array($query)){
  echo"<td><select name='status'><option value='0'>BUY</option>
  <option value='1'>SELL</option>
  <option value='2'>LOAN</option>
  </select></td>
  <td>$result[id_product]</td>
  <td>$result[name_product]</td>
  <td><input type='checkbox' name='check' /></td>";
  }
?>
</table>

The data that I want to insert into database is status, id_product, and name_product.

I want to insert this multiple data when checkbox is checked. I have don't know how to insert them. I hope anyone here can help me :-). I really appreciates any help.

seems like you are only retrieving information from the database. where is the code that is supposed to insert data? — RST
– RST, Commented Jan 30, 2016 at 16:59
@RST that is my question. how can to insert that data because it have a multiple data. Any suggest — Mike
– Mike, Commented Jan 30, 2016 at 17:32
code to insert the data does not exist. That's what I want to ask how to insert multiple data, @NanaPartykar — Mike
– Mike, Commented Jan 30, 2016 at 17:35

Kenney · Accepted Answer · 2016-02-02 16:24:55Z

2

That won't work - you would have one select / check parameter per result, so you can't tell where it belongs after you post the (missing) <form>, since all you get is a single $_REQUEST['check'] with value on and a single $_REQUEST['status'] with a value 0, 1 or 2.

Also, $result[id_product] should be {$result['id_product']} etc.

If you name your fields like this, including the product ID, you will be able to know to which product the check and status values apply:

$prod_id = $result['id_product'];
echo "
  <select name='status-{$prod_id}' ...>
  <input name='check-{$prod_id}' ...>
";

you can then process them server-side like this:

foreach ( $_REQUEST as $k => $v )
   if ( preg_match( "@^check-(.*)@", $k, $m ) )      
   {
       $id_product = $m[1];
       $status = $_REQUEST[ "status-$id_product" ];
       // your INSERT query...
   }

or, without preg_match, you'd check if strpos( $k, "check-" ) === 0 and substr( $k, strlen( "check-" ) ) to get the product id.

Alternative: Arrays

PHP offers another way to pass request parameters so that they end up as an array: you can name the inputs a[b], for example:

  <input name='a[foo]' value='abc'/>
  <input name='a[bar]' value='def'/>

If you submit this form and then

echo "<pre>" . print_r( $_REQUEST, 1 ) . "</pre>";

you will see

 Array
 (
     [a] => Array
     (
         [foo] => abc
         [bar] => def
     )
 )

That means that you can access those inputs as an array: $_REQUEST['a']['foo'].

You can also do this:

  <input name='a[]' value='abc'/>
  <input name='a[]' value='def'/>

in which case $_REQUEST is

 Array
 (
     [a] => Array
     (
         [0] => abc
         [1] => def
     )
 )

The Form

Now let's apply this to your form.

I'm taking this opportunity to introduce you to PDO since mysql_query etc. is deprecated. You could use mysqli, but if you change databases you'd have to change a lot of code; with PDO you'd simply change the connection string.

Also it's wise to use query parameters / parameter binding. The query

"SELECT * FROM PRODUCT WHERE IDCUSTOMER = $_SESSION[IDCUSTOMER]";

has two related problems: First, $_SESSION[IDCUSTOMER] should be {$_SESSION['IDCUSTOMER']}, otherwise you'd get a warning and the query will be ".. WHERE IDCUSTOMER = " - a syntax error. Second, if $_SESSION['IDCUSTOMER'] is not an integer, the query will also have a syntax error.

// connect to the database (connect.php)
$db = new PDO( "mysql:host=localhost;dbname=your_db", $dbuser, $dbpass );

// query for products using ? placeholders for query parameters:
$sth = $db->prepare( "SELECT * FROM PRODUCT WHERE IDCUSTOMER = ?" );
$sth->execute( [ $_SESSION['IDCUSTOMER'] ] );

while ( $row = $sth->fetch() ) {
  $id_product = $result['id_product'];  // makes things easier below
  echo "
    <tr>
      <td>
          <select name='status[$id_product]'>
            <option value='0'>BUY</option>
            <option value='1'>SELL</option>
            <option value='2'>LOAN</option>
          </select>
      </td>
      <td> $id_product </td>
      <td> {$row['name_product']} </td>
      <td> <input type='checkbox' name='check[$id_product]' /> </td>
  ";
}

Here you see we're using the foo[bar] notation for HTTP request parameter names rather than the foo[] notation. This is because if we use <select name='status[]'> and <input type='checkbox' name='check[]'>, then it can happen that count( $_REQUEST['status']) != count( $_REQUEST['check'] ), because a checkbox parameter is only submitted if it is checked, and absent otherwise.

It's also important to use the $result['id_product'] and not simply a row-index ($i++), because the order and number of rows that is returned by the query can change between generating the <form> and submitting it.

Processing the form

This is very similar to the earlier foreach ($_REQUEST snippet, but simpler because we use the foo[bar] syntax for request parameters:

$sth = $db->prepare( "
  INSERT INTO PRODUCTS (id_product, name_product, status, IDCUSTOMER ) VALUES (?,?,?,?)
");

foreach ( $_REQUEST['check'] as $id_product => $ignore )
    $sth->execute( [ 
        $id_product, 
        $name_product, // TODO
        $status,
        $_SESSION['IDCUSTOMER']
    ] );

edited Feb 2, 2016 at 16:24

answered Jan 30, 2016 at 17:02

Kenney

9,11317 silver badges21 bronze badges

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

7 Comments

Mike Over a year ago

This code works for what? <select name='status-{$result['id_product']}' ...> <input name='check-{$result['id_product']}' ...>

Mike Over a year ago

@Kenney.So, it will be like this? foreach ( $_REQUEST as $k => $v ) if ( preg_match( "@^check-(.*)@", $k, $m ) ) { $id_product = $m[1]; $status = $_REQUEST[ "status-$id_product" ]; Insert into product (status,id_product,name_product) values ($status,$id_product,$name_product) }

Kenney Over a year ago

Something like that, but where does $name_product come from? What are you trying to do exactly? Your table shows existing products, and you'll be adding those products to the table again - shouldn't that give errors when id_product is unique? If, for instance, you simply want to update those products with the new status, you should use an UPDATE query (I've seen others confused between UPDATE and INSERT, so I'm just making sure..)

Mike Over a year ago

I'll explain what i'm trying to do @Kenny. First, showing all of the data from product table which contain status, id product, and name product like in the table above. Second, when all of the data has been shown I choose which product that what I want and check the checkbox. Third, save all of the data into database. $name_product is just variabel for name product value.that i given. Yes I'll be adding those products to the table again so i make the id_product not unique. What does your opinion?

Kenney Over a year ago

Okay, thanks for explaining. Then you got it - it's basically just looping over all checked checkboxes and doing an INSERT query for each product. I'll be happy to help you to get it to work if you run into trouble.

|

Collectives™ on Stack Overflow

How to insert multiple data into database with php

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related