1

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 the visibility field. if i want to set visibility to 0, like for example items_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 a item_status of 2, it would return items of status 2 but with the "adjusted" item has the item_number of 3.

is this possible in PHP+MySQL?

normally you would have the item_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 the visibility column). so if i recently added item 6 then deleted items 2 and 4, the next item_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 an item_status of 2, normally you would get an item with an item_id of 5. but that item is only 3rd in the database (due to previously deleted items) so i want to return 3.

Joseph
  • 117,725
  • 30
  • 181
  • 234
  • 1
    It may be the alcohol, but I have no clue what your question actually is. – jprofitt Mar 17 '12 at 00:58
  • 1
    This sounds like a bad idea... You could technically do it with some hideous SQL (hideous performance wise particularly), but what is your end goal? Anywhere you use that id will be inconsistent since the id can change at any time. – Corbin Mar 17 '12 at 00:59
  • 1
    This can be done using `CASE WHEN`. But, can you post why you need it? I'm curious, why do you need the exact count position of an item? – Keyne Viana Mar 17 '12 at 01:34
  • @Keyne this is just to display the number of the item in the whole list, regardless if the list is filtered down. i may soon do an "undo" option that could also put the item in and out that's why a static item number isn't ideal – Joseph Mar 17 '12 at 01:59
  • If it's just for display purposes, why not simply create a count variable in your view? `$i++` – Keyne Viana Mar 17 '12 at 02:04
  • because i won't be returning the whole table. i might return just part of it, with items that are randomly picked out of the table, thus they don't have a contiguous count. – Joseph Mar 17 '12 at 02:06
  • Well, so I think my answer fits your needs partially since the item count is not based on the rows returned... It will be a little more complicated. You'll need a subselect to count first and then the sql to filter it. – Keyne Viana Mar 17 '12 at 03:14
  • You're going to have to do a full table scan and iteration just to print a small set of results. If the table gets large, that's going to be a major problem. – Corbin Mar 17 '12 at 03:21
  • There was a [question asked recently](http://stackoverflow.com/questions/9743121/how-to-reuse-auto-increment-values#comment12392091_9743121) that was pretty similar. You might be able to get some ideas from it. – jprofitt Mar 17 '12 at 03:25
  • @jprofitt that post inquires on how to give it/reuse an ID of a previously deleted item. i'm just after the position of items in a non-contiguous selection. – Joseph Mar 17 '12 at 04:39

2 Answers2

2
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:

+---------+------------+-------------+------------+-------------+
| item_id | item_name  | item_status | visibility | item_number |
+---------+------------+-------------+------------+-------------+
|       1 | Banana     |           1 |          1 |           1 |
|       2 | Apple      |           4 |          1 |           2 |
|       3 | Orange     |           3 |          1 |           3 |
|       4 | Strawberry |           2 |          0 |        NULL |
|       5 | Pear       |           5 |          1 |           4 |
|       6 | Plum       |           4 |          0 |        NULL |
|       7 | Grape      |           2 |          0 |        NULL |
|       8 | Peach      |           3 |          1 |           5 |
|       9 | Papaya     |           6 |          1 |           6 |
|      10 | Melon      |           7 |          0 |        NULL |
+---------+------------+-------------+------------+-------------+
Keyne Viana
  • 6,194
  • 2
  • 24
  • 55
  • i tested this query and works as planned.. however, the query always starts at 1. if i took items with status 3 in your table, item numbers should be 3 and 5 – Joseph Mar 17 '12 at 05:09
  • As I said when commenting on your answer, this query needs to be on a subquery in order to handle properly the item's count when specifying the status. I will change it as soon as I get some free time. – Keyne Viana Mar 17 '12 at 05:24
0

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:

Joseph
  • 117,725
  • 30
  • 181
  • 234