Converting empty string into null

Question

I have a form that POST value keeps coming as "" empty string into a unique SQL field. It has to be unique as the field is optional but jet it can not have 2 same values. The unique value does allow null multiple values. I don't even want to say what I tried to do, I'm trying to fix this for last few days. Closest that I got is putting '$OIB'=IF('$OIB'='',NULL,'$OIB' into NSERT INTO statement, then i get null values into a database but for some reason when the number is entered into OIB form field it puts number 1 into a database...

$result = mysqli_query($conn, "SELECT OIB, NAZIV FROM STRANKEP WHERE OIB = '$OIB' OR NAZIV = '$NAZIV'");

if(mysqli_num_rows($result) == 0) {
     // row not found, do stuff...
     $sql = "INSERT INTO STRANKEP (NAZIV, OIB, EMAIL, ADRESA, TELEFON) VALUES ('$NAZIV', CASE WHEN '$OIB' = '' THEN 'NULL', '$EMAIL', '$ADRESA', '$TELEFON')";
    $query = mysqli_query($conn, $sql);

This solution gets me null but not the real $OIB when entered into form, it just puts number 1.

$result = mysqli_query($conn, "SELECT OIB, NAZIV FROM STRANKEP WHERE OIB = '$OIB' OR NAZIV = '$NAZIV'");

if(mysqli_num_rows($result) == 0) {
     // row not found, do stuff...
     $sql = "INSERT INTO STRANKEP (NAZIV, OIB, EMAIL, ADRESA, TELEFON) VALUES ('$NAZIV', '$OIB'=IF('$OIB'='',NULL,'$OIB'), '$EMAIL', '$ADRESA', '$TELEFON')";
    $query = mysqli_query($conn, $sql);

Thank you in advance for the help.

Please reap up on Sql injection stackoverflow.com/questions/60174/… — nbk
– nbk, Commented Feb 9, 2020 at 0:03
Not using prepared statements is completely unacceptable coding. — Honeyboy Wilson
– Honeyboy Wilson, Commented Feb 9, 2020 at 0:29
I'm a noob, sorry, also this is only for the local networks and few people using the database. But I will make changes. — ikiK
– ikiK, Commented Feb 9, 2020 at 0:35

Ted Hopp · Accepted Answer · 2020-02-09 02:31:41Z

3

Try

CASE '$OIB' WHEN '' THEN NULL ELSE '$OIB' END

edited Feb 9, 2020 at 2:31

answered Feb 9, 2020 at 0:01

Ted Hopp

235k48 gold badges412 silver badges533 bronze badges

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

Comments

nbk · Accepted Answer · 2020-02-09 00:26:20Z

2

You can use also IF Clause

Like so

INSERT INTO STRANKEP (NAZIV, OIB, EMAIL, ADRESA, TELEFON) VALUES 
('$NAZIV', IF('$OIB' = '', NULL,'$OIB'), '$EMAIL', '$ADRESA', '$TELEFON');

But as mentioned in my comment use prepared statements like in PDO https://phpdelusions.net/pdo#prepared

edited Feb 9, 2020 at 0:26

answered Feb 9, 2020 at 0:19

nbk

49.6k8 gold badges32 silver badges53 bronze badges

2 Comments

ikiK Over a year ago

Tried that,it stores NULL as a text and not as real null.

nbk Over a year ago

i edited my answer remove simply the ticks around NULL

Gordon Linoff · Accepted Answer · 2020-02-09 14:39:44Z

2

I would recommend nullif(). It is a built-in standard function to do exactly this:

nullif(?, '')

Note: Do not munge queries with constant values. That makes the code subject to SQL injection attacks. And it can introduce very hard-to-debug errors. Use parameters!

answered Feb 9, 2020 at 14:39

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Converting empty string into null

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related