MYSQL INSERT query don't use empty fields

Question

I found some information about this subject (Trying to insert into mysql database ignoring blank fields). But then I can get some issues with injections...

I already added NOT NULLto my columns but it still inserted a value with ''. So it didn't give me the result I wanted.

Anyways I got this now:

if(isset($_POST['btnOpslaan']))
{
    include_once("dbConnect.php");

    $Date1 = strip_tags($_POST['Datum1']);
    $ProductDescription1 = strip_tags($_POST['ProductOmschrijving1']);
    $Amount1 = strip_tags($_POST['Aantal1']);
    $AmountCreated1 = strip_tags($_POST['AantalGemaakt1']);
    $Difference1 = strip_tags($_POST['Verschil1']);

    $Date2 = strip_tags($_POST['Datum2']);
    $ProductDescription2 = strip_tags($_POST['ProductOmschrijving2']);
    $Amount2 = strip_tags($_POST['Aantal2']);
    $AmountCreated2 = strip_tags($_POST['AantalGemaakt2']);
    $Difference2 = strip_tags($_POST['Verschil2']);

    $Query = "INSERT INTO productielijn1 (Datum, Productomschrijving, Aantal, AantalGemaakt, Verschil) VALUES(?, ?, ?, ?, ?), (?, ?, ?, ?, ?);

    if($stmt = $dbCon->prepare($Query)) 
    {       
       $stmt->bind_param('ssiiissiii', 
       $Date1, $ProductDescription1, $Amount1, $AmountCreated1, $Difference1,
       $Date2, $ProductDescription2, $Amount2, $AmountCreated2, $Difference2);

       if( $stmt->execute() === TRUE)
       {
           $Message = "Planning is aangemaakt!";
       }  
    }
}

My pageis for creating a schedule. But sometimes you only want to add 1 task. So it could be possible that $Date2, $productDescription2 and the others are empty. But it still adds the row to my mysql database with just values 0000-00-00 '' and 0, 0, 0.

I can easily stop this by making an IF statement checking if these fields are set. If not, it uses a different query.

But I got like 5 rows for adding information to the database ($Date3, $Date4 and $Date5 + all the other information per row).

So when I will solve this with if statements I get huge if statements...

Is there a easier way to solve this issue? Something I can tell my database for not adding a row when they are empty?

Then I get an if statement like IF(isset($_POST['Datum1']) && isset($_POST['Datum2']) &&isset($_POST['Datum3']) && isset($_POST['Datum4']) && isset($_POST['Datum5'])) and thats only for dates... — Bart88, Jan 13 '16 at 08:13

score 1 · Accepted Answer · answered Jan 13 '16 at 08:10

1

Design your database such that you store information regarding Date and product description. Use something like:

Schedule_id| Datum | ProductOmschrijving | Aantal | AantalGemaakt |

1 | xyz | xyz | asd | sdadsa |

1 | abc | dsf | axc | asddsa |

Post the data using arrays and than iterate the array and insert one by one

answered Jan 13 '16 at 08:10

Noor Ahmed

1,507
9
14

And how can I design it that way? I already put in the NOT NULL part. It still accepts `' '` in a string/date (since its not NULL) – Bart88 Jan 13 '16 at 08:17
Yes it will but the advantage of doing it the way I am saying is that no matter what is the number for variables posted, you just have to iterate the post array. No need for if else, it is a better approach of doing what you want and will save you time – Noor Ahmed Jan 13 '16 at 08:18
Well it pointed me the right direction. The next step is checking out how a array works in php :) thanks for pointing me in the right direction – Bart88 Jan 13 '16 at 08:21

MYSQL INSERT query don't use empty fields

1 Answers1

Schedule_id| Datum | ProductOmschrijving | Aantal | AantalGemaakt |

1 | xyz | xyz | asd | sdadsa |

1 | abc | dsf | axc | asddsa |