php - sql item number based on the items that are "visible"
Get the solution ↓↓↓i have this table:
item_id item_name item_status visibility "item_number"
6 foo6 3 1 4
5 foo5 2 1 3
4 foo4 1 0 x
3 foo3 3 1 2
2 foo2 2 0 x
1 foo1 1 1 1
the items are "in mercy" of thevisibility
field. if i want to set visibility to 0, like for exampleitems_id
's 2 and 4, i would like the "item_number
" (not a real column) to adjust accordingly.
if i were to select items that have aitem_status
of 2, it would return items of status 2 but with the "adjusted" item has theitem_number
of 3.
is this possible in PHP+MySQL?
normally you would have theitem_id
as the id for the item. however, the issue is that MySQL auto-increment would still continue even if you deleted an item (in this case, it's thevisibility
column). so if i recently added item 6 then deleted items 2 and 4, the nextitem_id
would have a 7 but there are only 4 items in the database. for these "visible" items, i want to have item numbers 1, 2, 3 and 4 instead of ids 1, 3, 5, and 6.
also, if i were to get items with anitem_status
of 2, normally you would get an item with anitem_id
of 5. but that item is only 3rd in the database (due to previously deleted items) so i want to return 3.
Answer
Solution:
SELECT item_id, item_name, item_status, visibility,
CASE WHEN (visibility !=0) THEN @itemCount := @itemCount +1 END AS item_number
FROM items
JOIN (SELECT @itemCount :=0) AS vars
ORDER BY item_id
This will result in:
+---------+
Answer
Answer
Answer
Solution:
Figured it out and it (unfortunately) needed a nested query:
SELECT * //select all columns
FROM ( //from a derived table
SELECT
b.*, //select all columns
@itemCount:=@itemCount +1 AS item_number //and a counter column named item_number
FROM items b, (SELECT @itemCount :=0) r //from items table and with the counter column initialized as 0
WHERE visibility != 0 //select only those visible
) AS b
LIMIT 0, 30 //limit to 30 rows
what it did:
- selected all rows (this will have a performance hit) that have visible items
- since only visible items are shown, they are the only ones "counted"
- the above query is a full result set and is now a derived table for the parent select
- the parent select then limits the rows according to how many is needed and preserving the count.
so if i wanted to show 30 rows starting from 1000, it will show 30 visible items starting at 1000 with the count starting with 1000
references:
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: property [id] does not exist on this collection instance.
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.