foreach to insert multiple rows

1

I am trying to insert into a MySQL table several rows with the user-filled content in an HTML form, when it even does submit.

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

This is what I am using, and you are only inserting the last option, in 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 that is 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>

Can anyone help?

    
asked by anonymous 21.06.2018 / 16:24

1 answer

1

The problem is that in your foreach you are replacing the $sql variable and you are not concatenating the values. Therefore, only one record will always be entered. 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:

// 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);
    
21.06.2018 / 17:25