CODEWITHSUNDEEP
phpmysql
Sai Vignesh
Sai Vignesh

Reputation: 41

SELECT statement for NOT NULL values is returning NULL values.

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 .

Upvotes: 1

Views: 791

Answers (3)

imclickingmaniac
imclickingmaniac

Reputation: 1563

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.

Upvotes: 0

Aleš Krajník
Aleš Krajník

Reputation: 217

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

Also, you should use some validation of your inputs.

Upvotes: 0

Andrey
Andrey

Reputation: 60065

   SELECT *
    FROM TABLE
    WHERE 
    signal IS NOT NULL

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

Upvotes: 3

Related Questions