Howto get multiple values from 1 row
Moderator: General Moderators
-
Shendemiar
- Forum Contributor
- Posts: 404
- Joined: Thu Jan 08, 2004 8:28 am
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)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
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
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!feyd wrote:Extremely reduced pool size and increased work to search and extract information are a few caveats to storeing into a single record.
-
Shendemiar
- Forum Contributor
- Posts: 404
- Joined: Thu Jan 08, 2004 8:28 am
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?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!
Complex database structure isnt very versatile, more like "per-task-set", storing structured data allows more versatility.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Have you requested your database's error function? MySQL has mysql_error(), for instance.
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...feyd wrote:Extremely reduced pool size and increased work to search and extract information are a few caveats to storeing into a single record.
Last edited by ody on Mon Aug 21, 2006 5:22 pm, edited 1 time in total.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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.
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.
-
Shendemiar
- Forum Contributor
- Posts: 404
- Joined: Thu Jan 08, 2004 8:28 am
It is selecting the Db, here is my code:
Maybe you can see what is wrong ?
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'];
?>