HTML Form Data to SQL Database

Question

Information

I made a PHP script that connects to my database and creates a record in my database. This works. Now, I added a simple form to this page and I want to make a record in my database based on the input that a user gives.

The PHP script without the form:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "detachering";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO medewerkers (voornaam, achternaam, leeftijd, uurloon, opleidingsniveau)
VALUES ('test', 'user', '20', '192', 'HBO')";

if ($conn->query($sql) === TRUE) {
    echo "Medewerker is aangemaakt";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

This is the form that I want to add to it:

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title></title>
  </head>
  <body>
      <form class="" action="index.html" method="post">
        <input type="text" name="voornaam" placeholder="Voornaam">
        <input type="text" name="achternaam" placeholder="Achternaam">
        <input type="text" name="leeftijd" placeholder="Leeftijd">
        <input type="text" name="uurloon" placeholder="Uurloon">
        <input type="text" name="opleidingsniveau" placeholder="Opleidingsniveau">
        <button type="submit" name="button">Save</button>
      </form>
  </body>
</html>

I personally think that it should be something like:

INSERT INTO medewerkers (voornaam, achternaam, leeftijd, uurloon, opleidingsniveau)
VALUES ('POST_['voornaam']', 'POST_['achternaam']', 'POST_['leeftijd']', 'POST_['uurloon']', 'POST_['opleidingsniveau']');

I haven't done much in PHP, so any help is greatly appreciated!

you get the input vars like so: $vornaam = $_POST['voornaam']; — Jeff
– Jeff, Commented Jan 2, 2018 at 21:02
but you should not put those values directly into your sql-statement, like you've tried. You should use prepared_statements — Jeff
– Jeff, Commented Jan 2, 2018 at 21:04
Please, read about sql injections and prepared statements. You are vulnerable to sql injections!!! Also you should learn about PDO. Since you are learning, learn the latest tech! — user2399035
– user2399035, Commented Jan 2, 2018 at 21:05
WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put $_POST, $_GET or any user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman
– tadman, Commented Jan 2, 2018 at 21:09
It's a concern right now, not later. Do it correctly the first time and you won't get held up on silly mistakes that could easily be avoided. If you skip this and accidentally ship this code the consequences could be catastrophic to your company. — tadman
– tadman, Commented Jan 2, 2018 at 21:10

Honk der Hase · Accepted Answer · 2018-01-02 22:15:35Z

1

Use prepared statements for this. Unfortunately, with mysqli you'll have to use references and cannot just use the POST array directly.

$stmt = $conn->prepare('INSERT INTO medewerkers (voornaam, achternaam, leeftijd, uurloon, opleidingsniveau) VALUES(?, ?, ?, ?, ?)');
if ($stmt) {
    $voornaam = $_POST['voornaam'];
    $achternaam = $_POST['achternaam'];
    $leeftijd = $_POST['leeftijd']
    $uurloon = $_POST['uurloon'];
    $opleidingsniveau = $_POST['opleidingsniveau'];

    $stmt->bind_param('sssss', $voornaam, $achternaam, $leeftijd, $uurloon, $opleidingsniveau);
    $stmt->execute();
}

edit: Since we are talking about it, here is the PDO example (with connection):

try {
    $dsn = 'mysql:host=localhost;dbname=test';
    $pdo = new PDO($dsn, 'root', 'passwd');
    $pdo->exec('SET CHARACTER SET UTF8');
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    $sql = "INSERT INTO medewerkers (voornaam, achternaam, leeftijd, uurloon, opleidingsniveau) VALUES(?, ?, ?, ?, ?)";
    $stmt = $pdo->prepare($sql);
    if ($stmt) {
        $params = [$_POST['voornaam'], $_POST['achternaam'], $_POST['leeftijd'], $_POST['uurloon'], $_POST['opleidingsniveau']];
        $stmt->execute($params);
    }

} catch(Exception $e) {
    echo $e->getMessage();
}

Note that I'm creating a new array for the parameters since I'm not sure if there are more values in the POST.

edited Jan 2, 2018 at 22:15

answered Jan 2, 2018 at 21:17

Honk der Hase

2,5081 gold badge17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

tadman Over a year ago

Good demonstration of bind_param but you can skip the single-use variables and just bind to the $_POST values.

Honk der Hase Over a year ago

Thx, but I think binding the POST directly won't work with mysqli->bind_param, it expects the bound variables as references. PDO would allow it as far as I know.

rpm192 Over a year ago

Thank you Lars, that worked. I got a syntax error at first, but I saw that you missed a semicolon after ['leeftijd'].

tadman Over a year ago

PDO is a lot better, it's true, however you can bind directly to arrays with the caveat that this bind is directly to the value, and switching the containing array variable, such as inside a loop, won't correctly propagate the switch. Since $_POST itself is fixed this isn't a problem.

rpm192 Over a year ago

One last question though, What does the 'sssss' at the start of bind_param() mean?

|

Collectives™ on Stack Overflow

HTML Form Data to SQL Database

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related