Validating Array Form Input for MYSQL Insertion

Question

I have made a form that submits an array of inputs to submit multiple rows in a database.

 <form action="addmultiprod.php" id="multiformsub" method="post">
 <div id="addmultiprod">
        <div class="inputbox"><input name="item_id[]" type="text" class="prodbox" placeholder="Item ID"></input></div>
        <div class="inputbox"><input name="item[]" type="text" class="prodbox" placeholder="Product Name"></input></div>
        <div class="inputbox"><input name="quant[]" type="text" class="prodbox" placeholder="Quantity"></input></div>
        <div class="inputbox"><input name="price[]" type="text" class="prodbox" placeholder="Price"></input></div>
    </div>
    <div id="addmultiprod2">
        <div class="inputbox"><input name="item_id[]" type="text" class="prodbox" placeholder="Item ID"></input></div>
        <div class="inputbox"><input name="item[]" type="text" class="prodbox" placeholder="Product Name"></input></div>
        <div class="inputbox"><input name="quant[]" type="text" class="prodbox" placeholder="Quantity"></input></div>
        <div class="inputbox"><input name="price[]" type="text" class="prodbox" placeholder="Price"></input></div>
    </div>
    <div id="addmultiprod3">
        <div class="inputbox"><input name="item_id[]" type="text" class="prodbox" placeholder="Item ID"></input></div>
        <div class="inputbox"><input name="item[]" type="text" class="prodbox" placeholder="Product Name"></input></div>
        <div class="inputbox"><input name="quant[]" type="text" class="prodbox" placeholder="Quantity"></input></div>
        <div class="inputbox"><input name="price[]" type="text" class="prodbox" placeholder="Price"></input></div>
    </div>
    </div>
 </form>

My question is, how will I be able to validate the data of a set of inputs when the values are blank in order to prevent inserting them into the database.

Example :

When the inputs inside <div id="addmultiprod2"> or atleast 1 is blank, the query would not insert it into the database.

Here is how I insert the data into my database.

 $sql ="INSERT INTO `$tablechoice`(item_id, item_description, item_quantity, item_price) 
 VALUES ('$item_id[0]','$item[0]','$quant[0]','$price[0]'),
 ('$item_id[1]','$item[1]','$quant[1]','$price[1]'), 
 ('$item_id[2]','$item[2]','$quant[2]','$price[2]'),";

 $conn2->query($sql);

What codes must I add/modify in order to fulfill the example above?

UPDATE:

I am also looking at another scenario where, if I am to add an option/button where the user can add additional set of inputs through jquery, how will I be able to validate that?

Many thanks on your future insights and feedback! :)

Unfortunately I cannot use prepared statements when the table is a variable. Do you know a secure way around that problem? — Kenreaper, Jun 10 '15 at 06:26
The tablename can be variable. Your values should be used as parameter in a prepared statement. — Jens, Jun 10 '15 at 06:28
You are correct that you cannot use variable table names in a PDO prepared statement. If you have a limited set of tables, however, you can use a whitelisted switch as seen here: http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter — kojow7, Jun 10 '15 at 06:32
@Jens Only the values to be inserted can be used as a parameter, as for the table "value", it cannot be. Hi there kojow, thanks again for your answers, what if i get the table names from the tables of a separate database? in my case unlimited set of tables I guess? Is there a secure way for that? — Kenreaper, Jun 10 '15 at 06:44

score 1 · Answer 1 · answered Jun 10 '15 at 06:20

1

Before your insert query you can check for empty values like

if(!empty($quant[0])) // similarly for all input arrays
{
   // your insert query here
}

answered Jun 10 '15 at 06:20

Manashvi Birla

2,837
3
14
28

Your answer is on the right track! However I am also looking at another scenario where, if I am to add an option where the user can add additional set of inputs, how will I be able to validate that? – Kenreaper Jun 10 '15 at 06:32
can you please elaborate this condition? – Manashvi Birla Jun 10 '15 at 06:34

score 1 · Answer 2 · answered Jun 10 '15 at 06:29

1

There are two ways to do this:

Method 1:

You can set each of the fields to NOT NULL in your MySQL table. That way if you try to insert something that is empty it will not insert.

Method 2:

In your PHP code check for empty before inserting:

if (!empty($item_id[0]) && !empty(item[0]) && !empty($quant[0]) && !empty($price[0])){
     //your database insert code here
}

answered Jun 10 '15 at 06:29

kojow7

10,308
17
80
135

1

Thanks again mate! Though I am looking for as how am I able to send this through a loop. Just as Logan suggested. Still much appreciation to your answer my friend. – Kenreaper Jun 10 '15 at 06:46

score 1 · Accepted Answer · answered Jun 10 '15 at 06:39

Note:

You can check them all by running it through a loop. This is good if the form being submitted is dynamically generated.
No need for </input>

Sample code:

$counter = count($_POST["item_id"]); /* COUNT HOW MANY FIELD HAS BEEN SUBMITTED*/

for($x = 0; $x<= $counter; $x++){ /* RUN IT THROUGH A FOR LOOP */

  if(!empty($item_id[$x]) && !empty($item[$x]) && !empty($quant[$x]) && !empty($price[$x])){

    /* THIS WILL ONLY EXECUTE IF THE SET IS NOT EMPTY */
    $sql ="INSERT INTO `$tablechoice`(item_id, item_description, item_quantity, item_price) 
           VALUES ('$item_id[$x]','$item[$x]','$quant[$x]','$price[$x]')";
    $conn2->query($sql);

  } /* END OF CHECKING EACH FIELD */

} /* END OF LOOP */

The perfect answer I am looking for. Cheers! – Kenreaper Jun 10 '15 at 06:49 — Kenreaper, Jun 10 '15 at 06:49

score 0 · Answer 4 · answered Jun 10 '15 at 06:40

0

if you wan to filter before the submit button clicked, you can use HTML 5 Attribute. Most of browser support HTML 5 now, i think this one is better way if you just want to make input is not empty:

use HTML 5 Attribute --> "required"

Ex: <input type="text" name="username" required>

that code will show message if username empty when click submit button.

i hope this help

answered Jun 10 '15 at 06:40

Doni

576
6
9

1

While it is good to do front-end checks like this, they can easily be overridden by someone. Therefore, you should still have the back-end checks in place. Also, in this case the user may want to give the option to the user for not filling in all the form fields. – kojow7 Jun 10 '15 at 06:43
you are correct kojow7... back-end checks is very important, since user can overridden our front-end checks. – Doni Jun 10 '15 at 07:08

Validating Array Form Input for MYSQL Insertion

4 Answers4

Note: