Severals names from html to MYSQL

Question

I am doing a basic HTML form to ask people: Name, Surname and Fav Song, I have it working for 1 person but I want that people can input several persons at the the same time, for example 4 persons.

This is my working HTML:

<form action='guarda.php' method='post' class="form-inline">
     
     
      <div class="col-md-4 col-sm-4">
       <div class="form-group">
        <label for="name" class="sr-only">Name</label>
        <input type="text" name="name" class="form-control" id="name" placeholder="Name" required>
       </div>
      </div>
      
      <div class="col-md-4 col-sm-4">
       <div class="form-group">
        <label for="surname" class="sr-only">Surname</label>
        <input type="text" name="surname" class="form-control" id="surname" placeholder="Surname" required>
       </div>
      </div>
     
      <div class="col-md-4 col-sm-4">
       <div class="form-group">
        <label for="song" class="sr-only">Fav Song</label>
        <input type="text" name="cancion[]" class="form-control" id="name" placeholder="Your fav Song">
       </div>
      </div>
     
     <center>
       <input type="submit" class="btn btn-default btn-block" value="Send" >
      </center>
     
     
     
     </form>

And this is my PHP file:

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "database");
 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
 
// Escape user inputs for security
$name = mysqli_real_escape_string($link, $_REQUEST['name']);
$surname = mysqli_real_escape_string($link, $_REQUEST['surname']);
$song = mysqli_real_escape_string($link, $_REQUEST['song']);
 
// attempt insert query execution
$sql = "INSERT INTO table (name, surname, song) VALUES ('$name', '$surname', '$song')";
if(mysqli_query($link, $sql)){
    echo "Records added successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
 
// close connection
mysqli_close($link);
?>

Now I want to be able to input 4 persons at the same time, like:

Name, surname, Song
Name, surname, Song
Name, surname, Song
Name, surname, Song

and input all the info at the same time to the database.

How I can do it?

Thanks

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Oct 17 '17 at 18:15
Use array-style names in the form: `name="name[]"`. Then `$_POST['name']` will be an array, and you can loop over it. — Barmar, Oct 17 '17 at 18:23
I don't know how to loop over the array to send the info to the database tables :( — Juan Sánchez, Oct 17 '17 at 18:46

score 0 · Answer 1 · edited Oct 17 '17 at 19:34

wrap your code in a function so you can call it several times and provide different parameters

e.g

function insert_query($name, $surname, $song) {

// Escape user inputs for security

$name = mysqli_real_escape_string($link, $_REQUEST['name']);

$surname = mysqli_real_escape_string($link, $_REQUEST['surname']);

$song = mysqli_real_escape_string($link, $_REQUEST['song']);

// attempt insert query execution

$sql = "INSERT INTO table (name, surname, song) VALUES ('$name', '$surname', '$song')";

    if(mysqli_query($link, $sql)){    
        echo "Records added successfully.";
    } else{
        echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);    
    }

}

Now you can call the function insert_query($name, $surname, $song) several times and pass in the needed parameters.

score 0 · Answer 2 · answered Oct 17 '17 at 19:26

HTML supports array notation. You should use a array name like persons and use input names as indexes. E.g.:

$persons = $_POST[persons];
foreach ($persons as $person)
{
    $person[surname] <-- you read it like an array and do stuff you need
}

But you should use prepared statements, it's safer and the proper way. See Prepared statements

score 0 · Answer 3 · answered Oct 17 '17 at 20:04

As mentioned above, you really should be using prepared statements so:

// Create connection
$db = new mysqli('localhost', 'root', '', 'database');

// Check connection
if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}

// Define post variables
$name = $_REQUEST['name'];
$surname = $_REQUEST['surname'];
$songs = $_REQUEST['cancion']; // This is an array, and you have both languages here (you have the label as song and the variable as cancion)

// Do some validation here
// Some validation

// Prepare SQL
$stmt = $db->prepare("INSERT INTO table (name, surname, song) VALUES (?, ?, ?)");

// Loop through songs (since first and last name aren't arrays)
foreach ($songs as $song) { // Part of the above validation should make sure this is an array
    // Bind the parameters
    $stmt->bind_param("sss", $name, $lastname, $song);
    // Execute the query for these values
    $stmt->execute();
}

$stmt->close();
$db->close();

You'd want to definitely ensure that the validation is thorough, but this is the basics of parameter binding in MySQLi, but personally, I prefer PDO.

The general idea is that your first and last name are staying consistent and the songs/cancions are a multiselect. In better database design, this would probably be split into a Person table and a Songs table to maintain normal form, but that's beyond this scope of this question.

PHP PDO and MySQLi

Severals names from html to MYSQL

3 Answers3