SELECT statement for NOT NULL values is returning NULL values.

Question

I have PHP code that gets values using a $_POST and then inserts into a DB. All of the fields are input based except the CU00, etc. because it denotes a primary key .

Now suppose user enters just one row:

$ sql="INSERT INTO weekly
VALUES
 ('$_POST[uactual]','$_POST[utarget]','CU001','$a1','$_POST[ucomment]',NOW())
,('$_POST[uactual2]','$_POST[utarget2]','CU002','$a2','$_POST[ucomment2]',NOW())
,('$_POST[uactual3]','$_POST[utarget3]','CU003','$a3','$_POST[ucomment3]',NOW())
,('$_POST[dactual]','$_POST[dtarget]','CD001','$b1','$_POST[dcomment]',NOW())
,('$_POST[dactual2]','$_POST[dtarget2]','CD002','$b2','$_POST[dcomment2]',NOW())
,('$_POST[dactual3]','$_POST[dtarget3]','CD003','$b3','$_POST[dcomment3]',NOW())
,('$_POST[iactual]','$_POST[itarget]','CI001','$c1','$_POST[icomment]',NOW())
,('$_POST[iactual2]','$_POST[itarget2]','CI002','$c2','$_POST[icomment2]',NOW())
,('$_POST[iactual3]','$_POST[itarget3]','CI003','$c3','$_POST[icomment3]',NOW())
,('$_POST[ractual]','$_POST[rtarget]','CR001','$d1','$_POST[rcomment]',NOW())
,('$_POST[ractual2]','$_POST[rtarget2]','CR002','$d2','$_POST[rcomment2]',NOW())
,('$_POST[ractual3]','$_POST[rtarget3]','CR003','$d3','$_POST[rcomment3]',NOW())";

SQL TABLE

ACTUAL|TARGET|KEY |SIGNAL |TIME 
NULL    NULL  CU001 NULL   00:00
NULL    NULL  CU002 NULL   00:00
NULL    NULL  CU003 NULL   00:00
NULL    NULL  CU004 NULL   00:00
100      200  CU005  300   00:00

I want to do a select where only the rows with signal are selected. But when I do a:

   SELECT *
    FROM TABLE
    WHERE 
    'signal' IS NOT NULL

I get all the rows returned. It is as if there are no NULL values in my table .

Used a isset ... works wonderfully now

Sai Vignesh
– Sai Vignesh

2013-03-22 11:20:36 +00:00
Commented Mar 22, 2013 at 11:20 — Sai Vignesh
– Sai Vignesh, Commented Mar 22, 2013 at 11:20

Andrey · Accepted Answer · 2013-03-19 22:48:26Z

3

   SELECT *
    FROM TABLE
    WHERE 
    signal IS NOT NULL

'signal' is just string literal which is indeed NOT NULL. You possibly meant ` instead of '.

answered Mar 19, 2013 at 22:48

Andrey

60.3k12 gold badges127 silver badges169 bronze badges

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

1 Comment

Andrey Over a year ago

@SaiVignesh no, 'signal' is string, not int.

Lemmings19 · Accepted Answer · 2013-03-19 23:36:08Z

0

You are putting empty strings into the DB.

Do this:

(empty($_POST['uactual']) ? 'NULL' : '\'' . $_POST['uactual'] . '\'')

For all $_POST variables. Then you can be sure that there will be NULL values.

Please don't do this: $_POST[uactual]! PHP will search for defined val first! Do $_POST['uactual'] Use ' always!

Also, your select is wrong. ` not '. If you don't believe do such query:

SELECT 'signal' FROM TABLE

This is correct:

SELECT `signal` FROM TABLE or
SELECT signal FROM TABLE

This will return you string 'signal', not the `signal` column.

 WHERE 
    'signal' IS NOT NULL

This will always be true! This why it returns whole data. You made 2 mistakes then :] In inserting and in selecting data.

edited Mar 19, 2013 at 23:36

Lemmings19

1,4913 gold badges24 silver badges39 bronze badges

answered Mar 19, 2013 at 22:57

imclickingmaniac

1,5731 gold badge17 silver badges31 bronze badges

5 Comments

Sai Vignesh Over a year ago

<?php (empty($a1) ? 'NULL' : '\'' . $a1 . '\'')?> I did this befor inseret bcos this is the signal column but never worked, should I change the SQL now

imclickingmaniac Over a year ago

No matter where you will do that. Your insert string should contain: NULL (without '') or 'somestring' (with '') or 888 (without '' if it should be numbers). You can also do (int) $x. Then you will be sure that the value will be integer (0 if not numeric string). I would do both (int) + empty :]

imclickingmaniac Over a year ago

empty($x) ? NULL : (int) $x Hope this helps.

Sai Vignesh Over a year ago

Fatal error: Call to undefined function int() <?php (empty($a1) ? 'NULL' : '\'' . int($a1) . '\'')?>

imclickingmaniac Over a year ago

SELECT * FROM TABLE WHERE signal IS NOT NULL without '. If you inserted NULLs like you should. In other case WHERE signal != '' or WHERE signal != 0 or even all at once WHERE signal IS NOT NULL AND signal != '' AND signal != 0. Thnx for correcting my answer Lemmings19 :)

Aleš Krajník · Accepted Answer · 2013-03-19 22:49:14Z

0

You are not inserting any NULLs. You are inserting empty strings at best.

Also, you should use some validation of your inputs.

answered Mar 19, 2013 at 22:49

Aleš Krajník

2173 silver badges11 bronze badges

5 Comments

Aleš Krajník Over a year ago

I cannot add comments to below POST, but the answer below is wrong. The quotes around string are not needed, MySQL will not understand that as a string but rather as a column name.

Aleš Krajník Over a year ago

The problem is that you are not inserting NULLs. You are inserting strings - you need to validate your input and convert empty strings to NULLs where appropriate.

Sai Vignesh Over a year ago

oh How do I filter empty string then

imclickingmaniac Over a year ago

when inserting data. I posted the empty() part above. And there is huge difference between ' and ` in sql! ' is string ` is table!

Sai Vignesh Over a year ago

<?php (empty($a1) ? 'NULL' : '\'' . $a1 . '\'')?> I did this befor inseret bcos this is the signal column but never worked, should I change the SQL now

Collectives™ on Stack Overflow

SELECT statement for NOT NULL values is returning NULL values.

3 Answers 3

1 Comment

5 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related