MYSQL Query Help

[Judahnator]

I have a table similar to this:

 

id | value

-----------

0  | 1,2,3

 

If I select from the table where ID is 0, i get "1,2,3."

Can I make it so when I select where id=0 I get 1, 2, and 3 but on separate rows?

 

Thanks!

[Ddave]

id | value1 | value2| value3

---------------------------------

0  | 1         |  2        |3

 

Can't you make your table like this ?
I'm not sure I get your problem correctly.

[prolemur]

You really shouldn't be storing multiple values in a single column. You should create a whole new table to store these values.

 

One column to reference which id the value belongs to (a foreign key) and the other to hold the value.

 

Then you could:

SELECT * FROM valuesTable WHERE id = 0 LIMIT 2;SELECT * FROM valuesTable WHERE id = 0 ORDER BY id LIMIT 1

[Judahnator]

id | value1 | value2| value3

---------------------------------

0  | 1         |  2        |3

 

Can't you make your table like this ?

I'm not sure I get your problem correctly.

 

No, because the next row might have 1,2,3,4,5,6.

The number of items is unknown.

 

 

 

You really shouldn't be storing multiple values in a single column. You should create a whole new table to store these values.

 

One column to reference which id the value belongs to (a foreign key) and the other to hold the value.

 

Then you could:

SELECT * FROM valuesTable WHERE id = 0 LIMIT 2;SELECT * FROM valuesTable WHERE id = 0 ORDER BY id LIMIT 1

 

Because I dont know how many items will be in the list, it would be difficult to put each piece of data into its own cell. Having 200 cells just in case seems a bit unreasonable...

[Ddave]

Because I dont know how many items will be in the list, it would be difficult to put each piece of data into its own cell. Having 200 cells just in case seems a bit unreasonable...

 

Are you using MySQL only ? Any front-end language used ?

Example: PHP has an explode() function that can separate string values using a delimiter, in this case the ",".

[Guest Judahnator01]

Are you using MySQL only ? Any front-end language used ?

Example: PHP has an explode() function that can separate string values using a delimiter, in this case the ",".

No, MySQL only.

This is for a friend who is using a different system (Adobe cold fusion I think) which does not like data stored like this. Using the PHP explode function would be nice, but that is not an option here.

We might just have to get a little more creative in how we store the data

[Ddave]

No, MySQL only.

This is for a friend who is using a different system (Adobe cold fusion I think) which does not like data stored like this. Using the PHP explode function would be nice, but that is not an option here.

We might just have to get a little more creative in how we store the data

 

Can you create another table ?

There's an idea floating in my head, I'll just write that down then share it here.

[Ddave]

I just realized that my idea is similar to this. I'll try to think of another solution (no promises ! ).

 

You really shouldn't be storing multiple values in a single column. You should create a whole new table to store these values.

 

One column to reference which id the value belongs to (a foreign key) and the other to hold the value.

 

Then you could:

SELECT * FROM valuesTable WHERE id = 0 LIMIT 2;SELECT * FROM valuesTable WHERE id = 0 ORDER BY id LIMIT 1

[Ddave]

No, MySQL only.

This is for a friend who is using a different system (Adobe cold fusion I think) which does not like data stored like this. Using the PHP explode function would be nice, but that is not an option here.

We might just have to get a little more creative in how we store the data

 

Can you share the process/ workflow you're trying to do ? From the input to the storage and output. Maybe we can get some ideas from there.

[prolemur]

Honestly a column should never contain more than a single value. Just create the separate table with a foreign key.

 

Every forum and stack overflow post will agree.

[omniomi]

We might just have to get a little more creative in how we store the data

 

 

What is the data? Why do you have a variable number of values for each id? Is there a maximum number of values an ID could have?

[Judahnator]

What is the data? Why do you have a variable number of values for each id? Is there a maximum number of values an ID could have?

 

It is variable because it is user-inputted data. In this specific form, the user is storing the different ships they own in Star Citizen.

There is no maximum, because the final number of ships that will ever exist is still unknown.

 

 

Can you share the process/ workflow you're trying to do ? From the input to the storage and output. Maybe we can get some ideas from there.

 

The user inputs the ship they own into an auto-completing text input. If they own more than one, there is a button to add more text inputs. There is no limit to the number of ships they can own.

When the user saves the data, each ship must be saved in a different cell because Adobe Coldfusion does not like reading fields with JSON or CSV data in them.

I also cannot add multiple references to another table in the cell. So I thought that maybe if there was a SQL command to break it up life could get easier

[Brenz]

You need foreign keys for this.

 

So I assume you have a user table like:

 

id | name          | email

1  | John Smith | blah@gmail.com

2  | Jane Doe    | hi@Test.com

 

So then you would have a ships table:

 

id | Name     | Description

1 | Ship 1     | Awesome ship

2 | Ship 2     | Its massive

3 | Ship 3     | Kinda rubbish

 

Now finally you need a table to link a user to a ship. This allows you to create a 1 to many many to many relationship between users and ships:

 

id | fk_user_id | fk_ship_id

1  | 1               | 2

2  | 2               | 3

3  | 2               | 1

4  | 1               | 3

 

Now from these 3 tables you can see John owns ships 2 and 3 and Jane has ships 3 and 1. Whenever a person says they own a new ship an entry will be added to the last table

[mcrius]

You need foreign keys for this.

 

So I assume you have a user table like:

 

id | name          | email

1  | John Smith | blah@gmail.com

2  | Jane Doe    | hi@Test.com

 

So then you would have a ships table:

 

id | Name     | Description

1 | Ship 1     | Awesome ship

2 | Ship 2     | Its massive

3 | Ship 3     | Kinda rubbish

 

Now finally you need a table to link a user to a ship. This allows you to create a 1 to many relationship between users and ships:

 

id | fk_user_id | fk_ship_id

1  | 1               | 2

2  | 2               | 3

3  | 2               | 1

4  | 1               | 3

 

Now from these 3 tables you can see John owns ships 2 and 3 and Jane has ships 3 and 1. Whenever a person says they own a new ship an entry will be added to the last table

 

 

Actually this is a many to many relationship. A one to many would be to just add a user_id column to the ships table.

[Brenz]

Actually this is a many to many relationship. A one to many would be to just add a user_id column to the ships table.

 

Good spot

[Judahnator]

You need foreign keys for this.

 

So I assume you have a user table like:

 

id | name          | email

1  | John Smith | blah@gmail.com

2  | Jane Doe    | hi@Test.com

 

So then you would have a ships table:

 

id | Name     | Description

1 | Ship 1     | Awesome ship

2 | Ship 2     | Its massive

3 | Ship 3     | Kinda rubbish

 

Now finally you need a table to link a user to a ship. This allows you to create a 1 to many many to many relationship between users and ships:

 

id | fk_user_id | fk_ship_id

1  | 1               | 2

2  | 2               | 3

3  | 2               | 1

4  | 1               | 3

 

Now from these 3 tables you can see John owns ships 2 and 3 and Jane has ships 3 and 1. Whenever a person says they own a new ship an entry will be added to the last table

 

This looks like it will work.

 

Ill play with it, shouldn't be too bad

[omniomi]

This looks like it will work.

 

Ill play with it, shouldn't be too bad

 

It's pretty simple, you just need to use joins. Using the structure Brenz suggested:

SELECT users.name, ships.name, ships.desc     FROM data    INNER JOIN users ON data.user_id=users.id    INNER JOIN ships ON data.ship_id=ships.id       WHERE users.id=1;

returns

 

name, name, desc

John Smith, Ship 2, Its massive

John Smith, Ship 3, Kinda rubbish