[SOLVED] How to store multiple items purchased in a single transaction?

How to store multiple items purchased in a single transaction?

I am new to MySQL.My database is retail product bought by customer or webview data. Example:

ID  Records
R1  {iphone5s,levis,Dell,Dior,amazon}
R2  {levis,Dell,amazon,Starbucks,google glass}
R3  {iphone5s,Dell,Dior,google glass}
R4  {iphone5s,levis,Starbucks}
R5  {iphone5s,levis,Dell,google glass}

I want to store this data into database.I store this data as:

ID    iphone5s   levis   Dell    Dior   amazon  Starbucks   google glass

R1       1         1      1        1       1         0          0

R2       0         1      1        0       1         1          1

R3       1         0      1        1       0         0          1

R4       1         1      0        0       0         1          0

R5       1         1      1        0       0         0          1


create table retail(ID varchar(50),
                    iphone5s int,
                    levis int,
                    Dell int,
                    Dior int,
                    amazon int,
                    Starbucks int,
                    googleglass int);

insert into retail
values ('r1',1,1,1,1,1,0,0), ('r2',0,1,1,0,1,1,1);

insert into retail
values ('r3',1,0,1,1,0,0,1),('r4',1,1,0,0,0,1,0),('r5',1,1,1,0,0,0,1);

Now I want to retrieve the ID which having iphone5s and store the ids. Similar I want collect ids having each columnname and store the corresponding id.

Using java I collect the column name by the following code:

ResultSet rs = stmt.executeQuery("SELECT * FROM retail");
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int i = 2; i < columnCount + 1; i++ ) {
  String name = rsmd.getColumnName(i);
}

Now I want to select ID from retail where columnname=1.

Columnname should change every time.

Solution

From a comment to the question:

Is any other better way to store the above original data in database system?

Absolutely. You should store each item purchased in a separate row, like this:

ID  item
--  ------------
R1  iphone5s
R1  levis
R1  Dell
R1  Dior
R1  amazon
R2  levis
R2  Dell
R2  amazon
R2  Starbucks
R2  google glass
...

That makes it easier to query (because the column names are constant) and you won't have to alter your database structure if you ever need to add a new product. You could always "pivot" the data into the format you have now, but you shouldn't store it that way.