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`)
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.