CODEWITHSUNDEEP
phpmysqlmysqli
Simon K
Simon K

Reputation: 83

How to insert form values, some with same name, to multiple MySQL rows

I have been searching this for the last couple of hours and there are a few very similar questions and answers but none are quite working for my situation. What I'm trying to do is insert a list of songs set to the same value for two columns, one per row, and with a couple of options set via radio buttons. The latter is the reason why I can't just write all of the songs in a textarea and split them.

This is what the form looks like (obviously not fully designed)

enter image description here

There are only three there now while I get it working, in reality an unlimited amount may be added via javascript.

And the code:

<form action="" method="post">
        <label>Session ID</label>
        <input type="text" name="session-id"><br>
        <label>Songs</label><br>
        <input type="text" name="song-key[]"><input type="checkbox" name="is-partial[]"><input type="checkbox" name="is-jam[]"><br>
        <input type="text" name="song-key[]"><input type="checkbox" name="is-partial[]"><input type="checkbox" name="is-jam[]"><br>
        <input type="text" name="song-key[]"><input type="checkbox" name="is-partial[]"><input type="checkbox" name="is-jam[]"><br>
        <input type="text" name="update-date" value="<?php echo date(" Y-m-d H:i:s ");?>" hidden readonly><br>
        <input type="submit" name="submit" value="Submit">
    </form>

The desired result, assuming the ID has been set and a unique song entered for each of the them, and whatever variety on the radio buttons, would be three table rows. session-id and update-date would all be the same value, the rest would be unique based on entry.

This is what I currently have, but it only inserts the last of the three songs.

for ($i=0; $i < count($_POST['song-key']); $i++ ) {
$sessionkey = mysqli_real_escape_string($connection, $_POST["session-key"]);
$songkey = mysqli_real_escape_string($connection, $_POST["song-key"][$i]);
$partial = mysqli_real_escape_string($connection, isset($_POST['is-partial'][$i])) ? 1 : 0;
$jam = mysqli_real_escape_string($connection, isset($_POST['is-jam'][$i])) ? 1 : 0;
$updated = mysqli_real_escape_string($connection, $_POST["update-date"]);

$sql = "INSERT INTO session_songs (session_key, song_key, is_partial, is_jam, last_updated)
VALUES ('$sessionkey', '$songkey', '$partial', '$jam', '$updated')";
}

What do I need to change to ensure all three (or more) are entered?

Upvotes: 0

Views: 1183

Answers (1)

Don&#39;t Panic
Don&#39;t Panic

Reputation: 41810

If you alter your input names, you can get a more useful $_POST array.

<input type="text" name="songs[0][key]">
<input type="checkbox" name="songs[0][is-partial]">
<input type="checkbox" name="songs[0][is-jam]"><br>

<input type="text" name="songs[1][key]">
<input type="checkbox" name="songs[1][is-partial]">
<input type="checkbox" name="songs[1][is-jam]"><br>

<input type="text" name="songs[2][key]">
<input type="checkbox" name="songs[2][is-partial]">
<input type="checkbox" name="songs[2][is-jam]"><br>

With specified keys, the checkbox type inputs will match up properly with the text inputs, where they way you have it now, they will not*, because only checked checkboxes are submitted to PHP. (Try only checking "is-partial" in the second and "is-jam" in the third row, and then var_dump($_POST), and you'll see that they both have index 0.)

If your form is structured like that, you can insert your records using a foreach loop instead of a for loop.

foreach ($_POST['songs'] as $song) {
    $key = $song['key'];
    $partial = isset($song['is-partial']) ? 1 : 0;
    $jam = isset($song['is-jam']) ? 1 : 0;
    // do the insert inside the loop rather than just building the SQL
}

The reason you're currently only getting the last one is that you're defining the SQL string inside the loop, but without executing the statement in the loop, you'll only get the values from the last iteration of the loop. Move your query execution inside the loop and you should get all three rows.

*unless they are all checked

Upvotes: 2

Related Questions