SQL NULL insertion not working

Question

I'm having some odd SQL problems when inserting new rows into a table. I have set some columns to NULL, as I have with another table in my database. Obviously when no data is passed through on insertion it should enter NULL into the record, however currently it is not.

I have checked all settings in comparison with my other table (which is inserting records as NULL correctly) but can't find the issue. The columns appear as below, in both tables.

`statement_1` varchar(255) DEFAULT NULL,

No data is being pasted through (so not a blank space issue). Can anyone suggest why one table is doing as expected but the other is not?

Using below as the insert statement

    $statement_a = "INSERT INTO statements (ucsid, statement_1, statement_2, statement_3, statement_4, statement_5, statement_6, statement_7, statement_8, statement_9, statement_10) VALUES (:ucsid, :statement_1, :statement_2, :statement_3, :statement_4, :statement_5, :statement_6, :statement_7, :statement_8, :statement_9, :statement_10)";
    $q_a = $this->db_connection->prepare($statement_a);
    $q_a->execute(array(':ucsid'=>$ucsid,
                      ':statement_1'=>$statement_1,
                      ':statement_2'=>$statement_2,
                      ':statement_3'=>$statement_3,
                      ':statement_4'=>$statement_4,
                      ':statement_5'=>$statement_5,
                      ':statement_6'=>$statement_6,
                      ':statement_7'=>$statement_7,
                      ':statement_8'=>$statement_8,
                      ':statement_9'=>$statement_9,
                      ':statement_10'=>$statement_10));

You need to provide more information for us to be able to help you. What is the error message? How are you getting the error message? Are you executing a SQL statement from a programming language or directly into the database? Which SQL statement is being executed? — Marco Aurélio Deleu, Oct 18 '15 at 15:48
@MarcoAurélioDeleu No error message as the rest of the entries are successfully being inserted, just when a record is empty the NULL value is not being added. I'm using PHP however this is occuring both through the PHP insertion and the insertion through phpMyAdmin interface. — Liam-FD, Oct 18 '15 at 15:50
@GiorgiNakeuri Simply nothing. No spaces, no text etc. Double click through phpMyAdmin to edit the field and there is nothing there. Rest of entries are added as they should, just when nothing is being entered SQL should be making the entry NULL. — Liam-FD, Oct 18 '15 at 15:57
Try to execute an SQL statement such as `SELECT * FROM my_table WHERE statement_1 IS NULL`. If your record shows up, you have no problem. — Marco Aurélio Deleu, Oct 18 '15 at 15:59
@MarcoAurélioDeleu That's giving me 0 results even though there are 5 different rows with null values — Liam-FD, Oct 18 '15 at 16:02
Try the opposite: `SELECT * FROM my_table WHERE statement_1 IS NOT NULL` — Marco Aurélio Deleu, Oct 18 '15 at 16:08
@MarcoAurélioDeleu That show's all 5 results. How are they NOT NULL records when I can see nothing being inserted through the script/phpmyadmin interface — Liam-FD, Oct 18 '15 at 16:10
Take a look at this http://stackoverflow.com/questions/1391777/how-do-i-insert-null-values-using-pdo — Marco Aurélio Deleu, Oct 18 '15 at 16:12
@MarcoAurélioDeleu Hmmm that seems that it would bind every value to null on insertion. It should be that only when data is not sent (so user does not enter a value for the statement column) that it becomes null — Liam-FD, Oct 18 '15 at 16:15
Check this for more information http://stackoverflow.com/questions/12392424/pdo-bindparam-vs-execute — Marco Aurélio Deleu, Oct 18 '15 at 16:16
@MarcoAurélioDeleu Thanks, I'm currnetly using execute for the other functioning insertion, so I'm still at a lost as to why this one is not working. — Liam-FD, Oct 18 '15 at 16:24
Like specified in the last link, the data type is not processed when using execute function. To make some sort of test, you can try to use `$statement_1 = null;` and see if it works. That way you're specifically setting null to the variable and that might work, but when not specifying a explicit value, the execute method will not be able to process correctly. — Marco Aurélio Deleu, Oct 18 '15 at 16:42
Most probably your `$statement_1` variable at the insert statement is blank (empty, not null) and you are inserting an empty char. Which means for your query to work try `WHERE trim(statement_1) = ''` — Jorge Campos, Oct 18 '15 at 16:43
@JorgeCampos Thanks, this does work however I need to look at stripping statement_1 if the user has not entered anything as I only want statement_1, statement_2, statement_3 etc etc to be selected if they contain data. I can't use IS NOT NULL because the fields that I don't want to show currently contain data — Liam-FD, Oct 18 '15 at 16:59

score 0 · Answer 1 · answered Oct 18 '15 at 17:32

I can not add comments as I am new:

Try a simple INSERT statement using NOT phpmyadmin. Try

http://www.heidisql.com/ OR https://www.mysql.com/products/workbench/

INSERT INTO statements (ucsid) VALUES (123)
INSERT INTO statements (ucsid, statement_1) VALUES (123, NULL)

In both cases the statement_1 should be NULL. Which in your case most likely is not. However that would tell the problem lies in the database table and NOT with php or the php execute method you are using.

Also is the statement_1 field defined as NOT NULL and the default set as NULL? which can not happen.

Try recreating a new database and a new table with no records and than try inserting NULL as values as a test.

Also can you post the SQL of your database and table with Character Set and Collation

Using HeidiSQL the data is inserted correctly, with null values added as they should be. Does this mean the error is coming from somewhere within the script? See sql here: http://pastebin.com/nhp6nu24 — Liam-FD, Oct 18 '15 at 17:55

score 0 · Accepted Answer · answered Oct 24 '15 at 16:21

0

I've fixed the issue by ensuring that NULL is passed through the functions if nothing has been inserted by using the following code

if($_POST['statement_1'] == '') { $statement_1 = NULL; } else { $statement_1 = $_POST['statement_1']; }

answered Oct 24 '15 at 16:21

Liam-FD

67
9

Subin Chalil · Answer 3 · 2015-10-18T16:27:45.310

-1

Here the value passed by the varriable $statement_1 will be ""

Try this query SELECT * FROM my_table WHERE statement_1 ="".You will get rows.

Which means you are assigning some values to $statement_1 else it should be null. Check your code. Hope this helps

edited Oct 18 '15 at 16:27

answered Oct 18 '15 at 16:11

Subin Chalil

3,531
2
24
38

either you should not pass $statement_1 or assign $statement_1=null(default value).Both will work right? – Subin Chalil Oct 18 '15 at 16:37

SQL NULL insertion not working

3 Answers3