Menu
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)
815
You have 2 ways of doing it: You can create a table (or multiple tables linked together) with a field for each key of your array, and insert into each field the corresponding value of your array. This is the most common way. You can just have a table with one field and put in here your array serialized.
We can insert array elements in an array by mentioning them within curly braces {} with each element separated by commas.
$data = array("one", "two", "tree"); // output one, two, three $insert_data = implode(",", $data); or $insert_data = json_encode($data); Thats for inserting data in single column. While retrieving you can do explode() or json_decode() to get the return data and can use them in the multi-select again.
MySQL doesn't have an array data type. This is a fundamental problem in architectures where storing denormalized rows is a requirement, for example, where MySQL is (also) used for data warehousing.
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) );
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.
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
