PDO Insert null instead of empty?

Question

I am having trouble figuring out how to insert null instead of empty with PDO. Here is my code:

$pdo = new PDO("mysql:charset=utf8mb4;host=$servername;dbname=$dbname", $username, $password);

$data = [
    'firstname'         => $data['firstname'],
    'lastname'          => $data['lastname'],
    'phone'             => $data['phone'],
    'email'             => $data['email']

];

$sql = "INSERT INTO entires 
            (firstname,
            lastname,
            phone,
            email) 
VALUES      (:firstname,
            :lastname,
            :phone,
            :email)";

$stmt= $pdo->prepare($sql);

$stmt->execute($data);

How do I insert the empty values as null instead of empty?

https://stackoverflow.com/questions/17723839/execute-pdo-with-an-array-containing-null-values — Anurat Chapanond, Feb 13 '21 at 05:54
@AnuratChapanond I saw that but how would I implement that in my code? — tony, Feb 13 '21 at 05:56
Don't ask PDO to do a job you have to do your self. If you need a null value then just don't send an empty string to the database. Send a null value in stead — Your Common Sense, Feb 13 '21 at 05:58
@YourCommonSense oh hello again, I was looking over your site looking for tips on how to do this, could you lend a hand? — tony, Feb 13 '21 at 05:59
what's so complex in just following a verbal instruction? `'email' => $data['email'] == '' ? null : $data['email']` **literally** means what I said above, "Send a null value to the database" — Your Common Sense, Feb 13 '21 at 06:04
@YourCommonSense oh wow okay that works very well, I was expecting to have to use bindValue - the above is the best way to do that? What is the bindValue solution? — tony, Feb 13 '21 at 06:09
best way to do what? to convert an empty string to null? you can write any other code you like. with bindvalue it will be exactly the same. It is not PDO at all. it is **your value you have in your array**. If it's an empty string then PDO will store an empty string. If it's null, then PDO will store a null. if it's 100 billion, then PDO will store 100 billion. Starting to get it? — Your Common Sense, Feb 13 '21 at 06:13
@YourCommonSense yeah I get it, I mean in terms of efficiency. This way you have to check every value, when using bindvalue would it offer a level of automation where I won't have to code each one? Could you show me how to do that, just to ease my curiosity? — tony, Feb 13 '21 at 06:15
again. your problem has nothing to do with bindvalue or PDO at all. neither bindvalue nor any other PDO related function has a telepathic mechanism to get in your mind and tell that this particular empty string has to be converted to null and this one should not. it is not about PDO. it is about your data. it is **you**, not "bindvalue" has to decide which value you want to store. PDO, using bindvalue or whatever just stores anything you send. — Your Common Sense, Feb 13 '21 at 06:19
Okay, I thought bindvalue added options that would automatically convert empty strings into null values. I am guessing that's not the case though. — tony, Feb 13 '21 at 06:21
That was a misleading answer to a completely irrelevant question that deceived you. I did my best to fix it so it won't do any harm anymore — Your Common Sense, Feb 13 '21 at 06:26
WHY did you accept an answer that will ruin your application? Try to store 0 value and see what would happen — Your Common Sense, Feb 13 '21 at 08:03
this dude also just uses some functions but don't really understand what every function does. empty() is a very tricky function that returns positive for a wide range of values. in case you need to change only an empty string for a null, then just use the condition I wrote. — Your Common Sense, Feb 13 '21 at 08:06
by the way, my code is actually wrong the same way for which I scolded someone else. $var == '' will return true if $var=0 before PHP8. so it should be === — Your Common Sense, Feb 13 '21 at 08:27

Sol Rana · Answer 1 · 2021-02-13T08:28:49.323

-2

You can pass null value as below.

$data = [
    'firstname'         => trim($data['firstname']) == '' ? null : $data['firstname'],
    'lastname'          => trim($data['lastname']) == '' ? null : $data['lastname'],
    'phone'             => trim($data['phone']) == '' ? null : $data['phone'],
    'email'             => trim($data['email']) == '' ? null : $data['email'],
];

edited Feb 13 '21 at 08:28

answered Feb 13 '21 at 07:22

Sol Rana

35
4

this code makes no sense. – Your Common Sense Feb 13 '21 at 08:02
You are correct empty() function can return positive for a wide range of value. But checking empty variable by just doing $variable == '' also does not seem to be correct. Just checking $variable == '' won't return true if variable has bunch of white spaces. So trim($variable) == '' is correct in my opinion. – Sol Rana Feb 13 '21 at 08:25

PDO Insert null instead of empty?

1 Answers1