Re: Writing MySQL Update Query with NULL value

Hi Bill, Please keep the replies on the list so others can provide their input and also learn.

2) You do need quotes around your data, plus you should use mysql_real_escape_string to stop sql injection attacks:

Again, I'm using mysql_real_escape_string() in my code, just trying to focus on the NULL question and not complicate the code I posted.

Fair enough :)

// set a default of NULL $suffix = "NULL"; if (!empty($_POST['suffix'])) { // note - you need to add the quotes around the data here $suffix = "'" . mysql_real_escape_string($_POST['suffix']) . "'"; }

Is this materially different than: $suffix = empty($suffix) ? NULL : $suffix; having already sanitized $suffix?

It may end up different in your final query. Notice the quotes around $suffix where mysql_real_escape_string is used. You cannot have quotes around the "NULL" keyword otherwise the database (mysql, postgres, oracle, mssql will all work the same in this regard) treats that as a text value. NULL !== "NULL". If you have a query like: update table set field='NULL' where id='X'; Then field will have a value of 'NULL' - which is a text string (a value the db knows about). If you have a query like: update table set field=NULL where id='X'; Then field will have a NULL value which means "unknown". You can test that out with these queries: // this will find "unknown" values for "field". select id, field from table where field is null; // this will find values for field where it contains the string 'NULL'. select id, field from table where field='NULL'; or even better: select id, field, isnull(field) from table; -- Postgresql & php tutorials http://www.designmagick.com/