Rosamunda Rosamunda - 4 months ago 8
SQL Question

PHP: How to add three values into a database in a single foreach loop?

I have an input form with two values as a key, constructed this way:


So, the HTML would look like, for example, like this:

<input name="verificarPago[4-10]" type="text">


Besides that I have the value that the user can type, for example "Juan".

The inputs are created programatically, and I want to save them each three values (in the example: 4, 10, Juan) into a database.

So, when the user submits the form, I thought on using
foreach
in order to access the elements in that array, but my problem is: How may I explode the keys to access the two values separately?

I've tried this (I'm not taking into account security now, I'm trying to prove the concept):

$verificar = $_POST['verificarPago'];
foreach ($verificar as $pago => $curso) {
//I'm pretty much stuck here.
$verificar= array_flip(explode("-", $verificar));

//I want to insert the three items into the database
$verificarPago = "INSERT pagosVerificados SET userID = '$usuario', pagoID = '$pago', cursoID = '$curso'";
$cargarPago = mysqli_query($conectar, $verificarPago);

}


I've tried adding to the form another input type, so the values wouldn't be together, but that way I should do a foreach inside a foreach and that renders duplicated results (and with errors as well).

Answer

Without addressing any architectural issues, adding error-checking, or addressing security problems, here's how to do what you're asking for

$verificar = $_POST['verificarPago'];
foreach ($verificar as $ids => $text) {
    list($pagoID, $cursoID) = explode("-", $ids);

    $sql = "INSERT INTO pagosVerificados (userID, pagoID, cursoID, someVarcharField)
                      VALUES ($usuario, $pagoID, $cursoID, '$text');";
    $cargarPago = mysqli_query($conectar, $sql);

}

This is a tad inefficient though - executing an insert for every iteration. Since mysql supports bulk inserts, you can modify the generation of the query to leverage that.

$verificar = $_POST['verificarPago'];
$rows = array();

foreach ($verificar as $ids => $text) {
    list($pagoID, $cursoID) = explode("-", $ids);
    $rows[] = "($usuario, $pagoID, $cursoID, '$text')";
}

$sql = "INSERT INTO pagosVerificados (userID, pagoID, cursoID, someVarcharField) VALUES "
               . implode(',', $rows)
               . ";";
$cargarPago = mysqli_query($conectar, $sql);

Now it's just one round-trip to the database