Unable to catch null data properly

Question

0.00/5 (No votes)

See more:

Hi everyone
I'm trying to catch all data from MySQL to check data already exists or not
if data already exists then it should update else insert new data.
I have 7 to 8 conditions in where clause, some of them are null
now the issue is
when i run query its not catching null column thats why its insert new data instead of updating
below is my code

What I have tried:

PHP

$fetch_stock = mysqli_query($connection, "SELECT * FROM `book_stock` WHERE book_name = '$book_name' AND author_id = $book_author AND author_id1 = $book_author1 AND author_id2 = $book_author2 AND author_id3 = $book_author3 AND author_id4 = $book_author4 AND author_id5 = $book_author5 AND author_id6 = $book_author6");
               if (mysqli_num_rows($fetch_stock) > 0) {
                   echo "UPDATE book_stock SET stock_count = stock_count + 1 WHERE book_name = '$book_name' AND author_id = $book_author AND author_id1 = $book_author1 AND author_id2 = $book_author2 AND author_id3 = $book_author3 AND author_id4 = $book_author4 AND author_id5 = $book_author5 AND author_id6 = $book_author6";
                   mysqli_query($connection, "UPDATE book_stock SET stock_count = stock_count + 1 WHERE book_name = '$book_name' AND author_id = $book_author AND author_id1 = $book_author1 AND author_id2 = $book_author2 AND author_id3 = $book_author3 AND author_id4 = $book_author4 AND author_id5 = $book_author5 AND author_id6 = $book_author6");
               } else {
                   echo "INSERT INTO book_stock (book_name, author_id, author_id1, author_id2, author_id3, author_id4, author_id5, author_id6, stock_count) value ('$book_name', $book_author,$book_author1, $book_author2, $book_author3, $book_author4, $book_author5, $book_author6, 1)";
                   mysqli_query($connection, "INSERT INTO book_stock (book_name, author_id, author_id1, author_id2, author_id3, author_id4, author_id5, author_id6, stock_count) value ('$book_name', $book_author,$book_author1, $book_author2, $book_author3, $book_author4, $book_author5, $book_author6, 1)");
               }

when i fetch query its look like this

INSERT INTO book_stock (book_name, author_id, author_id1, author_id2, author_id3, author_id4, author_id5, author_id6, stock_count) value ('cat demo1', 1,2, 3, 4, NULL, NULL, NULL, 1)

Posted 2-Jan-22 21:50pm

Rahul Gupta 2022

Updated 3-Jan-22 1:19am

v2

Add a Solution

Comments

Richard MacCutchan 3-Jan-22 3:53am

You need to use the debugger to see exactly what values you are passing into the SELECT clause and what is being returned. There is nothing we can tell from just looking at that code.

0x01AA 3-Jan-22 4:14am

Quote: 'I have 7 to 8 conditions in where clause, some of them are null'
Does this mean some of the parameters are NULL? If yes, I think you need to test that something like


... AND (NOT $book_author6 IS NULL AND (author_id6 = $book_author6)) ...

for alle these parameters.

Rahul Gupta 2022 3-Jan-22 4:22am

yes some parameters are null

Richard MacCutchan 3-Jan-22 4:31am

Do what I suggested above, and actually inspect what happens in your code. You will never solve this issue by guessing.

0x01AA 3-Jan-22 4:37am

So finally you compare something like 'NULL = NULL' which is undefined (and for most SQL server evaluates to false). Try what I described above ;)

Rahul Gupta 2022 3-Jan-22 4:40am

i tried what you suggest, but i got same result

0x01AA 3-Jan-22 4:46am

Sorry, the SQL fragment above is wrong. Should be more (too early for me ;) ):


... AND ($book_author6 IS NULL OR (author_id6 = $book_author6)) ...

...and this for _all_ parameters which can be NULL

Rahul Gupta 2022 3-Jan-22 4:56am

Thank you so much.
you made my date

0x01AA 3-Jan-22 5:36am

You are welcome :-)
Btw. I added it as answer ;)

Rahul Gupta 2022 3-Jan-22 4:45am

SELECT * FROM `book_stock` WHERE book_name = 'cat demo1' AND author_id = 1 AND (NOT 2 IS NULL AND (author_id1 = 2)) AND (NOT 3 IS NULL AND (author_id2 = 3)) AND (NOT 4 IS NULL AND (author_id3 = 4)) AND (NOT NULL IS NULL AND (author_id4 = NULL)) AND (NOT NULL IS NULL AND (author_id5 = NULL)) AND (NOT NULL IS NULL AND (author_id6 = NULL))
i got this

Richard MacCutchan 3-Jan-22 5:16am

AND (NOT 2 IS NULL
How can 2 be NULL, and how can NULL not be NULL?

3 solutions

Solution 1

We can't help you: we have no access to your code while it is running or to your data, and you need both in order to start working out what is going on. So start with the debugger in your system (or here: Best 6 PHP Debugging Tools of 2021 For Developers[^] if you don't have one).

But ... Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Posted 2-Jan-22 22:13pm

OriginalGriff

Solution 2

When SQL parameters can be NULL then at the end the SQL Server can come to a situation where it compares the NULL parameter with NULL values from the table you query.

Comparing NULL = NULL is pretty undefined, respectively depends havy on the SQL server in use. For some of them you can configure how it should behave and others do evaluate NULL = NULL as false.

One solution to overcome this is to check also the respective parameters in the WHERE clause like this:

SQL

... AND ($book_author6 IS NULL OR (author_id6 = $book_author6)) ...

Posted 3-Jan-22 0:03am

0x01AA

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Accepted Answer · 2022-01-03T01:19:00

Solution 3

You can use IFNULL MySQL function for checking null values. IFNULL gives flexibility to return an alternate value if the underlying value is null.

I have modified your SQL assuming that underlying table field names are of type int.

SELECT * FROM `book_stock` 
WHERE book_name = '$book_name' 
AND author_id = $book_author 
AND author_id1 = $book_author1 
AND author_id2 = $book_author2 
AND author_id3 = $book_author3 
AND IFNULL(author_id4, 0)= IFNULL($book_author4, 0)
AND IFNULL(author_id5, 0) = IFNULL($book_author5, 0)
AND IFNULL(author_id6, 0) = IFNULL($book_author6, 0)

In addition, you need to see @OG comments regarding SQL Injection Attacks.

Posted 3-Jan-22 1:19am

_Asif_

Comments

0x01AA 3-Jan-22 7:23am

That is a good one, have a 5

_Asif_ 3-Jan-22 7:40am

thanks

Rahul Gupta 2022 3-Jan-22 9:12am

Hi sorry for asking you a silly question,
Why do you write 0 in else part?

_Asif_ 4-Jan-22 3:37am

IFNULL would replace null with 0 for both left and right sides, which leads to 0 = 0, which is true hence null rows would get returned in the resultset.

Rahul Gupta 2022 5-Jan-22 6:41am

Hi i got it
but not only i want to display data by this logic but also update book stock table..
if i use your method then during update it will update null column with 0