my PHP won't post to database while using w3schools example

Question

I have been breaking my head around this html/php/mysqli thing and I can't get it to work. I used several echo statements to see what type of error I am facing but nothing shows up when I am trying to post data into my database.

I have used echo $_POST['name of input']; , print_r($_POST); and only on the 1st one I can see my post. So I think it is posting correctly, right?!

I for some strange reason can't find the problem in my code. I have searched for quiet some time on the web but with little to no result.

This is my HTML:

<html>
<head><title>Test2017</title></head>
<body>
    <form action="insert.php" method="post">
        <table width="400" border="0" cellspacing="10">
            <tr>
                <td>voornaam:</td>
                <td><input type="text" name="voornaam"></td>
            </tr>

            <tr>
                <td>roepnaam</td>
                <td><input type="text" name="roepnaam"></td>
            </tr>

            <tr>
                <td>tussenvoegsel</td>
                <td><input type="text" name="tussenvoegsel"></td>
            </tr>

            <tr>
                <td>achternaam</td>
                <td><input type="text" name="achternaam"></td>
            </tr>

            <tr>
                <td><input type="submit" value="registreren!"></td>
            </tr>
        </table>
    </form>
</body>
</html>

and this my insert.php, and also at the VALUES i have tried "''",'' and "" but non of that worked.

<?php

$connect=mysqli_connect("localhost","root","usbw","test");

//check connection
if (mysqli_connect_errno()){
        echo 'Failed to connect to MySQL:' . mysqli_connect_error();
}

$voornaam= mysqli_real_escape_string($connect, $_POST['voornaam']);
$roepnaam= mysqli_real_escape_string($connect, $_POST['roepnaam']);
$tussenvoegsel= mysqli_real_escape_string($connect, $_POST['tussenvoegsel']);
$achternaam= mysqli_real_escape_string($connect, $_POST['achternaam']);

$sql="INSERT INTO user (voornaam,roepnaam,tussenvoegsel,achternaam) VALUES ('$voornaam','$roepnaam','$tussenvoegsel','$achternaam')";

if (!mysqli_query($connect,$sql)) {
        die('Error: ' . mysqli_error($connect));
}
echo "1 record added";

mysqli_close($connect);
?>

You guys are my only help, because I am pulling my hair out for this.

Thank you in advance!

I have typed the HTML code first and I have pasted it everywhere else even in the database. So I would not have a problem like that. It is all lowercase.

Don't use that tutorial. Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use prepared statements with bound parameters, via either the [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) drivers. [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. — Alex Howansky, Jun 28 '17 at 19:35
ok, well it was my intention to not be vunrable againts sql injections. i am going to read the post you provided me — Gandalf, Jun 28 '17 at 19:36
Note, the code does use `mysqli_real_escape_string()` but that is not always enough. — Alex Howansky, Jun 28 '17 at 19:37
here i though im going to learn something new with mysqli, because the last time i coded some mysql it was quiet some time ago. so PDO is the way to go?! — Gandalf, Jun 28 '17 at 19:40
Either is fine, but you must use parameterised and bound queries in either one to be safe from SQLInjection — RiggsFolly, Jun 28 '17 at 19:42
I think parameterised queries are easier to work with in PDO than in mysqli. — Don't Panic, Jun 28 '17 at 19:43
Suggestion: ___Before you throw the baby out with the bath water___ Add `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to the top of your script. This will force any `mysqli_` errors to generate an Exception that you can see on the browser and other errors will also be visible on your browser. — RiggsFolly, Jun 28 '17 at 19:43
@RiggsFolly well, this sets the boys from the man, i feel such like a rookie it is not normal :( and how does one parameterised an mysqli? — Gandalf, Jun 28 '17 at 19:45
The PHP docs has a little tutorial on that. http://php.net/manual/en/mysqli.quickstart.prepared-statements.php — Don't Panic, Jun 28 '17 at 19:46
@Don'tPanic at example #3 im completely lost with what they mean. — Gandalf, Jun 28 '17 at 19:48
You should remove the $connect variable from the mysqli_real_escape string. This function should only be called with one parameter only. — Gerrit Luimstra, Jun 28 '17 at 19:48
@RiggsFolly where exactly must i put it because i have put it above the $connect but nothing happend, i did refresh the page. — Gandalf, Jun 28 '17 at 19:50
Change `mysqli_real_escape_string($connect, $_POST['voornaam']);` to just `mysqli_real_escape_string($_POST['voornaam']);` on all occurences of this function. — Gerrit Luimstra, Jun 28 '17 at 19:51
@GerritLuimstra **Please check your facts before suggesting complete rubbish** — RiggsFolly, Jun 28 '17 at 19:52
@Don'tPanic i can for some reason not understand the PDO past example #3 and im feeling like im walking blindfolded — Gandalf, Jun 28 '17 at 19:56

RiggsFolly · Answer 1 · 2017-06-28T20:06:55.167

0

Ok, we have probably totally confused you now, so try this

<?php
ini_set('display_errors', 1); 
ini_set('log_errors',1); 
error_reporting(E_ALL); 
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

if ($_SERVER['REQUEST_METHOD'] == 'POST') {

    $connect=mysqli_connect("localhost","root","usbw","test");
    if (mysqli_connect_errno()){
        echo 'Failed to connect to MySQL:' . mysqli_connect_error();
    }

    // using 4 ? one for each column value in the query
    $sql="INSERT INTO user 
                    (voornaam,roepnaam,tussenvoegsel,achternaam) 
            VALUES (?,?,?,?)";

    $stmt = $connect->prepare($sql);
    // pass the actual data for each parameter, in order
    // the 'ssss' in this case denotes that all 4 params are strings
    // they can be s=string, i=integer,b=blob, d=decimal
    $stmt->bind_param('ssss', 
                        $_POST['voornaam'],
                        $_POST['roepnaam'],
                        $_POST['tussenvoegsel'],
                        $_POST['achternaam']
                    );
    $result = $stmt->execute();
    if ( $result ) {
        echo "1 record added";
    } else {
        echo $connect->error;
    }
}
?>

edited Jun 28 '17 at 20:06

answered Jun 28 '17 at 19:58

RiggsFolly

93,638
21
103
149

why are the question marks needed at the values? and the 'ssss' at tje bind param? – Gandalf Jun 28 '17 at 20:02
One `?` for each value you are parameterising – RiggsFolly Jun 28 '17 at 20:03
The '?'s denote where the parameters will be substituted. – D Lowther Jun 28 '17 at 20:03
1

One of either `sibd` to identify the data type of that parameter, I assumed 4 strings in this case – RiggsFolly Jun 28 '17 at 20:04
so i tried your code and it does post to the page, what i get is this: prepare($sql); $stmt->bind_param('ssss', $_POST['voornaam'], $_POST['roepnaam'], $_POST['tussenvoegsel'], $_POST['achternaam'] ); $result = $stmt->execute(); if ( $result ) { echo "1 record added"; } else { echo $connect->error; } } ?> – Gandalf Jun 28 '17 at 20:05
@RiggsFolly ok i had some feeling about it, but now i am sure, just changes the questionmarks into $voornaam, etc. that is correct right? because i am still getting the same "error" as shown above with data inserted – Gandalf Jun 28 '17 at 20:07
@RiggsFolly first i did not know what the sibd ment but now i do, and yes they are all strings. but my data still is not being posted to my database – Gandalf Jun 28 '17 at 20:29
@Gandalf Can you post a var_dump result of the $stmt variable after you've prepared it? So we can see the SQL command. – Gerrit Luimstra Jun 28 '17 at 20:30
Then we had better see your schema for this table – RiggsFolly Jun 28 '17 at 20:35
@GerritLuimstra i tried what you said i used var_dump($post), and this was the result: prepare($sql); $stmt->bind_param('ssss', var_dump $_POST['voornaam'], var_dump $_POST['roepnaam'], var_dump $_POST['tussenvoegsel'], var_dump $_POST['achternaam'] ); $result = $stmt->execute(); if ( $result ) { echo "1 record added"; } else { echo $connect->error; } } ?> – Gandalf Jun 28 '17 at 20:42
I think he wanted to see the output from the `var_dump()` statements – RiggsFolly Jun 28 '17 at 20:43
so this: var_dump($_POST), var_dump($_POST), var_dump($_POST), var_dump($_POST) is not the correct way to var_dump ? – Gandalf Jun 28 '17 at 20:48
Each `var_dump()` should generate some output on the browser page – RiggsFolly Jun 28 '17 at 20:50
but am i doing it right or wrong, because it is my first time using var_dump, and i really want to help you guys so you can give me an answer of some sort, maybe i should move this comment to an chat, because i really want to avoid extended discussions in comments, just found out i need 20 rep :( – Gandalf Jun 28 '17 at 20:53
After the `$stmt->bind_param(....);` in this code.. Add a `var_dump($stmt);` and show us the results here. – Gerrit Luimstra Jun 28 '17 at 21:29

score 0 · Answer 2 · answered Jun 28 '17 at 20:02

I reformatted your original example to use a prepared statement, as this is safer for handling user generated input. I added a try catch around your code to attempt to raise visibility on whatever error you are running into

<?php
// ensure reporting for mysql is on.
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
  // Subbing out what you had for db connection to illustrate what each 
  // of those parameters should point to on your local db
  $database = new mysqli('host', 'user', 'password', 'db_schema');

  // guessing on whether these are strings.
  $voornaam = filter_input(INPUT_POST, 'voornaam', FILTER_SANITIZE_STRING);
  $roepnaam = filter_input(INPUT_POST, 'roepnaam', FILTER_SANITIZE_STRING);
  $tussenvoegsel = filter_input(INPUT_POST, 'tussenvoegsel', FILTER_SANITIZE_STRING);
  $achternaam = filter_input(INPUT_POST, 'achternaam', FILTER_SANITIZE_STRING);

  // Formatting for readability, parameterized query
  $query = "INSERT INTO user (
    voornaam, 
    roepnaam, 
    tussenvoegsel, 
    achternaam
  ) VALUES ( ?, ?, ?, ?)";

  // prepare query statement
  $stmt = $database->prepare($query);
  // bind parameters and types to statement
  $stmt->bind_param('ssss', $voornaam, $roepnaam, $tussenvoegsel, $achternaam);
  // execute
  $stmt->execute();

  echo 'Records added: ' . $stmt->affected_rows;
  $stmt->close();

  $database->close();

} catch (Exception $e) {
  // basic print error to screen error handling, not ideal for
  // anything other than testing :)
  echo $e->getCode() . ' - ' . $e->getMessage();
}

my PHP won't post to database while using w3schools example

2 Answers2