Why are extra empty rows generated in mysql table when using php insert variables

I have tried to insert variable to mysql table from submitted html form. However, I observed that 3 rows is generated while I have just inserted 1 row of data variable. The problem disappeared when the data is not variable. This problem annoyed me very much. Hope problems can be solved. Thank you very much.

Below is the coding of creating the table:

<!DOCTYPE html>
<html>
<head>
<title>Server Database</title>
</head>
<body>
<span style='font-size:24;font-weight:bold;'>Server Database</span>
<br /><br />

<?php
$host="localhost";
$port=3306;
$socket="";
$user="root";
$password="";
$dbname="";

$conn = new mysqli($host, $user, $password, $dbname, $port, $socket)
    or die ('Could not connect to the database server' .       mysqli_connect_error());

echo "it work"."<br>";

$SCusName=$_POST['CusName'];
$SCusChiName=$_POST['CusChiName'];
$SDate=$_POST['Date'];

$sql = "CREATE DATABASE db";
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully"."<br>";
} else {
    echo "Error creating database: " . $conn->error;
}

mysqli_select_db($conn,"db")
    or die ('cannot select');

$sql = "CREATE TABLE Requests (
RequestNumber INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
CustomerName VARCHAR(50),
ChineseName VARCHAR(50),
DateOfRequest VARCHAR(30)
)";

if ($conn->query($sql) === TRUE) {
    echo "Table Requests created successfully"."<br>";
} else {
    echo "Error creating table: " . $conn->error;
}

$sql = "INSERT INTO Requests (CustomerName, ChineseName, DateOfRequest)
VALUES ('".$SCusName."', '".$SCusChiName."', '".$SDate."');";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully"."<br>";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$sql = "SELECT BbKeyID, CustomerName, ChineseName FROM Requests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
   // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "BbKeyID: " . $row["BbKeyID"]. " CustomerName: " .    $row["CustomerName"]. " ChineseName: " . $row["ChineseName"]. "<br>";
}
} else {
    echo "0 results";
}

$conn->close();
?>

</body>
</html>

The result in mysql table become contains three row as follow with a message 3 row(s) returned: The first row contains correct data while the other two rows are empty in value only with the auto-incremented RequestNumber. Is the RequestNumber make the problems?

1 Result 1 Result 2 Result 3

2

3

The empty values are not NULL.

If the insert data is changed to:

$sql = "INSERT INTO Requests (CustomerName, ChineseName, DateOfRequest)
VALUES ('AAA', 'BBB', 'CCC')";

The result become correct and only one row given out as follow.

1 Result 1 Result 2 Result 3

Please help me to solve the problem. Thank you very much..

Answers


You run your INSERT query EVERY TIME the page is loaded, even if no form was submitted. So if the page is fetched via GET, you'll have undefined $_POST data, and insert empty strings.

You should have at least

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
   ... do db insert stuff here ...
}

to "hide" the code from non-post requests.

And you are vulnerable to sql injection attacks. Enjoy getting your server pwn3d.


Need Your Help

UINavigationController back button issue

xcode button navigation controller back

My Application have navigation controller and table views. When the back button is clicked and the view is popped out from controller stack, i noticed that the table events are not executed (eg:

I received an error when joining data through JoinRowSet

java sql-server jdbc

I've tried to do a Join via Java using JoinRowSet but with no success, i'm receiving an error :