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

User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

I shouldn't say that you shouldn't EVER store a serialized array in your database. There are cases in which this is ok, but this isn't one of them. He has a clear case of many-to-many, in which he should use a many-to-many schema
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Post by Shendemiar »

The Ninja Space Goat wrote:I shouldn't say that you shouldn't EVER store a serialized array in your database. There are cases in which this is ok, but this isn't one of them. He has a clear case of many-to-many, in which he should use a many-to-many schema
Good, i was frightened that there was something horrible!!! (Since i sometimes store sequenced strings... and xml... and ini files... and pictures... i guess im horrible) :D
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

feyd wrote:Extremely reduced pool size and increased work to search and extract information are a few caveats to storeing into a single record.
Yep, don't ever get off on this footing. Do it the way database were made to be used - relationally. Could you imagine searching that data the way you suggest? It would be very slow and on-top of that you'd need to pull the entire set of data back into memory, which means on large datasets you may run out of memory!
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Post by Shendemiar »

d11wtq wrote:Yep, don't ever get off on this footing. Do it the way database were made to be used - relationally. Could you imagine searching that data the way you suggest? It would be very slow and on-top of that you'd need to pull the entire set of data back into memory, which means on large datasets you may run out of memory!
Im in belief that parsing stuff in memory is faster than huge amount of databasehits. What kind of data-amounts would lead to "out of memory"? Cant the server que multiple simultaneous request?

Complex database structure isnt very versatile, more like "per-task-set", storing structured data allows more versatility.
danharibo
Forum Commoner
Posts: 76
Joined: Thu Aug 17, 2006 8:56 am

Post by danharibo »

UH oh, now my query:
SELECT proj_users.name, awards.name
FROM devdb
LEFT JOIN awards
ON proj_users.user_id=awards.award_id
Doesn't return anything ?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Have you requested your database's error function? MySQL has mysql_error(), for instance.
danharibo
Forum Commoner
Posts: 76
Joined: Thu Aug 17, 2006 8:56 am

Post by danharibo »

no database selected ? thats odd
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

feyd wrote:Extremely reduced pool size and increased work to search and extract information are a few caveats to storeing into a single record.
What do you mean by "Extremely reduced pool size", dependant on the scenario; for instance connection pooling is a good thing along with buffer pooling being reduced *most of the time*.. now I cant think of any other "pooling".. so I'm intreged as to what you are talking about...
Last edited by ody on Mon Aug 21, 2006 5:22 pm, edited 1 time in total.
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

d11wtq wrote:Do it the way database were made to be used - relationally.
Not all databases are based on set theory, and not all code needs the set theory model.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Pool, as in space available in a column of any type. A finite amount of information can be stored in each of the column types. The amount of values you can keep in a single column is limited further in how you choose to encode those values. For instance, if stored as straight integers with some delimiting character to separate them the larger the integers you accumulate the less quantity of integers you could possibly store.

As a practical example, say there are 10,000 awards. The quantity of four digit awards that can be held is far less than the quantity of two or three digit awards. Whereas by storing each value as their own record, there is no such limitation.

Taking this a step further, imagine that you want to find users that have one (or more) of the six awards and display which of those they have. Using the single column will require writing a somewhat clever query to immediately know which of the six they own, while using the separated records makes retrieval a simple loop.
danharibo
Forum Commoner
Posts: 76
Joined: Thu Aug 17, 2006 8:56 am

Post by danharibo »

Any body? i still have that problem
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

danharibo wrote:Any body? i still have that problem
no database selected? Are you using mysql? If so, use mysql_select_db() function
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Post by Shendemiar »

danharibo wrote:Any body? i still have that problem
Sorry that we ruined your thread :D
danharibo
Forum Commoner
Posts: 76
Joined: Thu Aug 17, 2006 8:56 am

Post by danharibo »

It is selecting the Db, here is my code:

Code: Select all

<?php
$link = mysql_connect('localhost', 'root', '*****');
if (!$link) {
   die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_select_db("devdb");
$query = "SELECT proj_users.name, awards.name
FROM proj_users
LEFT JOIN awards
ON proj_users.user_id=awards.award_id;";
$result = mysql_query($query) or die(mysql_error());

//mysql_close($link);
// store the record of the "example" table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry 
echo "Name: ".$row['name'];
?>
Maybe you can see what is wrong ?
danharibo
Forum Commoner
Posts: 76
Joined: Thu Aug 17, 2006 8:56 am

Post by danharibo »

Antbody????
Post Reply