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 https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — nbk, Feb 09 '20 at 00:03
Not using prepared statements is completely unacceptable coding. — Honeyboy Wilson, Feb 09 '20 at 00: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, Feb 09 '20 at 00:35

Ted Hopp · Answer 1 · 2020-02-09T02:31:41.973

2

Try

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

edited Feb 09 '20 at 02:31

answered Feb 09 '20 at 00:01

Ted Hopp

222,293
47
371
489

Tried that also,it also stores NULL as a text and not as real null. – ikiK Feb 09 '20 at 00:26
1

Null should not have quotes around it. – Honeyboy Wilson Feb 09 '20 at 00:27

nbk · Accepted Answer · 2020-02-09T00:26:20.690

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 09 '20 at 00:26

answered Feb 09 '20 at 00:19

nbk

20,484
4
19
35

Tried that,it stores NULL as a text and not as real null. – ikiK Feb 09 '20 at 00:24
1

i edited my answer remove simply the ticks around NULL – nbk Feb 09 '20 at 00:26

score 1 · Answer 3 · answered Feb 09 '20 at 14:39

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!

Converting empty string into null

3 Answers3