foreach to insert multiple Rows

Question

I am trying to insert several Rows into a Mysql table with the user-filled content in an HTML form, when it does Submit.

What I need is that when several options are selected in the options of a select, several Rows are created in the table, with the options chosen, individually, and the rest of the fields, remain the same.

This is what I’m using, and you’re only inserting the last option into a single Row, contrary to what I want.

$event = mysqli_real_escape_string($link, $_REQUEST['event']);
$date = mysqli_real_escape_string($link, $_REQUEST['date']);
$local = mysqli_real_escape_string($link, $_REQUEST['local']);
$disc = mysqli_real_escape_string($link, $_REQUEST['disc']);
$username = mysqli_real_escape_string($link, $_POST['username']);

foreach($_POST['username'] as $username){
    $sql = "INSERT INTO events (event, date, local, disc, username) VALUES ('$event','".date('d-m-Y', strtotime($date))."', '$local', '$disc', '$username')";
}

if(mysqli_query($link, $sql)){
    mysqli_close($link);
    header('Location: ../events.php');
    exit;
} else {
   echo "ERRO: Não foi possivel inserir o Evento. $sql. " . mysqli_error($link);
}

The html form being processed is as follows::

<form action="insert/insertEvents.php" id="newEvent" method="post">
                                        <div class="form-group">
                                            <label for="user">Utilizador</label>
                                            <br>
                                            <select class="selectpicker form-control" name="username[]" multiple>
                                            <?php
                                                while ($row = mysqli_fetch_array($query2))
                                                { echo ' 

                                                <option>'.$row['username'].'</option>';

                                                }
                                            ?>
                                            </select>
                                        </div>
                                        <div class="form-group">
                                            <label for="nameEvent">Evento</label>
                                            <br>
                                            <input type="text" name="event" class="form-control" id="event">
                                        </div>
                                        <div class="form-group">
                                            <label for="reportDate">Data</label>
                                            <br>
                                            <div class='input-group' id='datetimepicker1'>
                                                <span class="input-group-addon">
                                                    <span class="glyphicon glyphicon-calendar"></span>
                                                </span>
                                                <input type='text' name="date" data-date-format="DD-MM-YYYY" class="form-control" />
                                            </div>
                                        </div>
                                        <div class="form-group">
                                            <label for="namefat">Local</label>
                                            <br>
                                            <input type="text" name="local" class="form-control" id="local" placeholder="ex: Porto">
                                        </div>
                                        <div class="form-group">
                                            <label for="namefat">Descrição</label>
                                            <br>
                                            <textarea rows="12" name="disc" class="form-control" id="disc" form="newEvent"></textarea>
                                        </div>   
                                            <button type="submit" class="btn btn-primary">Adicionar</button>
                                    </form>

Someone can help?

Answer 1

The problem is that in your foreach you are replacing the variable $sql and is not concatenating the values. Therefore, only one record will always be inserted. In this case, only the last one the user selected.

My suggestion:

Replace this:

foreach($_POST['username'] as $username){
    $sql = "INSERT INTO events (event, date, local, disc, username) VALUES ('$event','".date('d-m-Y', strtotime($date))."', '$local', '$disc', '$username')";
}

for this reason:

// string inicial
$sql = "INSERT INTO events (event, date, local, disc, username) VALUES ";

foreach($_POST['username'] as $username){
    // concatenando os valores
    $sql .= "('$event','".date('d-m-Y', strtotime($date))."', '$local', '$disc', '$username'),";
}
// retira a última vírgula
$sql = substr($sql, 0, -1);