carrilo Posted September 11, 2013 Share Posted September 11, 2013 I want to introduce to a null value introduced into a table when I don't introduce any value. I have the following code which I thought would do this but it in fact introduces a text value of "null" if I leave the quotation marks off it introduces a blank value (not null). How can I introduce a real null value? $al3 = ( !empty($_POST['al3']) ) ? ($_POST['al3']): "NULL"; Link to comment https://forums.phpfreaks.com/topic/282079-introducing-null-value/ Share on other sites More sharing options...
cyberRobot Posted September 11, 2013 Share Posted September 11, 2013 Try removing the quotes: http://php.net/manual/en/language.types.null.php Link to comment https://forums.phpfreaks.com/topic/282079-introducing-null-value/#findComment-1449153 Share on other sites More sharing options...
AbraCadaver Posted September 11, 2013 Share Posted September 11, 2013 Or if the column default is NULL then just "" I think. Link to comment https://forums.phpfreaks.com/topic/282079-introducing-null-value/#findComment-1449169 Share on other sites More sharing options...
carrilo Posted September 12, 2013 Author Share Posted September 12, 2013 I've tried both of these alternatives. In both cases the value introduced is blank/empty but not NULL Link to comment https://forums.phpfreaks.com/topic/282079-introducing-null-value/#findComment-1449185 Share on other sites More sharing options...
vinny42 Posted September 12, 2013 Share Posted September 12, 2013 PHP's idea of NULL is not necessarily SQL's idea of NULL. If you want to insert NULL then the text NULL must appear in the query: INSERT INTO table (columnname) VALUES (NULL); This means that if your query puts quotes around it it will fail. If you append a variable containing NULL to a quer string you'll get a bad query. If you give it an empty string it will store an empty string. You'd have to use a construction like NULLIF(), but that only works if you make a distinction between NULL and an empty string. So what remains is the prepared statement: http://php.net/manual/en/mysqli.prepare.php to which you can feed a variable with value NULL (not the string, but the PHP value (wel, lack of value, NULL indicates that the value is not known)) Link to comment https://forums.phpfreaks.com/topic/282079-introducing-null-value/#findComment-1449194 Share on other sites More sharing options...
carrilo Posted September 14, 2013 Author Share Posted September 14, 2013 Sorry I don't really understand this, could you give me an example Link to comment https://forums.phpfreaks.com/topic/282079-introducing-null-value/#findComment-1449470 Share on other sites More sharing options...
DFulg Posted September 14, 2013 Share Posted September 14, 2013 The correct answer has already been posted: $al3 = ( !empty($_POST['al3']) ) ? ($_POST['al3']): null; Make sure that the relevant field excepts null values. If you are not sure, use the "Explain table" syntax and post it here. Also, never insert user data directly into a mysql query. Always properly sanitize the data first. Link to comment https://forums.phpfreaks.com/topic/282079-introducing-null-value/#findComment-1449477 Share on other sites More sharing options...
carrilo Posted September 14, 2013 Author Share Posted September 14, 2013 If I write the query like this, with null without quotes the value entered is an empty value, not a null value. Yes it does accept null values Link to comment https://forums.phpfreaks.com/topic/282079-introducing-null-value/#findComment-1449481 Share on other sites More sharing options...
vinny42 Posted September 14, 2013 Share Posted September 14, 2013 [quote The correct answer has already been posted: $al3 = ( !empty($_POST['al3']) ) ? ($_POST['al3']): null; Don't use empty() for this, you don't want the value zero or an empty string to be converted to null. Small difference, huge consequences. @carillo, what do not not understand about prepared statements? Link to comment https://forums.phpfreaks.com/topic/282079-introducing-null-value/#findComment-1449485 Share on other sites More sharing options...
DFulg Posted September 14, 2013 Share Posted September 14, 2013 My mistake, this will not work unless null is specified upon insert time since PHP treats null values differently then MYSQL. You can however do this using a prepared statement in MYSQLi or PDO which should be what you are using anyway. Below is pseudo code to illustrate an example usage: $stmt = $mysqli->prepare("INSERT INTO table (field1, field2) VALUES (?, ?)"); $stmt->bind_param('ss', $field1, $field2); $field1 = "string1"; $field2 = null; $stmt->execute(); Link to comment https://forums.phpfreaks.com/topic/282079-introducing-null-value/#findComment-1449486 Share on other sites More sharing options...
carrilo Posted September 14, 2013 Author Share Posted September 14, 2013 Ok I have found the error, the value should not be in quotes in the query Insert into .... values $al3 (not '$al3') this works with $al3 = ( !empty($_POST['al3']) ) ? "'{$_POST['al3']}'" : 'NULL'; Link to comment https://forums.phpfreaks.com/topic/282079-introducing-null-value/#findComment-1449489 Share on other sites More sharing options...
vinny42 Posted September 14, 2013 Share Posted September 14, 2013 this works with if you append $a13 into the query then yes, because you are creating the word NULL in SQL. Still don't use empty() because that's not what you need, or should want. (basically just forget that empty() exists, it is quite a pointless function) Link to comment https://forums.phpfreaks.com/topic/282079-introducing-null-value/#findComment-1449491 Share on other sites More sharing options...
DFulg Posted September 15, 2013 Share Posted September 15, 2013 Again, I urge you to use prepared statements in MYSQLi or PDO instead of MYSQL, which is now deprecated. Also again, never insert user data directly into a SQL statement as this is a security risk. Always properly sanitize data before insertion. Link to comment https://forums.phpfreaks.com/topic/282079-introducing-null-value/#findComment-1449545 Share on other sites More sharing options...
vinny42 Posted September 15, 2013 Share Posted September 15, 2013 Again, I urge you to use prepared statements in MYSQLi or PDO instead of MYSQL, which is now deprecated. +1 Link to comment https://forums.phpfreaks.com/topic/282079-introducing-null-value/#findComment-1449593 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.