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
– Kenreaper, Commented Jun 10, 2015 at 6:26
The tablename can be variable. Your values should be used as parameter in a prepared statement. — Jens
– Jens, Commented Jun 10, 2015 at 6: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: stackoverflow.com/questions/182287/… — kojow7
– kojow7, Commented Jun 10, 2015 at 6: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
– Kenreaper, Commented Jun 10, 2015 at 6:44

Logan Wayne · Accepted Answer · 2015-06-10 06:39:54Z

1

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 */

answered Jun 10, 2015 at 6:39

Logan Wayne

5,98716 gold badges34 silver badges50 bronze badges

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

1 Comment

Kenreaper Over a year ago

The perfect answer I am looking for. Cheers!

Manashvi Birla · Accepted Answer · 2015-06-10 06:20:30Z

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, 2015 at 6:20

Manashvi Birla

2,8393 gold badges16 silver badges28 bronze badges

2 Comments

Kenreaper Over a year ago

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?

Manashvi Birla Over a year ago

can you please elaborate this condition?

kojow7 · Accepted Answer · 2015-06-10 06:29:12Z

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, 2015 at 6:29

kojow7

11.5k21 gold badges94 silver badges148 bronze badges

1 Comment

Kenreaper Over a year ago

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.

Doni · Accepted Answer · 2015-06-10 06:40:35Z

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, 2015 at 6:40

Doni

6168 silver badges9 bronze badges

2 Comments

kojow7 Over a year ago

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.

Doni Over a year ago

you are correct kojow7... back-end checks is very important, since user can overridden our front-end checks.

Collectives™ on Stack Overflow

Validating Array Form Input for MYSQL Insertion

4 Answers 4

Note:

1 Comment

2 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Note:

1 Comment

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related