CODEWITHSUNDEEP
phpmysqldatabaseinsertforms
ThisaruG
ThisaruG

Reputation: 3412

Pass Data from HTML Form to MySQL Database using PHP

I'm trying to insert some data into a mysql database I created on my localhost (using wamp server). But it's not inserting any data into the table. It doesn't shows any error massages either. In the other hand, it shows the echos I made. Please help me out. I can't type all the code in one page like this question as I'm redirecting to this page from an intermediate php file. Here is the code I wrote. 

<?php
    $con = mysqli_connect("localhost", "root", "11111", "CAR_PARKING_SYSTEM");

    $D_License_No = $_POST['D_License_No'];
    $V_License_No = $_POST['V_License_No'];
    $V_Type = $_POST['vehicle_type'];

    if(!strcmp($V_Type, "four_wheel"))
    {
        $charge = 400;
    }
    else
    {
        $charge = 50;
    }
    $query = "INSERT INTO CUSTOMER_VEHICLE (V_License_No, D_License_No, Arrived_Time, Charge) 
            VALUES ('$V_License_No', '$D_License_No', 'date('H:i')', '$charge')";

    echo "<p>Driver License Number " .$D_License_No. "</p>";
    echo "<p>Vehicle License Plate Number " .$V_License_No. "</p>";
?>

Upvotes: 1

Views: 2850

Answers (4)

Sverri M. Olsen
Sverri M. Olsen

Reputation: 13283

Here is a way of handling this query:

First we create a database handle. Most people these days use the object oriented way of doing it, so we will use it here.

$mysqli = new MySQLi('localhost', 'root', '11111', 'CAR_PARKING_SYSTEM');

Then we define the SQL query. The question marks (?) are place holders for where the values will be placed.

$sql = <<< SQL
    INSERT INTO `CUSTOMER_VEHICLE` (
        `V_License_No`,
        `D_License_No`,
        `Arrived_Time`,
        `Charge`
    ) VALUES (
        ?, ?, ?, ?
    );
SQL;

In order to fill out the place holders we need to prepare the query. This is called a "prepared statement" (or "stmt" for short).

Prepared statements are sent to the database, which checks it for errors and also optimizes it so that consecutive calls to the execute() method are performed faster. In this case, however, we are mostly just using a prepared statement in order to avoid malicious input from affecting the query.

$stmt = $mysqli->prepare($sql);
if ($stmt === false) {
    die('Could not prepare SQL: '.$mysqli->error);
}

Before we can use the prepared statement we have to have some way of putting values into the place holders. We do that by binding some variables to the place holders. In this case we are binding the variables $vLicense, $dLicense, $arrived and $charge to the place holders. The variables names can be anything.

$ok = $stmt->bind_param('sssi', $vLicense, $dLicense, $arrived, $charge);
if ($ok === false) {
    die('Could not bind params: '.$stmt->error);
}

Now that we have bound some variables to the place holders we can start setting their values. In this case we are using the filter_input() function to sanitize the input of some variables. The $charge variable is set to one of two values depending on what vehicle type we are dealing with.

$vLicense = filter_input(INPUT_POST, 'V_License_No', FILTER_SANITIZE_STRING);
$dLicense = filter_input(INPUT_POST, 'D_License_No', FILTER_SANITIZE_STRING);
$arrived  = date('H:i');
if (isset($_POST['vehicle_type']) && $_POST['vehicle_type'] === 'four_wheel') {
    $charge = 50;
} else {
    $charge = 400;
}

The values are set and now we can execute the statement. That is done simply by calling the statement's execute() method:

if ($stmt->execute() === false) {
    die('Could not execute query: '.$stmt->error);
} else {
    echo '<p>Query executed successfully!</p>';
}
$stmt->close();

I encourage you to read about the MySQLi documentation and the filter extension.

The full code can be found here.

Upvotes: 3

Tomas Ramirez Sarduy
Tomas Ramirez Sarduy

Reputation: 17471

You are not inserting anything, $query is just an string, you need to pass the query to the mysqli function, also, is better if you check and avoid sql injection. Your code should be something like this:

$mysqli = new mysqli("localhost", "root", "11111", "CAR_PARKING_SYSTEM");

$D_License_No = $_POST['D_License_No'];
$V_License_No = $_POST['V_License_No'];
$V_Type = $_POST['vehicle_type'];
$charge = !strcmp($V_Type, "four_wheel")?400:50;

$query = "insert into CUSTOMER_VEHICLE 
    (`V_License_No`, `D_License_No`, `Arrived_Time`, `Charge`) 
    values(? , ?, ?, ?)"
$stmt = $mysqli->prepare($query);
$stmt->bind_param("ssss",$V_License_No, $D_License_No, date('H:i'), $charge);
if($stmt->execute()){
    //success inserted
    $stmt->close();
    echo "<p>Driver License Number " .$D_License_No. "</p>";
    echo "<p>Vehicle License Plate Number " .$V_License_No. "</p>";
}
else{
    $error = $mysqli->error;
}

Upvotes: 2

Paul Denisevich
Paul Denisevich

Reputation: 2414

You forgot to run a query:

$result = mysqli_query($query);

Upvotes: 0

PHPmaker
PHPmaker

Reputation: 163

you need to use some debugging to get some errors s oyou can investergate futher this is how i would of built said script

<?php
try 
{
    $con = mysqli_connect("localhost", "root", "11111", "CAR_PARKING_SYSTEM");

if (empty($con))
{
echo "Mysql failed".mysqli_error();
die; // should always kill script if you dont need it to continue
}

    $D_License_No = $_POST['D_License_No'];
    $V_License_No = $_POST['V_License_No'];
    $V_Type = $_POST['vehicle_type'];

    if(!strcmp($V_Type, "four_wheel"))
    {
        $charge = 400;
    }
    else
    {
        $charge = 50;
    }
    $query = "INSERT INTO CUSTOMER_VEHICLE (V_License_No, D_License_No, Arrived_Time, Charge) 
            VALUES ('$V_License_No', '$D_License_No', 'date('H:i')', '$charge')";

    echo "<p>Driver License Number " .$D_License_No. "</p>";
    echo "<p>Vehicle License Plate Number " .$V_License_No. "</p>";
}
catch (Exception $e)
{
echo $e;
}
?>

im sorry if i have some syntax off i am on a train and not the best place to code :P

Upvotes: -3

Related Questions