List:General Discussion« Previous MessageNext Message »
From:Prabhat Kumar Date:May 16 2010 6:04pm
Subject:Re: Array data type
View as plain text  
Hi,

> How to store multiple values in a single field? Is there any array data
> type
> concept in mysql?
>
As Jörg said "Multiple values in a single field" would be an explicit
violation of
the relational model..."

then also, if you want to use.
this might be this will help you.
I used like this in past:

In database, I have taken a column as TEXT. In which I have separated a
value by *- *(hifen)

eg. furniture table there is 2 col , name (person name) - used (furniture's
used by that person).

value will be :

> ====================
> | Name    |  Used             |
> --------------------------------------
> | Prabhat | chair-table-bed |
> ===================
>
And in PHP you can easily separate these value.

eg,

>       $names = "Markus;Nigel;David";
>
To use these names in a meaningful way, we should first separate them into
an array ($namearray), using
explode()<http://au3.php.net/manual/en/function.explode.php>
:
$namearray = explode(";", $names);

The end result:
$namearray = Array ( [0] => Markus [1] => Nigel [2] => David )


But remember this is VERY bad database design. I had used since, that was
required for few days only.

Thanks,


In database :
On Sun, May 16, 2010 at 9:34 PM, Mark Goodge <mark@stripped> wrote:

> On 14/05/2010 09:54, Joerg Bruehe wrote:
>
>> Hi Samrat, all!
>>
>>
>> Samrat Kar wrote:
>>
>>> Hello,
>>>
>>>
>>>
>>> How to store multiple values in a single field? Is there any array data
>>> type
>>> concept in mysql?
>>>
>>
>> "Multiple values in a single field" would be an explicit violation of
>> the relational model (on which the SQL language is based) and cause all
>> kinds of trouble in your queries.
>>
>> Ever and again, developers use some kind of encoding to store a
>> combination of values (like flags in a bit field) in one database field,
>> but in many cases this makes queries very hard to write, and may prevent
>> optimization of the SQL statement.
>>
>> It depends on your application, especially on whether this field will be
>> used in search conditions ("... WHERE combined_field has flag_X ..."),
>> to decide about a sensible approach.
>> In general, I would prefer separate fields for different flags, and a
>> separate table for a truly multi-valued field (like multiple postal or
>> mail addresses for a person).
>>
>
> If you're merely *storing* the data in the table, and will only ever
> retrieve it based on other factors - that is, you'll never use that field
> for any operands including joins and 'where' clauses - then it's often
> useful to store a flattened array (eg, one created by PHP's serialize()
> function, javascript JSON or even XML) as a string and then expand it to an
> array again after retrieving it. That can often be a useful way of storing
> meta-data about a data object (eg, EXIF data from a photograph), especially
> where you can't know in advance what the array structure will be when you
> create the database.
>
> However, that's not really an array datatype in MySQL, it's simply a method
> of storing an array as a string. So it's of fairly limited application,
> there are cases where it's very useful but it's not a substitute for storing
> the array values separately using the appropriate table design where you do
> need to run queries against it.
>
> Mark
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>


-- 
Best Regards,

Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile     : 91-9987681929
www.datavail.com

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

Thread
Array data typeSamrat Kar14 May
  • Re: Array data typeCarsten Pedersen14 May
  • Re: Array data typeJoerg Bruehe14 May
    • Re: Array data typeMark Goodge16 May
      • Re: Array data typePrabhat Kumar16 May