MySQL and PHP - insert even if variable is blank?

Question

I have a MySQL statement that inserts some variables into the database. I recently added 2 fields which are optional ($intLat, $intLng), but I would like to include them in the insert statement if entered:

$query = "INSERT INTO data (notes, id, filesUploaded, lat, lng, intLat, intLng)
          VALUES ('$notes', '$id', TRIM('$imageUploaded'), '$lat', '$long', 
                  '$intLat', '$intLng')";
mysql_query($query);

Are those fields integers? If so, you don't need to enclose them in quotes. You may wish to cast them to integers with intval() or (int), and then if they are null they will become 0. — JAL
– JAL, Commented Jan 6, 2011 at 21:17

MSD · Accepted Answer · 2011-01-06 21:10:48Z

2

Just use one insert, and if the variable is empty it will post to the database empty

answered Jan 6, 2011 at 21:10

MSD

3591 gold badge4 silver badges16 bronze badges

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

1 Comment

JAL Over a year ago

This is fine as long as those fields are defined as DEFAULT NULL. In the case of the statement above, in which everything appears to be a string, it works either way, too.

kander · Accepted Answer · 2011-01-06 21:18:11Z

0

Disagree with Michael Stevens; if the values are optional, they should be NULL in the database. However, since you have them quoted, they will be inserted as empty strings. That would be bad from a data-sanity perspective.

So... how to do it? Spend a few moments writing (or finding...) a function that takes an associative array as input, and generates the query. Key ingredients: array_keys, implode and mysql_real_escape_string. The PHP Manual is littered with these :)

answered Jan 6, 2011 at 21:18

kander

4,3061 gold badge28 silver badges43 bronze badges

Collectives™ on Stack Overflow

MySQL and PHP - insert even if variable is blank?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related