Insert array into database in a single row

Question

I wonder if this would be doable ? To insert an array into one field in the database.

For instance I have a title, I want to have that title with only one id, but it's going to be bilingually used on the website.

It feels a bit unnecessary to make another table to have their global ids and then another table with the actual titles linked to the table with the global id.

I just want to have something like this

ID      TITLE
1       Array("english title", "nederlandse titel");

I'm using PHP/MSYQL, so if it would be doable could you please explain in these languages.

Oh yeah I figured that I could format it funky and use the split function to turn it into an array again. But I wonder if I could just store it as an array right away, I case the user might type something with the same formatting (one out of a million)

Solution 1

it's doable:

$title = serialize($array);

and then to decode:

$title = unserialize($mysql_data);

but as mentioned it really lessens the benefits of a database in the first place. i'd definitely suggest looking into a multi-table or multi-column option instead, depending on the amount of languages you want to support and if that number will change in the future.

edit: a good point mentioned by dcousineau (see comments)

Sometimes the serialized output, even after escaping, throws characters into the query that screws things up. You may want to wrap your serialize() in base64_encode() calls and then use base64_decode() before you unserialize.

adjusted code for those situations:

$title = base64_encode(serialize($array) );
$title = unserialize(base64_decode($mysql_data) );

Solution 2

There's really only two reasonable choices here:

Join with another table
pros: unlimited titles in unlimited languages
cons: join overhead is more computationally expensive, SQL is marginally more complex to update/insert etc

Multiple columns
eg: TITLE_EN, TITLE_NL, TITLE_DE
pros: fast to insert, select, etc
cons: limited number of languages, adding more is an ALTER TABLE

Given our two choices, you usually should should pick the first one. Unless you're dealing with just an obscene amount of transactions that cannot be parallelized, or you absosmurfly can ensure that you will never add languages, the extra flexibility in schema layout will save you headaches in the long run.

Solution 3

Arrays do violate normalization; in my experience with internationalization databases I've found that having a the phrases normalized is the best design,

I allows you to easily make wholesale copies of rows - for instance 'es' to 'es-mx' or 'en' to 'en-US', 'en-GB', and my favorite: 'xx-piglatin'. In an array schema, you would either have to re-write every record or add complex parsing or use something more complex than arrays, like XML.

It is relatively easy to use LEFT JOINs for find untranslated phrases for work and also to use COALESCE to return a default so the program remains usable even if the phrase is not translated.

Solution 4

Use a table with 3 columns !

ID, TITLE_EN, TITLE_NL

There is no good reason to serialize that, REALLY !

View more solutions

Comments

[Robert Gould]

As a side note, Owen's solution is also useful if you need to store scripts in a database.

[bmdhacks]

I'm having trouble expressing how dangerous this approach is. If you ever decide that you need to query this data (which you do, in databases), you're screwed. And it's not a simple alter table that will fix it. I speak from experience with this, you're going to get burned with this approach.

[Owen]

i don't think anyone would disagree with you. but if the question is "how do i store an array" the answer is "serialize". me and others have offered reasons and alternatives to not use this solution, but ultimately it's up to the OP to choose what works for him.

[penetra]

Serializing into a db is great when you want say log something so you can look at it later and maybe only want to search for a string (without ordering of course). And that searching is only done by you, not by a user.

[dcousineau]

A caveat: Sometimes the serialized output, even after escaping, throws characters into the query that screws things up. You may want to wrap your serialize() in base64_encode() calls and then use base64_decode() before you unserialize.