Insert database not working

Question

Hi Im working on a school project and i cant insert this into the database

What am I doring wrong? I dont have any error`s so I im stuck. Please help me. I do have a connection to the database. But the things I insert into the form do not show in to the databse.

$servername = "localhost";
$username = "root";
$password = "root123";
$dbname = "ToetsPro";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
    $query = "INSERT INTO `opleiding` (`id`,
                                   `opleiding`,
                                   `locatie`) 
              VALUES              (NULL,
                                   '".$_POST["opleiding"]."',
                                   '".$_POST["locatie"]."');";
    //echo $query; exit();                     
    $result = mysqli_query($conn, $query);

    $id = mysqli_insert_id($conn);

?>
<!DOCTYPE HTML> 

<form id="register" action="" method="post">
    <table>
        <tr>
            <td>Opleiding: </td>
            <td><input type="text" name="opleiding"></td>
        </tr>
        <tr>
            <td>Locatie: </td>
            <td><input type="text" name="locatie"></td>
        </tr>
        <tr>
            <td></td>
            <td><input type="submit" name="submit"></td>
        </tr>
    </table>
</form>

<a href="index.php">Terug naar de homepage</a>

Is id an auto increment field? Remove it from that query. Else ID cannot be NULL — user2659982, Apr 10 '17 at 08:48
For one thing your code is wide open to SQL injection, so you're not even really controlling what SQL code you execute in the first place. Additionally, you should use `mysqli_error($conn)` to check for errors if `mysqli_query` is returning false. It's *probably* failing because you're trying to insert `NULL` for an `id`, and it's unlikely that an identifying column allows nulls. — David, Apr 10 '17 at 08:50
You do not have any error because you do not check for any. Check for sql errors after calling `mysqli_query()` using `mysqli_error()`. — Shadow, Apr 10 '17 at 08:50
if you really want to check your query. copy the output of your `echo $query;` and run that query in your phpmyadmin or workbench or whatever sql software you're using. — hungrykoala, Apr 10 '17 at 09:01

score 1 · Accepted Answer · edited May 23 '17 at 11:46

What am I doring wrong? I dont have any error`s so I im stuck.

well there are few things you doing wrong.

one of the very first things I have noticed is that you are mixing up Object oriented style and Procedural style doing so might confuse you in the long run.

here your db connection

$conn = new mysqli($servername, $username, $password, $dbname);

You using mysqli object oriented style

Then here : $result = mysqli_query($conn, $query); You using procedural style. I suggest that you only stick with one style, in that way you can easily read,organize and maintain your code.

two : You might be making an error with your insert statement, id then inserting a null on the ID that might be the problem, if your id is an auto_increment better not even include it within your query.

three You are writing a dangerous code, that will harm your application in the long run. you are directly inject $_POST values in your query, that might dangerous and it leave your application wide open to sql injections

You should learn to use prepared statements, with mysqli or PDO prepared statements.

This is how your code should look :

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

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

if (isset($_POST['submit'])) {
    //check and validate your inputs

    $opleiding = $_POST['opleiding'];
    $locatie   = $_POST['locatie'];


    $query = $conn->prepare("INSERT INTO `opleiding` (opleiding,locatie)VALUES(?,?)");
    $query->bind_param("ss", $opleiding, $locatie);

    if ($query->execute()) {

        echo "success";
        $id->insert_id;
    } else {

        echo "Error :" . $conn->error;
    }
}

?>
<!DOCTYPE HTML> 

<form id="register" action="" method="post">
    <table>
        <tr>
            <td>Opleiding: </td>
            <td><input type="text" name="opleiding"></td>
        </tr>
        <tr>
            <td>Locatie: </td>
            <td><input type="text" name="locatie"></td>
        </tr>
        <tr>
            <td></td>
            <td><input type="submit" name="submit"></td>
        </tr>
    </table>
</form>

<a href="index.php">Terug naar de homepage</a>

Upvoted for showing a secure query process. @Kenneth If this isn't the accepted answer, please explain what isn't working as expected and perhaps post the database structure in your question. — mickmackusa, Apr 10 '17 at 09:52

score 0 · Answer 2 · answered Apr 10 '17 at 09:36

0

I think first of all you should use mysqli_real_escape_string() for every variable data filtering then your query should like this way:

 $query = "INSERT INTO `opleiding` (`id`,`opleiding`,`locatie`) VALUES (NULL,'".$_POST['opleiding']."','".$_POST['locatie']."')";

answered Apr 10 '17 at 09:36

Abdur Rahim

72
6

1

i think you should demonstrate how he should use `mysqli_real_escape_string()` – Masivuye Cokile Apr 10 '17 at 09:37

Insert database not working

2 Answers2