I am using a table for my website translations as part of my larger DB schema. For choosing between which language to display on the webiste.
The table in particular is : language as follows:
CREATE TABLE `language` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`lang_id` tinyint(3) unsigned NOT NULL,
`about_us` text COLLATE utf8_unicode_ci DEFAULT NULL,
`access_denied` text COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=xyz
With 192 rows of words (starting with about_us, access_denied)… etc … → 192 values finishing up with primary key. You see here I am using text, but I could also use VarChar or some other suggested value as the words tend to be short (ambye less than 100 characters).
I then choose the language ID, and add the appropriate transalction with the associated language ID and put thte resultant value into an arry that I can access throughout the website.
This worked when I had 192 word combinations — however, I also have a series of categories, and sub categories etc that result in 970 word combinations.
My DB says when I have more than about 195 columns (as above) – it generates an error. I cannot create the table.
I am not sure how to structure the language table to get access to all the word combinations. I think about 1,200 when I add all of the different categories and subcategories.
Any suggestions on how to structure a website so that I can create an array of words that I can then access throughout the site?
My problem here is the database strucutre - I can access data and put it into an array fine.
Ok, but now I am unclear how to utilize in script. If I have a value, say ‘about us’ do I then search on the database for that value and language for every word combo?
I had previously put it into an array at language select (1x) and then queried throughout the script.
Like this :
select * from language_table where lang_id=2;
echo $arr[‘about_us’] prints to screen abouteee usssee (fr etc)
How would you extract the data and print to screen with php then? Are you looping through your array then at runtime to find a word match? That does not seem as efficient to me - but perhaps I am not understanding.
ps - for clarification, this was the error when I had created the table using the word as the column key Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some column—
I can change to text, but that also does not really solve my problem for very long.
As the sytem still falls over at arelatively small row size.
This would remain as it is (EDIT: Though i wouldnt SELECT *. It’s wasteful.) ; it’s what you get BACK that would be different:
Instead of 1 row with many, many columns, it would return many rows, with a single column value.
In technical terms, the design has been pivoted.
Instead of saying $arr = mysqli_fetch_array($res)
(I’m guessing, as you havent shown me this line in your code.)
You would instead walk across all the rows in the result:
Thank you yes - but how are you matching it to the English?
Let’s say your code is <p>hi world<\p>
Before I would echo $ array[‘words’]->hi_world
Now I am not sure.
Would you insert the English as hi_world, loop through the array and set the English as the key and then the foreign language as the value and then print to screen line this $arr[‘words’][‘hi_world’]
