How to display product stock desc wise

Question

0.00/5 (No votes)

See more:

I have a website where users can get books on rent, user can order books as per their requirements. There is one business logic, where we are showing the count of the same name of books, with their respective different book id. All works fine, but when the user orders books, the user gets the same id book, which other users have bought. To solve this I made 1 column(book_stock) where, if the user buys the book book_stock column will update to 0 else it will stay 1. This logic works fine, but when all books are purchased by the user, the book gets invisible from the website, because all books book_stock columns are updated to 0. But I want to show that book on the front end as out of stock, so user can get to know we have books but currently it's out of stock.

What I have tried:

Below is a MySQL query which is used to display books on frontend

SQL

SELECT DISTINCT book_name, book_id, cats_id, book_author, book_author1, book_author2, book_author3, book_author4, book_author5, book_author6, book_rating, book_front_img, book_status, book_lan, book_stock, trends FROM `book_table` HAVING cats_id = '14' AND book_status = 1 ORDER BY book_stock DESC

I tried to use order by clause to show the book as per book_stock = 1, but it's not working please let me know where I'm making things wrong.

Posted 25-Oct-22 4:03am

Rahul Gupta 2022

Updated 1-Nov-22 3:04am

Add a Solution

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)

CHill60 · Answer 1 · 2022-11-01T03:04:00

Your main problem is giving every individual book a unique id. You appear to have a model that looks a little bit like this..

SQL

declare @book_table table (book_id int identity(1,1), book_name nvarchar(255), book_author nvarchar(255), cats_id int, book_stock int);
insert into @book_table(book_name, book_author, cats_id, book_stock) values
('VB6 - The recurring nightmare','CHill60', 14,1),
('VB6 - The recurring nightmare','CHill60', 14,1),
('VB6 - The recurring nightmare','CHill60', 14,1);
-- sell the first book
update @book_table set book_stock = 0 where book_id = 1;
select * from @book_table;

Giving you data that looks like this

book_id	book_name						book_author	cats_id	book_stock
1		VB6 - The recurring nightmare	CHill60		14		0
2		VB6 - The recurring nightmare	CHill60		14		1
3		VB6 - The recurring nightmare	CHill60		14		1

If you wanted to see how much of each book name is left then you could do something like this

SQL

select book_name , book_author, cats_id, SUM(book_stock) as current_stock
from @book_table
group by book_name , book_author, cats_id
order by SUM(book_stock) desc;

Note the use of GROUP BY so that we can sum the "flag" book_stock over all the rows.

But that is not a good database model. It is more usual to give the "book" the id number - i.e. each book_name has an id, and book_stock represents the current number of copies of that book in stock. E.g.

SQL

declare @book_table table (book_id int identity(1,1), book_name nvarchar(255), book_author nvarchar(255), cats_id int, book_stock int);
insert into @book_table(book_name, book_author, cats_id, book_stock) values
('VB6 - The recurring nightmare','CHill60', 14,3);

select book_name , book_author, cats_id, book_stock from @book_table;

The data looks like

thisbook_name					book_author	cats_id	book_stock
VB6 - The recurring nightmare	CHill60		14		3

To sell a book, deduct one from the current stock e.g.

SQL

-- sell a book
update @book_table set book_stock = book_stock - 1 where book_id = 1;

And to display you could do something like this

SQL

select book_name , book_author, cats_id, case when book_stock = 0 then 'Out of Stock' else cast(book_stock as varchar(30)) end as current_stock
from @book_table
order by book_stock desc;

Note only an order by is required now - no grouping required.

N.B. I have included the 'Out of Stock' in the SQL just to show how easy it is to still list the book but show it as out of stock. You would not normally do this in the SQL but have something in your UI layer that does that instead.