multiple insert with php and mysql

Question

I am working on to create multiple insert page where user can choose how many records he needs to insert.

<form name="" action="" method="post">   
<p>Write Down How many rows are you trying to insert<input type="text" name="numb" placeholder=""/></p>
<input type="submit" name="add"/><br>
<?php
if(isset($_REQUEST['add'])){
$add=$_POST['numb'];
for($i=0; $i<$add; $i++) {
    echo "<form method=\"post\" action=\"2.php\" >";

    echo "Water Pump<input type=\"text\" name=\"dap\"/><br>
    Tractor Hiring<input type=\"text\" name=\"tractor\"/><br><form>";
    }   echo "<input type=\"submit\" name=\"save\"/><br>";
}
?>

I am not redirecting to 2nd page. This code is in 2nd page.

if(isset($_REQUEST['save'])){
$insert=$conenction->query("insert into machinery (tractor_hiring,water_pump)VALUES('".$_POST['tractor']."','".$_POST['dap']."')");
if($insert!=true)
echo "nothing happens";
else 
echo "good to go";
}

You do know that `if` tests against boolean conditions without needing an explicit comparison vs. `true`, right? Try not to do things like `if(($insert!=true)==true)` — tadman, Oct 15 '14 at 18:07
Not your question,but very important: never trust user input. Please read http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Stephan Vierkant, Oct 15 '14 at 18:09
This looks [terrifyingly insecure](http://bobby-tables.com/) and for your sake I hope this is not on the public internet. You need to ensure any and all user parameters are [properly escaped](http://bobby-tables.com/php) or you are at serious risk of an application compromise. `$_POST` data **NEVER** goes directly in a query. If you're using PDO, use [prepared statements](http://php.net/manual/en/pdo.prepared-statements.php) to avoid this mess. — tadman, Oct 15 '14 at 18:10
thank you all for suggestions but i am not getting to 2nd page where i can process the form. Its test at final project i will user bind_parm() — user3127648, Oct 15 '14 at 18:14
Why your form doesn't process, is because you don't have a tag, you got
2 times, which is the same for ismael miguel. — JiFus, Oct 15 '14 at 18:26
yes i changed that but still the file is stacked in first page — user3127648, Oct 15 '14 at 18:39

Ismael Miguel · Accepted Answer · 2014-10-15T19:41:38.053

I will show how I would do this, trying to make a walkthough:

Change the field names to machine[][dap] and machine[][tractor].
Using machine[] will create an array, with each field name inside [].
Create a new array, called (for example) $sql, like this:
$sql=array();
Cycle though each $_POST using foreach:
foreach($_POST['machine'] as $v)
Inside the foreach, you will assign the 'built' query piece to $sql:
$sql[]='"'.my_escape($v['dap']).'","'.my_escape($v['tractor']).'"';
notice: my_escape is a fictional function, used to escape the data (like mysql_real_escape_string()).
Run your query, and join the array with it:
$connection->query('insert into machinery (tractor_hiring,water_pump)VALUES('.implode('),(',$sql).')');

Final code (file 1):

<form name="" action="" method="post">   
<p>Write Down How many rows are you trying to insert<input type="text" name="numb" placeholder=""/></p>
<input type="submit" name="add"/><br>
<?php
if(isset($_REQUEST['add'])){
$add=$_POST['numb'];
for($i=0; $i<$add; $i++) {
    echo "<form method=\"post\" action=\"2.php\" >";

    echo "Water Pump<input type=\"text\" name=\"machine[][dap]\"/><br>
    Tractor Hiring<input type=\"text\" name=\"machine[][tractor]\"/><br><form>";
    }   echo "<input type=\"submit\" name=\"save\"/><br>";
}
?>

Final code (file 2):

<?php
if(isset($_REQUEST['save'])){
    $sql=array();
    foreach($_POST['machine'] as $v)
    {
        $sql[]='"'.my_escape($v['tractor']).'","'.my_escape($v['dap']).'"';
    }
    $connection->query('insert into machinery (tractor_hiring,water_pump)VALUES('.implode('),(',$sql).')');
    if($insert!=true)
        echo "nothing happens";
    else 
        echo "good to go";
}
?>

NOTICE: I AVOIDED TO TOUCH THE CODE INDENTATION (changed only on the 2nd file, cause it was terrible). YOU should indent EVERY FILE in a proper way!
Also notice that I used the same fictional function on the final code.

For the reported problem about the 1st page, here are the changes:

The form has no action. Easily fixed by setting action="#".
Only inputs with a value will be sent. <input type="submit" name="add"/> doesn't have a value.
<input type="submit" name="add" value="Add"/>
Notice: the value will also be used as the text to be displayed.
You don't have a closing </form> for the main <form>.

Final result:

<form action="#" method="post">   
<p>Write Down How many rows are you trying to insert<input type="text" name="numb" placeholder=""/></p>
<input type="submit" name="add" value="Add"/><br>
</form>
<?php
if(isset($_REQUEST['add'])){
$add=$_POST['numb'];
for($i=0; $i<$add; $i++) {
    echo "<form method=\"post\" action=\"2.php\" >";

    echo "Water Pump<input type=\"text\" name=\"machine[][dap]\"/><br>
    Tractor Hiring<input type=\"text\" name=\"machine[][tractor]\"/><br><form>";
    }   echo "<input type=\"submit\" name=\"save\"/><br>";
}
?>

The form field naming syntax will result in `tractor` data and `dap` data having auto-incremented/different indexes. In other words, they will not be on the same row despite the intention to keep the data paired. https://stackoverflow.com/q/20184670/2943403 To fix this issue, use `[$i]` instead of `[]` after `name=\"machine` (for both fields). If this was my project, I'd simplify `$_POST` data structure, by naming the fields `tractor[]` and `dap[]` because using `machine` adds unnecessary array depth. — mickmackusa, Jan 04 '20 at 21:52

multiple insert with php and mysql

1 Answers1