html form is connected to mysql, but it isn't submitting any data nor it is creating empty rows

Question

Update Oct 11 2020

I tried this code:

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$connect = mysqli_connect("localhost", "root", "", "sip-krl");
 
// Check connection
if($connect === false){
    die("ERROR: Koneksi gagal " . mysqli_connect_error());
}

$id_jenisPembayaran = $_POST['id_jenisPembayaran'];
$nama_jenisPembayaran = $_POST['nama_jenisPembayaran'];
 
// Attempt insert query execution
$sql = "INSERT INTO jenispembayaran (id_jenisPembayaran, nama_jenisPembayaran) VALUES ('$id_jenisPembayaran', '$nama_jenisPembayaran')";
if(mysqli_query($connect, $sql)){
    echo "Data berhasil dimasukkan";
} else{
    echo "ERROR: Data gagal masuk ke basis data $sql. " . mysqli_connect_error();
}
 
// Close connection
$connect->close();

header("location:/skripsi-manual/data-jenisPembayaran.php");
?>

It works perfectly. However, this also makes my program vulnerable to SQL Injection.

I'm stumped as why this (the code above) works but the code below gets me nowhere?

Could anyone show me the why? Thanks in advance.

---

I'm making a form to input type of payment data to my database. The codes are following:

.php file = input-jenisPembayaran.php

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "sip-krl";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

$id_jenisPembayaran = mysqli_escape_string ($conn, $_POST['id_jenisPembayaran']);
$nama_jenisPembayaran = mysqli_escape_string($conn, $_POST['nama_jenisPembayaran']);

$sql = "INSERT INTO jenispembayaran (id_jenisPembayaran, nama_jenisPembayaran);
VALUES (?,?)";

$stmt = mysqli_stmt_init($conn);

if(!mysqli_stmt_prepare($stmt, $sql)){
    echo "Error.";
} else{
    mysqli_stmt_bind_param($stmt, "is", $id_jenisPembayaran, $nama_jenisPembayaran);
    mysqli_stmt_execute($stmt);
}

header("location:/skripsi-manual/jenispembayaran.php?tambah-data=success");

$conn->close();
$stmt->close();
?> 

Form: jenispembayaran.php

<div class="content">
        <form action="./assets/php/input-jenisPembayaran.php" method="POST">
           <article class="card">
                <h4> Masukkan Jenis Pembayaran </h4>
                <br>
                <br>
                <label for="id_jenisPembayaran">Kode Jenis Pembayaran:</label>
                <input type="text" name="id_jenisPembayaran" id="id_jenisPembayaran">
                <br>
                <br>
                <label for="nama_jenisPembayaran">Nama Jenis Pembayaran:</label>
                <input type="text" name="nama_jenisPembayaran" id="nama_jenisPembayaran">
                <br>
                <br>
                <button input type="submit" value="submit">Masukkan</button>
            </article>
        </form>
    </div>

Database structure:

dbName: sip-krl => table: jenispembayaran => tableRows: id_jenisPembayaran(int), nama_jenisPembayaran(varchar)

"input-jenisPembayaran.php" manages to connect my form to the database. But it fails to insert the data I typed in.

Could anyone show me where my code went wrong and show me how to solve this?

Thank you in advance.

Answer 1

I've tried with your code and with my limited PHP knowledge, I couldn't make it work. Also I did find a tutorial similar to your codes but I also couldn't make it work. However, I found another tutorial that works and try to emulate what you're doing with this tutorial instead and I've managed to perform the process that you desire with this code below:

<?php
/* Database credentials. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'mydatabase');
 
/* Attempt to connect to MySQL database */
$link = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

/* You can set a separate connection file and instead of writing the database connection credentials, 
   just call it using  require_once "config\database.php"; */

// Define variables and initialize with empty values
$id_jenisPembayaran = $nama_jenisPembayaran = "";

// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){

    // Assigning variables with data from input fields
        $id_jenisPembayaran = trim($_POST["id_jenisPembayaran"]);
        $nama_jenisPembayaran = trim($_POST["nama_jenisPembayaran"]);

         // Prepare an insert statement
        $sql = "INSERT INTO jenispembayaran (id_jenisPembayaran, nama_jenisPembayaran) VALUES (?,?)";
         
        if($stmt = mysqli_prepare($link, $sql)){
            // Bind variables to the prepared statement as parameters
            mysqli_stmt_bind_param($stmt, "ss", $param_id_jenisPembayaran, $param_nama_jenisPembayaran);
            
            // Set parameters
            $param_id_jenisPembayaran = $id_jenisPembayaran;
            $param_nama_jenisPembayaran = $nama_jenisPembayaran;

            
            // Attempt to execute the prepared statement
            if(mysqli_stmt_execute($stmt)){
                // Records created successfully. Redirect to landing page
                header("location: testing2.php");
                exit();
            } else{
                echo "Something went wrong. Please try again later.";
            }
         
        // Close statement
        mysqli_stmt_close($stmt);
}
    // Close connection
    mysqli_close($link);
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Create Record</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
    <style type="text/css">
        .wrapper{
            width: 500px;
            margin: 0 auto;
        }
    </style>
</head>
<body>
<div class="content">
    <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
        <article class="card">
            <br>
            <br>
            <label for="id_jenisPembayaran">Kode Jenis Pembayaran:</label>
            <input type="text" name="id_jenisPembayaran" value="<?php echo $id_jenisPembayaran; ?>">
            <br>
            <br>
            <label for="nama_jenisPembayaran">Nama Jenis Pembayaran:</label>
            <input type="text" name="nama_jenisPembayaran" value="<?php echo $nama_jenisPembayaran; ?>">
            <br>
            <br>
            <input type="submit" value="submit">
        </article>
    </form>
</div>
</body>

As you can see, the PHP and HTML code are in one single file and honestly I attempted many times just to understand how the flow went because I've manage to post this code but with empty and NULL values. Even though I got this initial code from a tutorial, I had to trim and re-code them to make it work.

This is the source of the tutorial : https://www.tutorialrepublic.com/php-tutorial/php-mysql-crud-application.php and I was particularly re-create your codes according to the create.php section. Also, please look at the database config section and how every PHP page is calling it to create a connection.