how to insert multiple rows to mysql

Question

i am trying for ages but can not get this working.

So far this is working perfect

<?
                if ($_POST['newhour_start']!=""&&$_POST['newhour_end']!="") 
                {
                    $inserthour = mysql_query("INSERT INTO hour 
                    (hour_start,hour_end,hour_day) VALUES 
                    ('".$_POST['newhour_start']."','".$_POST['newhour_end']."','".$_POST['newhour_day']."')");
                }

?>
    <td colspan="5">
        Start:<input name="newhour_start" type="text" id="newhour_start" > 
        End: <input name="newhour_end" type="text" id="newhour_end" >
        <input name="newhour_day" type="hidden" id="newhour_day" value="Monday" >
    </td>

My question is how can i add another 5 sets of start and end inputs to this code?

I know i can just add another input names, if statements and change their names but i want to learn the proper way.

You might want to learn about [Little Bobby Table](http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain)... — Denis de Bernardy, May 12 '11 at 16:11
yep. re Denis's comment - See also http://stackoverflow.com/questions/5980766/why-is-this-sql-code-insecure — Spudley, May 12 '11 at 16:13

Michael J.V. · Accepted Answer · 2011-05-12T16:22:18.747

2

You use an array.

So how to use an array? In HTML, you need this:

Start #1:<input name="newhour_start[]" type="text" /> 
End #1: <input name="newhour_end[]" type="text"  />

Start #2:<input name="newhour_start[]" type="text" /> 
End #2: <input name="newhour_end[]" type="text"  />

Start #3:<input name="newhour_start[]" type="text" /> 
End #3: <input name="newhour_end[]" type="text"  />

And in PHP you need this:

if(isset($_POST['newhour_start']) && isset($_POST['newhour_end']))
{
    foreach($_POST['newhour_start'] as $index => $start)
    {
        $hour_start = $start; // now this is the fun part - you CLEAN this string to avoid SQL injection
        $hour_end = $_POST['newhour_end'][$index]; // clean this one too

        $insert[] = "('$hour_start', '$hour_end')";
    }

    $query = "INSERT INTO table (first_column, secon_column) VALUES ". implode(',', $insert);

}

I didn't use your table structure in this example, but I think it's simple enough so that you can learn and modify it.

edited May 12 '11 at 16:22

answered May 12 '11 at 16:14

Michael J.V.

5,499
1
20
16

i have got a small proplem now. even if the fields are empty it is inserting into database. what can i do that will only insert into if it is not empty – eMRe May 12 '11 at 17:16
[code] if(isset($_POST['newhour_start']) && isset($_POST['newhour_end'])) { $newhour_day = "Monday"; foreach($_POST['newhour_start'] as $index => $start) { $newhour_start = $start; // $newhour_end = $_POST['newhour_end'][$index]; // clean this one too $insert[] = "('$newhour_start', '$newhour_end', '$newhour_day')"; } $query = "INSERT INTO hour (hour_start, hour_end, hour_day) VALUES ". implode(',', $insert); $inserthour = mysql_query($query) or die(mysql_error()); } [/code] – eMRe May 12 '11 at 17:17
I have done this way. will it be ok? for ($i=0; $i – eMRe May 12 '11 at 19:26

score 0 · Answer 2 · answered May 12 '11 at 16:16

Don't ever insert values in your Database without escaping them!

The proper way of doing this would be:

Add your Fields or whatever to your HTML-Form.
Use the MySQLi-class in PHP to access your Database (it's Object Oriented).
Create a PreparedStatement for your Insert
Loop over your given Fields and bind the parameters to your PreparedStatement
Execute the Statement
Close the Database Connection.

how to insert multiple rows to mysql

2 Answers2