dynamic PDO mysql update

Question

I have a form with many optional fields. I am using PHP and PDO to collect the data from the forms and store it in a DB.

Since some optional fields may be empty, I only want to store inputs that actually have value. I can easily identify the inputs that have value, but without using a whole lot of IF statements, I can't seem to find a way to ONLY insert a value into the DB if it exists.

My current PDO looks something like this:

$data = array($var1, $var2, $var3, $var4, $var5, $var6);

$STH = $DBH->("INSERT INTO users (first, last, addr, city, email, phone) values (?, ?, ?, ?, ?, ?)";
$STH->execute($data);

Is there a way to only update a column if a value in the array exists? Example: if $var3 and $var4 are empty, nothing is stored in the DB for "addr" and "city"?

Set the defaults in the mySQL database and then you can submit empty or whatever values — Chitowns24, Mar 18 '14 at 19:19
Thanks @Chitowns24 for the suggestion. I wasn't sure that was good practice, but after doing more research it seems to be preferred. I showed off my noobiness. Thanks guys. — klinore, Mar 18 '14 at 19:47

score 0 · Accepted Answer · edited May 23 '17 at 12:05

0

For any other noobs out there, turns out inserting null values into a database is acceptable as long as you have your mysql rows up to allow null. You may also want to give them a default value of null.

See this post:
MySQL, better to insert NULL or empty string?

Thanks to everyone for the help.

edited May 23 '17 at 12:05

Community

1
1

answered Mar 18 '14 at 19:51

klinore

2,589
4
19
21

score -1 · Answer 2 · answered Mar 18 '14 at 19:21

Ideally, you'd set up the table to not allow null values for those fields. Otherwise, on the PHP side you could just check if the values have been set and are not null:

if (isset($var1, $var2, $var3, $var4, $var5, $var6)) {
  $data = array($var1, $var2, $var3, $var4, $var5, $var6);
  $STH = $DBH->("INSERT INTO users (first, last, addr, city, email, phone) values (?, ?, ?, ?, ?, ?)";
  $STH->execute($data);
}

Unless you want to still insert? In which case just initialize the values to an empty string or whatever:

$data = array($var1?:"", $var2?:"", $var3?:"", $var4?:"", $var5?:"", $var6?:"");

dynamic PDO mysql update

2 Answers2