Null Values in DB

Question

I am trying to make a VERY simple PHP form that posts a form to MySQL Database, however I am having some issues, and would welcome a simple fix for this if possible:

My PHP:

<?php
$con=mysqli_connect("serveraddress","db","password","dbname");
// Check connection

if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql="INSERT INTO Persons (email, type, cats)
VALUES
('$_POST[email]','$_POST[type]','$_POST[cats]')";

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

mysqli_close($con);
?>

My HTML:

<form action="personuploader.php" method="post">

        <table class="#"> 

            <tr>

                <th colspan="2">Test</th>

            </tr>

            <tr>

                <td>Email Address:</td>

                <td><input type="text" name="email"> </td>

            </tr>

            <tr>

                <td>Type:</td>

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

            </tr>

            <tr>

                <td>Cats:</td>

                <td><input type="text" name="cats"> </td>

            </tr>

            <tr>

                <td></td>

                <td><input type="submit" value="upload" name="upload">

            </tr>

        </table>  

    </form>

My SQL Configuration:

Even though I have not null set in the DB I am getting empty results, is it possible to stop the form resubmitting on refresh causing null results be entered into the DB. I will enter some form validation to stop null results passing into the post script in the future but refreshing the page still sends over null results.

Answer 1

Edit:

Your column names have mixed-case letters (Cats and cats are not the same)

I edited my answer, where I changed it from:

$sql="INSERT INTO `Persons` (`email`, `type`, `cats`)

to

$sql="INSERT INTO `Persons` (`Email`, `Type`, `Cats`)

I also made a mistake with a missing ) for if(empty($_POST['email'] which has been fixed.

Please make sure also, that your column names are indeed called Email Type Cats and not email type cats Change it to the letter-case that is in your DB.

Your table's original structure: (larger image)

• You should have talked to me first, instead of posting a new question with my code

---

See the rest below in the code.

---

As I stated in my comments under your original question, have put this together for you.

• Don't use this method VALUES ('$_POST[email]','$_POST[type]','$_POST[cats]') you're open to SQL injection

• To avoid re-submissions causing an empty entry, you can use a header() to redirect to another page, or use AJAX

However, I am sure there are other ways of doing this in the query itself, I just don't remember how right now.

I.e.: In place of where you have echo "1 record added";

you can do header("Location: added.php"); exit();

You can also use a conditional statement:

if(empty($_POST['variable'])){ die("Fill this in.");}

Try the following. It will check for empty fields, as well as check if the upload submit-type button is set.

Plus, I modified the way your query was done, replacing POST variables with mysqli_real_escape_string()

<?php
$con=mysqli_connect("serveraddress","db","password","dbname");
// Check connection

if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

if(isset($_POST['upload'])){

// You can replace the || with && if required
// depending on what you want to check for.
    if(empty($_POST['email']) || empty($_POST['type']) || empty($_POST['cats']))
    {
    die("You need to fill in all the fields.");
    }

$email = mysqli_real_escape_string($con, $_POST['email']);
$type = mysqli_real_escape_string($con, $_POST['type']);
$cats = mysqli_real_escape_string($con, $_POST['cats']);

$sql="INSERT INTO `Persons` (`Email`, `Type`, `Cats`) 
VALUES ('$email','$type','$cats')";

    if (!mysqli_query($con,$sql))
    {
    die('Error: ' . mysqli_error($con));
    }
    
// Uncomment out if you're going to use echo, but not with header.
// echo "1 record added";

header("Location: redirect_to_other_page.php");
exit();

} // end of if(isset($_POST['upload']

// else conditional statement for if(isset($_POST['upload']
else{ echo "You cannot do this operation from here."; }

mysqli_close($con);
?>

---

Footnotes:

Just saying, the following:

('$_POST[email]','$_POST[type]','$_POST[cats]')

should have been:

('$_POST['email']','$_POST['type']','$_POST['cats']')

However, using this method is highly discouraged, as I already mentioned.

Answer 2

You need to check if a submit actually occured:

if ($_SERVER["REQUEST_METHOD"] == 'POST') {
    ... submit occured, do DB stuff
}

And note that an empty string is NOT the same as an SQL null. Empty string is just that - a string which happens to be empty/zero-length. An SQL null is quite literally "unknown". Your code cannot insert an actual null - it can only ever insert empty strings.

Answer 3

You should check whether the Upload button has been clicked on the "personuploader.php" file.

// Initializing Variables
$email = '';
$type  = '';
$error = false;    

if ( isset ( $_POST['upload'] ) ) {

    // Then capture the POST Variables
    $email = $_POST['email'];

    // Do Some Validations
    if ($email == '') {
        $error = true;
    }

    // Process the Form if NO Errors

    if ($error == false) {

        // Insert The Data into DB
    }

}