how to send multiple values with multiple select input-CodePudding

I need to know which will be better to insert multiple values into table with multiple select input with also more than one value.

Is better with one value sepparated with "|" each value or is better with data-id, date-price, etc?

Here the input with the first form:

<select  name="toBuy[]" id="multi" multiple="multiple" style="width:100%">
    <?php
        $sql = $conn->prepare("SELECT id, name, price FROM ITEMS WHERE active != 0");
        $sql->execute();
        while($row = $sql->fetch(PDO::FETCH_ASSOC)) {
            echo '<option value="'.$row['name'].'|'.$row['id'].'|'.$row['price'].'">'.$row['name'].'</option>';
        }
    ?>
</select>

Here the input with the second form:

<select  name="toBuy[]" id="multi" multiple="multiple" style="width:100%">
    <?php
        $sql = $conn->prepare("SELECT id, name, price FROM ITEMS WHERE active != 0");
        $sql->execute();
        while($row = $sql->fetch(PDO::FETCH_ASSOC)) {
            echo '<option value="'.$row['name'].'" data-id="'.$row['id'].'" data-price="'.$row['price'].'">'.$row['name'].'</option>';
        }
    ?>
</select>

Now in the insert file, how can I add those value into the table?

        $sql2 = "INSERT INTO PAID (idItem, name, price, idUser)
         VALUES ";
        $insertQuery2 = array();
        $insertData2 = array();
        foreach ($_POST['id'] as $i => $item) {

            //WITH THE FIRST FORM
            $result = explode('|', $_POST['toBuy']);
            $result.[0];
            $result.[1];
            $result.[2];

            $insertQuery2[] = '(?, ?, ?, ?)';
            $insertData2[] = $result.[0][$i];
            $insertData2[] = $result.[1][$i];
            $insertData2[] = $result.[2][$i];
            $insertData2[] = $idUser;


            //SECOND FORM

            $insertQuery2[] = '(?, ?, ?, ?)';
            $insertData2[] = $_POST['id'][$i];
            $insertData2[] = $_POST['name'][$i];
            $insertData2[] = $_POST['price'][$i];
            $insertData2[] = $idUser;
        }
        if (!empty($insertQuery2)) {
            $sql2 .= implode(', ', $insertQuery2);
            $stmt2 = $conn->prepare($sql2);
            $stmt2->execute($insertData2);
        }

CodePudding user response：

You only need to use the item_id and user_id for this one. Data related to the item is already stored in your item table. What you need to do here is push all the selected item_id's into an array and use a loop when inserting values.

CodePudding user response：

First, as mentioned before, you only need:

echo '<option value="'.$row['id'].'">'.$row['name'].'</option>';

With the multiple option you should get an array of chosen ids. Then your SQL string could be:

$sql = "INSERT INTO PAID (idItem, name, price, idUser) ";
$sql.= "SELECT id, name, price, " . $idUser;
$sql.= "FROM ITEMS ";
$sql.= "WHERE id IN (" . implode(', ', $_POST['toBuy'] . ')');

Voila, this should do it. Maybe there's an error in it, I coded it on the fly without testing, but maybe you get the idea.

See here: https://www.w3schools.com/SQL/sql_insert_into_select.asp