Creating and inputting multiple form inserts to mysql using php

Question

I'm fairly new to php and I'm having a hard time figuring out how to make a form where I can have multiple inputs and use php to input all the inputs into a sql table. I set up a table so that each row is a new entry, this way a schedule can be uploaded all at once, instead of 1 by 1.

Here is my current code:

html:

<table id="register">
<form action="processing_games.php" method="post"/>
<tr>
    <td><p>Date<br>(m/dd/yy)</p></td>
    <td><p>Time<br>(h:mm AM/PM)</p></td>
    <td><p>Opponent</p></td>
    <td><p>Location<br>(Rink Name)</p></td>
    <td><p>Conference<br>Game?</p></td>
    <td><p>Home or Away<br>Game?</p></td>
</tr>
<tr>
    <td><input type="date" name="date"></td>
    <td><input type="time" name="time"></td>
    <td><input type="text" name="opponent"></td>
    <td><input type="text" name="location"></td>
    <td><input type="radio" name="conference" value="+"> Conference
    <br>
    <input type="radio" name="conference" value=""> Non-Conference </td>
    <td><input type="radio" name="home_away" value=" vs "> Home
    <br>
    <input type="radio" name="home_away" value=" @ "> Away</td>
</tr>
<tr>
    <td><input type="date" name="date"></td>
    <td><input type="time" name="time"></td>
    <td><input type="text" name="opponent"></td>
    <td><input type="text" name="location"></td>
    <td><input type="radio" name="conference" value="+"> Conference
    <br>
    <input type="radio" name="conference" value=""> Non-Conference </td>
    <td><input type="radio" name="home_away" value=" vs "> Home
    <br>
    <input type="radio" name="home_away" value=" @ "> Away</td>
</tr>
</table>
<br><br>
<center>
<input type="submit" value="Submit"/>
</form>

php:

<?php
include '../connection.php';

$game_date = $_POST['date'];
$game_time = $_POST['time'];
$value3 = $_POST['opponent'];
$value4 = $_POST['location'];
$value5 = $_POST['conference'];
$value6 = $_POST['home_away'];

$value = date('n/j/y', strtotime($game_date));
$value2 = date('g:i A', strtotime($game_time));

$sql = "INSERT INTO schedule (date, time, opponent, location, conference, home_away) VALUES ('$value', '$value2', '$value3', '$value4', '$value5', '$value6')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
    header("Location: http://www.rit.edu/sg/clubhockey/admin/complete.php");
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

mysqli_close($conn); 
?>

Any help is appreciated.

Answer 1

You can transform your html to send post data as "multidimensional" array. Update all of your input names (date, time, oppenent... except submit!) from:

name="date"

to

name="items[0][date]" // for first block of inputs
name="items[1][date]" // for second block of inputs
name="items[n][date]" // for nth block ;)

The key "items" is just as example!

Post your form to processing_games.php and var_dump($_POST). You should see the $_POST array output structure like:

Array
(
[items] => Array
    (
        [0] => Array
            (
                [date] => your input date 1
                [oppenent] => your input openent 1
            )

        [1] => Array
            (
                [date] => your input date 2
                [oppenent] => your input openent 2
            )
    )
)

Then you can iterate through items array ($_POST['items']) an build insert query/queries. Just example:

<?php

$postItems = null;
if (isset($_POST['items'])) {
    $postItems = $_POST['items'];

    // iterate through and execute single queries
    foreach($postItems as $item) {

        // $item should be associative array with keys (date, time, oppenent)
        var_dump($item); // show $item

        $datetime = new \DateTime($item['date']);
        $dateFormatted = $datetime->format('m-d-Y');

        // build query:
        // please DO NOT insert values from POST without escaping, just for explanation!
        $sql = 'INSERT INTO schedule (date, time, opponent, ...) 
            VALUES ("'.$dateFormatted.'", "'.$item['time'].'"
            , "'. $item['opponent'].'", ...)';

        echo $sql . '<br />';
    }
}

?>

  <html>

  <body>
    <form method="post">
      <table>
        <tr>
          <td>
            <p>Date<br>(m/dd/yy)</p>
          </td>
          <td>
            <p>Time<br>(h:mm AM/PM)</p>
          </td>
          <td>
            <p>Opponent</p>
          </td>
          <td>
            <p>Location<br>(Rink Name)</p>
          </td>
          <td>
            <p>Conference<br>Game?</p>
          </td>
          <td>
            <p>Home or Away<br>Game?</p>
          </td>
        </tr>
        <tr>
          <td><input type="date" name="items[0][date]"></td>
          <td><input type="time" name="items[0][time]"></td>
          <td><input type="text" name="items[0][opponent]"></td>
          <td><input type="text" name="items[0][location]"></td>
          <td><input type="radio" name="items[0][conference]" value="+"> Conference
            <br>
            <input type="radio" name="items[0][conference]" value=""> Non-Conference </td>
          <td><input type="radio" name="items[0][home_away]" value=" vs "> Home
            <br>
            <input type="radio" name="items[0][home_away]" value=" @ "> Away</td>
        </tr>
        <tr>
          <td><input type="date" name="items[1][date]"></td>
          <td><input type="time" name="items[1][time]"></td>
          <td><input type="text" name="items[1][opponent]"></td>
          <td><input type="text" name="items[1][location]"></td>
          <td><input type="radio" name="items[1][conference]" value="+"> Conference
            <br>
            <input type="radio" name="items[1][conference]" value=""> Non-Conference </td>
          <td><input type="radio" name="items[1][home_away]" value=" vs "> Home
            <br>
            <input type="radio" name="items[1][home_away]" value=" @ "> Away</td>
        </tr>
      </table>
      <input type="submit" name="submit" value="submit" />
    </form>
  </body>

  </html>

And please: Notice the comment code comment before building insert query! ;)

FYI #1 You can try out prepared statements and bind the values to the statement. No need to escape your values to prevent SQL injections - prepared statements will do this work.

FYI #2, please check this question and its accepted answer: Submitting a multidimensional array via POST with php