How can I avoid inserting empty rows into MySQL when working with PHP variables that have no value?

Question

I have searched for the last few hours on this and have come up empty.

I am using a sample piece of code that I have edited slightly to work as needed. It posts values to a MySQL table, each set to a respective row. What I would like to be able to do is have the code not create a row if the PHP variable does not have a value. I am still relatively new with MySQL but have been really digging in so please if you have an answer, help me understand some of the meaning behind it. Thank you for your help!

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$col1Val1 = $_POST['col1Val1'];
$col1Val2 = $_POST['col1Val2'];
$col1Val3 = $_POST['col1Val3'];
$col2Val1 = $_POST['col2Val1'];
$col2Val2 = $_POST['col2Val2'];
$col2Val3 = $_POST['col2Val3'];
$col3Val1 = $_POST['col3Val1'];
$col3Val2 = $_POST['col3Val2'];
$col3Val3 = $_POST['col3Val3'];


try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // begin the transaction
    $conn->beginTransaction();
    // our SQL statements
    $conn->exec("INSERT INTO tableName(column1, column2, column3) 
            VALUES ('$col1Val1', '$col2Val1', '$col3Val1'),
                    ('$col1Val2', '$col2Val2', '$col3Val2'),
                    ('$col1Val3', '$col2Val3', '$col3Val3')");

    // commit the transaction
    $conn->commit();
    echo "New records created successfully";
}
catch(PDOException $e)
{
    // roll back the transaction if something failed
    $conn->rollback();
    echo "Error: " . $e->getMessage();
}

$conn = null;
?>

Well **first you turn on error reporting** and then you fix all the obvious bugs! Add [error reporting](http://stackoverflow.com/questions/845021/how-to-get-useful-error-messages-in-php/845025#845025) to the top of your file(s) _while testing_ right after your opening PHP tag for example ` — RiggsFolly, May 16 '17 at 21:52
**Then you do something about the SQL Injection issues** Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, May 16 '17 at 21:53
I had no bugs when I tested this, the issue arises when I leave some of the input fields blank. A row is created in the database that has no values. — Born2Discover, May 16 '17 at 21:53
If you turn on error reporting as I suggest, and then leave some fields blank, you will see the errors reported — RiggsFolly, May 16 '17 at 21:57
I'm ignoring SQL injections, I know about those. I simply made this sample up for the sake of time. — Born2Discover, May 16 '17 at 21:57
There is no EASY/LAZY way round this. You have to check each input field and if its empty amend the query accordingly — RiggsFolly, May 16 '17 at 22:00
ALternatively, you have to change the tabe definition and define the columns so that an empty value is not allowed but then you whole query will fail and rollback whenever you leave a single input field empty — RiggsFolly, May 16 '17 at 22:01

score 1 · Answer 1 · answered May 16 '17 at 22:13

The way that query is currently written with multiple value sets, it's going to insert three rows whether they're empty or not. You need to evaluate each row separately in order to avoid inserting empty ones.

You have two main options.

Prepare an INSERT statement with placeholders for one row of values, iterate your $_POST and only execute the statement with rows that aren't empty. This is what I would suggest. With only a maximum of three rows to insert, the performance hit of executing multiple queries should be minimal.
Build your INSERT statement dynamically, and append a set of values for each of the rows that aren't empty. This is fine too, and it is still possible to construct a prepared statement that way, but for something like this it seems more complicated than necessary.

My suggestion for option 1:

$sql = "INSERT INTO tableName(column1, column2, column3) VALUES (?, ?, ?)";
$statement = $conn->prepare($sql);

for ($i=1; $i <= 3; $i++) {
    $row = [];
    for ($j=0; $j <= 3; $j++) {
        $row[] = $_POST["col{$i}Val{$j}"];
    }
    if (array_filter($row)) {       // check if the row has any non-empty values
        $statement->execute($row);
    }
}

This could be simplified a bit if you changed the names of your form fields up a bit so that you could get the values from sub-arrays in $_POST.

score 0 · Answer 2 · answered May 16 '17 at 22:19

So thank you to all who gave me some tips. I believe I came up with a solution that will work decently for anyone who comes across the same issue.

What I did was for each set of data that goes to its own row, I created an ID field "row#Data" in the HTML that is defaulted to 0 but changes to 1 if each value is filled out. Then I used the if statement for each row instead of checking each variable.

There may be other ways to do this dynamically but to ensure functionality, this is what I came up with.

if ($row1Data == 1) $conn->exec(INSERT INTO...
if ($row2Data == 1) $conn->exec(INSERT INTO...
if ($row3Data == 1) $conn->exec(INSERT INTO...
...

How can I avoid inserting empty rows into MySQL when working with PHP variables that have no value?

2 Answers2