CODEWITHSUNDEEP
phpmysqlhtml
Jesse James
Jesse James

Reputation: 35

Multiple insert into Mysql from an html form using php

Thanks so very much for all your help, Mysql DB works , now I am encountering a little problem, hope I'll fix it quickly and a litle help of course, so here My Html form :

<div>Date: 
    <input onclick="ds_sh(this);" name="trans_date" readonly="readonly" style="cursor: text" /><br/><br/>
    Product:
    <select name="product_id []">
        <option value="1">Item1</option>
        <option value="2">Item2</option>
        <option value="3">Item3</option>
        <option value="4">Item4</option>
        <option value="5">Item5</option>
    </select>
    Quantity:
    <input type="text" name="stock_plus []" /><br/>
</div>

This div is repeated 10 times or more, and I use that form to let user add inventory count of selected product

Now when I tried to used PHP to insert rows on my table :

1-Method 1 :

PHP Code:

$product = $_POST['product_id']; $stock_plus = $_POST['stock_plus'];
$Date = $_POST['trans_date']; $limit = count($stock_plus);
for($i=0;$i<$limit;$i++) {
    $product[$i] = mysql_real_escape_string($product[$i]);
    $stock_plus[$i] = mysql_real_escape_string($stock_plus[$i]);
}

$query = "INSERT INTO table (trans_date, product_id, stock_plus)
VALUES ('".$Date."','".$product[$i]."','".$stock_plus[$i]."')";
if(mysql_query($query))
    echo "$i successfully inserted.<br/>";
else
    echo "$i encountered an error.<br/>";

I got : Notice: Undefined offset: 2... and not all the rows are inserted.

Method 2 :

PHP Code:

$trans_date=$_POST['trans_date']; $sql = 'INSERT INTO table
(trans_date, product_id, stock_plus) VALUES ';

for($i = 0;$i < count($_POST['product_id']);$i++) {
    $sql .= "('$trans_date','".$_POST['product_id'][$i]."','".$_POST['stock_plus']i]."')";
    if($i != count($_POST['product_id']) - 1)
    {
        $sql .= ',';  
    } 
}
if (!mysql_query($sql))   {   die('Error: ' . mysql_error());   }

Here no errors but not all the rows are inserted. Can you help me please to see clearier what I missed,regards

Additional :

thanks Travesty3, I look too into myhtml for errors, My HTML body looks exactly like:

<body>
<form action="../inserts/stock_insert.php" method="post">
<div>
<!-- JS Datepicker -->
Date: <input onclick="ds_sh(this);" name="trans_date" readonly="readonly" style="cursor: text" />
<br/>
<!-- User should select product -->
Product:<select name="product_id []">
<option value="1">Item1</option>
<option value="2">Item2</option>
<option value="3">Item3</option>
<option value="4">Item4</option>
<option value="5">Item5</option>
</select>
<!-- User must enter the quantity -->
Quantity: <input type="text" name="stock_plus []" /><br/>
</div> 
<input type="submit" name="Submit" value="Submit" />
</form>
</body>

the div is repeated 10 times, so the result I should have in my DB table is 10 rows insterted (trans_date, product_id(FK),stock_plus) When echoing Mysql errors, there are none.

Upvotes: 2

Views: 4696

Answers (2)

christophmccann
christophmccann

Reputation: 4211

First of all, and most importantly, before you do anything fix the massive security hole in both methods. You are allowing unsanitised user input to be inserted directly into an SQL query which allows SQL injection. You must, without exception, always sanitise input from the user. To do this pass all of the variables through mysql_real_escape_string before you put them in your query. You have sanitised some input, but you dont for example sanitise $date. I assume this is because it is coming from a date picker and you don't see the risk. You should never rely on anything client-side for security because it can always be changed - always include server side validation and sanitisation. A malicious user could, for example, modify the date through their own javascript before posting it to your server.

However, given you are doing the same query multiple times, I would strongly recommend you start using MySQLi or PDO and prepared statements. When you use a prepared statement it is pre-compiled, which means you get a big performance boost when you re-run the same query but only with different data. I would strongly recommend you look that up.

The reason you are getting an undefined offset is because the array index 2 does not exist in your array. Your code looks a bit confusing to me - your for loop excludes your mysql_query. You would need to do the mysql_query within the for loop. You would be better doing a foreach round the product_ids e.g.

$i = -1;
$product_ids = $_POST['product_id'];
$stock_plus = $_POST['stock_plus'];
$date = mysql_real_escape_string($_POST['trans_date']);
foreach($product_ids as $product_id) {
    $product_id = mysql_real_escape_string($product_id);
    $stock = mysql_real_escape_string($stock_plus[++$i]);
    mysql_query("INSERT INTO table (trans_date, product_id, stock_plus) VALUES ({$date}, {$product_id}, {$stock})");
}

Upvotes: 1

Travesty3
Travesty3

Reputation: 14489

You are performing your query outside of your loop, so only one insert is being performed. Move your query inside your for-loop.

Try this:

$product = $_POST['product_id'];
$stock_plus = $_POST['stock_plus'];
$Date = mysql_real_escape_string($_POST['trans_date']);
$limit = count($stock_plus);

for ($i=0; $i<$limit; $i++)
{
    $product[$i] = mysql_real_escape_string($product[$i]);
    $stock_plus[$i] = mysql_real_escape_string($stock_plus[$i]);

    if(mysql_query("INSERT INTO table (trans_date, product_id, stock_plus) VALUES ('{$Date}', '{$product[$i]}', '{$stock_plus[$i]}')"))
        echo "$i successfully inserted.<br/>";
    else
        echo "$i encountered an error.<br/>";
}

Upvotes: 3

Related Questions