Page 1 of 3

Howto get multiple values from 1 row

Posted: Mon Aug 21, 2006 1:32 pm
by danharibo
Ok, i have a database, and a user, the user can gt given awards, but how can i query the database to Get the actuall awards, each u ser can have more than 1 award. i just can't seem to think of a practical way to do it :/ any ideas?

Posted: Mon Aug 21, 2006 1:33 pm
by GeXus
So you have a users table and an user_awards table... each time a user is given an award you add them to the user_awards table... then do an inner join to get the awards.

Posted: Mon Aug 21, 2006 1:37 pm
by danharibo
Maybe you didn't understand, i have a database with a user table (and a Award table, to store the actuall types of award) and, i need a way to Store the users awards in the database

Posted: Mon Aug 21, 2006 1:39 pm
by feyd
You're referring to a linking table. It has a minimum of two columns: user ID and award ID. The query to perform the matching is two inner joins.

Posted: Mon Aug 21, 2006 1:43 pm
by danharibo
Yes, but i don't see how that Could Let me select Multiple Awards? It means each user could only have 1 award

Posted: Mon Aug 21, 2006 1:44 pm
by feyd
Try it, you'll be surprised.

Posted: Mon Aug 21, 2006 1:55 pm
by danharibo
:roll: i meant, how could i store the awards, Mysql Dont support Arrays

Posted: Mon Aug 21, 2006 1:57 pm
by feyd
I've given you the answer.

It takes three tables: user, awards, and user-awards. Understand now?

Posted: Mon Aug 21, 2006 2:01 pm
by danharibo
i think i understand, but can i store multiple Variables in 1 cell?

Posted: Mon Aug 21, 2006 2:05 pm
by feyd
No, they are stored in separate records. Using a single cell to store the data would result in a very finite number of awards someone could have. Using a linking table, that number is only limited by your operating system's storage limits.

This is called a many-to-many relationship between users and awards. In relational databases that requires a third table to cross link between any number of users and any number of awards.

Posted: Mon Aug 21, 2006 2:06 pm
by Shendemiar
danharibo wrote:i think i understand, but can i store multiple Variables in 1 cell?
Use var_export to transform php-array to a string that is stored in database

Posted: Mon Aug 21, 2006 2:12 pm
by danharibo
AND CAN i do the same to turn a string into a php array?

Posted: Mon Aug 21, 2006 2:28 pm
by Luke
Shendemiar wrote:
danharibo wrote:i think i understand, but can i store multiple Variables in 1 cell?
Use var_export to transform php-array to a string that is stored in database
No No! We do not support bad practices here at devnet! Why store an array in string format and do all that parsing???Image

Do not listen to this fella. Listen to feyd. He was correct. Set up your user table:
user_id, name, other_user_info, etc.

Now set up your award table
award_id, awardname, etc.

Now set up a third table like this:
user_id, award_id

Code: Select all

Now see you can have records like this:
user_id    award_id
1              12                       User one has award #12
1              32                       User one also has award #32
2              32                       User two has award #32 also
2              16                       User two also has award #16
See... this allows you to associate rows from the first table with rows from the second table without the need for storing serialized array strings which is BAD BAD BAD!

If you do not know how to select information given this schema, you need to research the term "sql joins"
Google is a good start... if you are working with mysql, then mysql's site is another excellent resource!

Posted: Mon Aug 21, 2006 3:21 pm
by Shendemiar
The Ninja Space Goat wrote:...without the need for storing serialized array strings which is BAD BAD BAD!
Whats bad about that?

Posted: Mon Aug 21, 2006 3:26 pm
by feyd
Extremely reduced pool size and increased work to search and extract information are a few caveats to storeing into a single record.