Inserting multiple html form arrays into mysqli using php

Question

I have an HTML form that passes multiple arrays into php to be inserted into a MySQL DB. On that end everything works fine and I can retrieve the data in PHP. Here’s the data_in.php:

    <?php 
$z=0; 
if(isset($_POST['newarr'])){
$z = $_POST['newarr'];
}   
        if($z == 0){ 
        echo'
        <div id="d1">Add between 1 and 25 items<br/></div>
        <form method="post" action="">
        <input type=number name=newarr min=1 max=25 />
        <button type=submit value=submit>Submit</button>    
        </form>
        ';
        }     
                else {
                    echo '
                        <form action="test.php" method="post">
                        <table for=record>
                        <th for=ww>Item Number</th>
                        <th for=c>UPC</th>
                        <th>Name</th>
                        <th>Size</th>
                        <th>Qty</th>
                        <th>Category</th>
                        ';
                                for($i=1; $i<=$z; $i++){ 
                                    echo'   <tr>

                                        <td>'.$i.'</td>                                         
                                        <td><input type="text" name="upc[]" value="" size="20" placeholder="Enter UPC" /></td>
                                        <td><input type="text" name="name[]" value="" size="20" placeholder="Enter Name" /></td>

                                                    <td>
                                                    <select name="size[]">
                                                    <option value="">SELECT ONE</option>
                                                    <option value="6pp">6 pack/pouch</option>
                                                    <option value="8pp">8 pack/pouch</option>
                                                    <option value="10pp">10 pack/pouch</option>
                                                    <option value="12pp">12 pack/pouch</option>
                                                    <option value="24pp">24 pack/pouch</option>
                                                    <option value="325ml">325ml BTL</option>
                                                    <option value="750ml">750ml BTL</option>
                                                    <option value="1lt">1 Litter BTL</option>
                                                    <option value="1.75lt>1.75 Litter BTL</option>
                                                    <option value="1gal>1 Gallon BTL</option>
                                                    <option value="2gal>2 Gallon BTL</option>
                                                    <option value="5gal>5 Gallon BTL</option>   
                                                    </select>                   
                                                    </td>

                                        <td><input type="number" name="qty[]" value="" min="1" max="100" placeholder="Enter QTY" /></td>

                                                    <td>
                                                    <select name="type[]">
                                                    <option value="">SELECT ONE</option>
                                                    <option value="1">Mixers</option>
                                                    <option value="2">Rum</option>
                                                    <option value="3">Whisky</option>
                                                    <option value="4">Vodka</option>
                                                    <option value="5">Gin</option>
                                                    <option value="6">Tequila</option>
                                                    <option value="7">Beers</option>
                                                    <option value="8">Cognac</option>
                                                    <option value="9">Wine</option>
                                                    <option value="10">Champagne</option>
                                                    </select>                   
                                                    </td>                  
                                                   </tr>
                                                    ';
                                    }
                                        echo '</table>
                                            <div for=select>
                                            <input type="reset" value="clear" />
                                            <input type="submit" value="submit" />
                                            </div>
                                        </form>';
                }
        ?>

The problem I’m having is inserting the multiple arrays into MySQL. All I’ve managed to do is retrieve the data and echo it, var_dump it. But I can’t figure out a way to insert it correctly.

Here’s my ins.php:

<?php 
include_once 'includes/admin_connect.php';


//$ins = "INSERT INTO products(UPC,product_name,product_size) VALUES";
$upc = mysqli_real_escape_string($mysqli, implode(',',$_POST['upc']));
$name = mysqli_real_escape_string($mysqli, implode(',',$_POST['name']));
$size = mysqli_real_escape_string($mysqli, implode(',',$_POST['size']));

$nArray = array(
'upc' => $upc, 
'name' => $name, 
'size' => $size,
); 

//var_dump($nArray);

foreach($nArray as $row => $vals) { 

    $newArr = implode(',',array_keys($nArray));


$ins1 = 'INSERT INTO products ('.$newArr.') VALUES ('.$vals.')';

}   

$query = mysqli_query($mysqli,$ins1) or die(mysqli_error($mysqli));

?>

That particular code gives me:

Unknown column '6pp' in 'field list'

I know that means it’s getting to the first select field and it’s inserting it as a table, but I don't know how to fix it.

Jacer Omri · Accepted Answer · 2014-06-03 01:23:33Z

1

Try this:

<?php 
include_once 'includes/admin_connect.php';

//a callback to use mysqli connection to escape strings
$mysqli_escape = function($str) use ($mysqli) {
   return mysqli_real_escape_string($mysqli, $str);
};

//escape values in arrays
$upc = array_map($mysqli_escape, $_POST['upc']);
$name = array_map($mysqli_escape, $_POST['name']);
$size = array_map($mysqli_escape, $_POST['size']);

for($i=0;$i<count($upc);$i++) { 
   $ins1 = 'INSERT INTO products (UPC,product_name,product_size) VALUES (\''.$upc[$i].'\', \''.$name[$i].'\', \''.$size[$i].'\')';
   $query = mysqli_query($mysqli,$ins1) or die(mysqli_error($mysqli));
}
?>

edited Jun 3, 2014 at 1:23

answered Jun 3, 2014 at 0:17

Jacer Omri

1,78322 silver badges37 bronze badges

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

4 Comments

user3701157 Over a year ago

Throws error: Unknown column 'CocaCola' in 'field list' which is the value I inserted for product_name on the first row.

user3701157 Over a year ago

Yup, it works, what do you mean by quoting the values (I see what you did differently, but don't understand the "semantics" behind it). Also, by going through a for loop in this case wouldn't it be a problem for long db entries?

Jacer Omri Over a year ago

you can concatenate VALUES() in the same query so you can send one query to the database server at the end. What i did is basically add quotes for the strings in the query, a values without quotes ' is considered as a column name, that's why you get these errors

user1978142 Over a year ago

@user3701157 if this did helped you, you can kindly upvote/accept this answer

Collectives™ on Stack Overflow

Inserting multiple html form arrays into mysqli using php

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related