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
– Bart88, Commented Jan 13, 2016 at 8:13

Noor Ahmed · Accepted Answer · 2016-01-13 08:10:13Z

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, 2016 at 8:10

Noor Ahmed

1,61710 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bart88 Over a year ago

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)

Noor Ahmed Over a year ago

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

Bart88 Over a year ago

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

Collectives™ on Stack Overflow

MYSQL INSERT query don't use empty fields

1 Answer 1

Schedule_id| Datum | ProductOmschrijving | Aantal | AantalGemaakt |

1 | xyz | xyz | asd | sdadsa |

1 | abc | dsf | axc | asddsa |

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Schedule_id| Datum | ProductOmschrijving | Aantal | AantalGemaakt |

1 | xyz | xyz | asd | sdadsa |

1 | abc | dsf | axc | asddsa |

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related