Best way for saving infinit playlists (arrays) into db? (php mySql)

Question

0

1

So client gives me a string like "1,23,23,abc,ggg,544,tf4," from user 12 . There can be infinit number of elements with no spaces just value,value,... structure. I have users table (with users uId(key), names etc). I have streams table with ( sId(key), externalID, etc values). User sends me externalId's. And I need to hawe externalId's in play list (not my sId's). I need some way to store such array into my DB and be able to get it from DB.

I need to be able to do 2 things

return such string back to user
be able to get na array from it like {1; 23; 23; abc; ggg; 544; tf4;}

So what is best method (best here means shourt(small amount of) code)

to store such data into db
to retrivew stored tata in bouth ways shown

flag	asked May 2 at 20:50 Ole Jak 1,7451215 55% accept rate

3

First you need an infinitely large hard disk... – Mark Byers May 2 at 20:55

Learning to spell might be a good idea too.. – Chacha102 May 2 at 21:03

I have it... ok let us say play list for no more than 10^10 items=) – Ole Jak May 2 at 21:03

Do you need to perform any sort of processing on the individual IDs? If not then the simplest solution is to literally store the string as its received. – cfeduke May 4 at 21:38

Crozin · Answer 1 · 2010-05-02 21:01:13Z

That's a classical one-to-many relationship. One user has many external ids:

User:         id, name
UserExternal: user_id, id # both fields as PK, id as CHAR

To fetch every external id connected to the user just execute the following query:

SELECT u.id, u.name, ue.id AS external
FROM user u
LEFT JOIN user_external ue ON u.id = ue.user_id

wlk · Answer 2 · 2010-05-04 21:31:55Z

I think, something like this should work:
User: user_id Value: user_id, value, id
And according to your example 1,23,23,abc,ggg,544,tf4 from user 12, you will have:

TABLE User  
user_id  
12

and

TABLE Value  
user_id | value | id  
12      | 1     | 0  
12      | 23    | 1  
12      | 23    | 2  
12      | ggg   | 4  
12      | abc   | 3  
...

id will be used for ordering list for each user, so if you want do retrieve it, just use this query: SELECT value FROM VALUE WHERE user_id = 12 ORDER BY id

Best way for saving infinit playlists (arrays) into db? (php mySql)

2 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php mysql arrays string db or ask your own question.

Hello World!

Related