0

I have this MYSQL (phpmydamin) database table that looks like this, and I'm trying to retrieve information on the values greater than 1 for a specific row as well as the column names corresponding with those set values. I'm basically trying to find out what item is held by a student and how many.

username      item1 item2 item3 item4 item5 item6 item7

n000111         1      0     1     1    1     1     1

n010554         0      0     3     2    0     0     0

n010555         1      0     4     0    0     1     1

n010556         0      0     0     0    0     0     0

n010557         0      8     0     2    0     1     0

So for example, if i'm sending the username "n010555", to my phpscript to fetch data from this table, I'm expecting feedback like:

n010555
item1 - 1
item3 - 4
item6 - 1
item7 - 1
total - 7

Something like that, I'll really appreciate the help. Thanks in advance.

aynber
  • 22,380
  • 8
  • 50
  • 63
semilogo97
  • 125
  • 1
  • 2
  • 11
  • Do you have a non flattened store of this data somewhere else in the DB? If not, this isn't going to be an easy query given the database choice. MySQL isn't great for anything like this. – Jacobm001 Oct 27 '17 at 17:56

1 Answers1

1

What you need to do is unpivot your data. Unfortunately, your data is stored in MySQL, which doesn't have such a feature.

This can be accomplished using a cross join, but it's not particularly efficient. If you don't have a huge amount of data, this may not be an issue, but if you have a nontrivial amount of data you may need to consider ETLing the data or changing your storage design.

Bluefeet has a good answer on how you can accomplish this. An adaptation for your solution would look something like:

select t.id,
  c.col,
  case c.col
    when 'item1' then item1
    when 'item2' then item2
    when 'item3' then item3
    when 'item4' then item4
    when 'item5' then item5
    when 'item6' then item6
    when 'item7' then item7
  end as data
from yourtable t
cross join
(
  select 'item1' as col
  union all select 'item2'
  union all select 'item3'
  union all select 'item4'
  union all select 'item5'
  union all select 'item6'
  union all select 'item7'
) c
where
  username = :username
Jacobm001
  • 4,431
  • 4
  • 30
  • 51
  • Hi Jacob, Thank you. Do you think manually converting my columns to rows and rows to columns in my table will make this easier to accomplish? – semilogo97 Oct 27 '17 at 19:49
  • @David Generally, yeah. Flattened data is fine for reporting, but it isn't a great way to store data you want to query like this. – Jacobm001 Oct 27 '17 at 19:50
  • Okay then, Thank you very much. I'll do just that. – semilogo97 Oct 27 '17 at 20:04