How to check value in where clause

Question

0.00/5 (No votes)

See more:

I have 2 table books and book_Stock - (which hold all book stock lists).
i have book_name, author_id, author_id1,author_id2,author_id3,author_id4,author_id5,author_id6 -- column name.
on the basis of book name and author I count stock
When I insert data into books and book stock its works fine.
for the update, I wrote a query that shows book name and author id already exist on the book_stock table or not. if it already exists then it will add 1 in the stock column else it will insert a new column.
but the problem is when I tried to check data already exists or not its not show, its direct insert new

What I have tried:

PHP

$update_book = "UPDATE `books` SET book_unique_id = '$book_unique_no', book_name = '$book_name', cats_id = $cats_id, book_author = '$book_author', book_author1 = '$book_author1', book_author2 = '$book_author2', book_author3 = '$book_author3', book_author4 = '$book_author4', book_author5 = '$book_author5', book_author6 = '$book_author6', book_publisher = '$book_publisher', book_publisher1 = '$book_publisher1', book_total_page = '$book_total_page', book_isbn = '$book_isbn', book_binding = '$book_binding', book_weight = '$book_weight', book_rating = '$book_rating', book_rack_no = '$book_rack_no', book_desc = '$book_desc', book_price = '$book_price' WHERE book_id = $book_id";
        }
        $check_data = "SELECT * FROM book_stock WHERE book_name = '$book_name' AND author_id = $book_author";
        if ($book_author1 != "NULL" || $book_author1 != '') {
            $check_data .= " AND author_id1 = $book_author1";
        }
        if ($book_author2 != "NULL" || $book_author2 != '') {
            $check_data .= " AND author_id2 = $book_author2";
        }
        if ($book_author3 != "NULL" || $book_author3 != '') {
            $check_data .= " AND author_id3 = $book_author3";
        }
        if ($book_author4 != "NULL" || $book_author4 != '') {
            $check_data .= " AND author_id4 = $book_author4";
        }
        if ($book_author5 != "NULL" || $book_author5 != '') {
            $check_data .= " AND author_id5 = $book_author5";
        }
        if ($book_author6 != "NULL" || $book_author6 != '') {
            $check_data .= " AND author_id6 = $book_author6";
        }
        // echo $check_data;
        $query = mysqli_query($connection, $check_data) or mysqli_errno($connection);
        if (mysqli_num_rows($query) > 0) {
            $update_stock = "UPDATE `book_stock` SET stock_count = stock_count + 1 WHERE book_name = '$book_name' AND author_id = $book_author ";
            if ($book_author1 != "NULL" && $book_author1 != '') {
                $update_stock .= " AND author_id1 = $book_author1 ";
            }
            if ($book_author2 != "NULL" && $book_author2 != '') {
                $update_stock .= " AND author_id2 = $book_author2 ";
            }
            if ($book_author3 != "NULL" && $book_author3 != '') {
                $update_stock .= " AND author_id3 = $book_author3 ";
            }
            if ($book_author4 != "NULL" && $book_author4 != '') {
                $update_stock .= " AND author_id4 = $book_author4 ";
            }
            if ($book_author5 != "NULL" && $book_author5 != '') {
                $update_stock .= " AND author_id5 = $book_author5 ";
            }
            if ($book_author6 != "NULL" && $book_author6 != '') {
                $update_stock .= " AND author_id6 = $book_author6 ";
            }
            $update_copies = mysqli_query($connection, $update_stock);
        } else {
            $book_author1 = (!empty($book_author1)) ? $book_author1 :  "NULL";
            $book_author2 = (!empty($book_author2)) ? $book_author2 :  "NULL";
            $book_author3 = (!empty($book_author3)) ? $book_author3 :  "NULL";
            $book_author4 = (!empty($book_author4)) ? $book_author4 :  "NULL";
            $book_author5 = (!empty($book_author5)) ? $book_author5 :  "NULL";
            $book_author6 = (!empty($book_author6)) ? $book_author6 :  "NULL";
            $insert_copies = 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) VALUES ('$book_name', $book_author, $book_author1, $book_author2, $book_author3, $book_author4, $book_author5, $book_author6, 1)");
        }

Posted 1-Jan-22 21:16pm

Rahul Gupta 2022

Updated 1-Jan-22 21:57pm

Add a Solution

Comments

Richard MacCutchan 2-Jan-22 3:22am

I think the following expression is wrong:

        if ($book_author1 != "NULL" || $book_author1 != '') {

I think it should be using the AND operator thusd:

        if ($book_author1 != "NULL" && $book_author1 != '') {

1 solution

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)

OriginalGriff · Answer 1 · 2022-01-01T21:57:00

In addition to what Richard has said, don't do it like that! 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?