Howto get multiple values from 1 row

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

danharibo
Forum Commoner
Posts: 76
Joined: Thu Aug 17, 2006 8:56 am

Howto get multiple values from 1 row

Post 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?
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post 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.
danharibo
Forum Commoner
Posts: 76
Joined: Thu Aug 17, 2006 8:56 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
danharibo
Forum Commoner
Posts: 76
Joined: Thu Aug 17, 2006 8:56 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Try it, you'll be surprised.
danharibo
Forum Commoner
Posts: 76
Joined: Thu Aug 17, 2006 8:56 am

Post by danharibo »

:roll: i meant, how could i store the awards, Mysql Dont support Arrays
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I've given you the answer.

It takes three tables: user, awards, and user-awards. Understand now?
danharibo
Forum Commoner
Posts: 76
Joined: Thu Aug 17, 2006 8:56 am

Post by danharibo »

i think i understand, but can i store multiple Variables in 1 cell?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Post 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
danharibo
Forum Commoner
Posts: 76
Joined: Thu Aug 17, 2006 8:56 am

Post by danharibo »

AND CAN i do the same to turn a string into a php array?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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!
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Post by Shendemiar »

The Ninja Space Goat wrote:...without the need for storing serialized array strings which is BAD BAD BAD!
Whats bad about that?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Extremely reduced pool size and increased work to search and extract information are a few caveats to storeing into a single record.
Post Reply