MySQL Table Schema Assistance

Hi,

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.

thanks
Karen

What is the maximum value of an unsigned tinyint?

https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

Oh I see! Thanks :slight_smile:

1 Like

those word combinations shouldn’t be hardcoded as columns. they should be column values

CREATE TABLE language_translate 
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, lang_id tinyint(3) unsigned NOT NULL
, words text COLLATE utf8_unicode_ci DEFAULT NULL
, language_words text COLLATE utf8_unicode_ci DEFAULT NULL
)
;
INSERT
  INTO language_translate
VALUES
 ( 1 , 22 , 'About us'      , 'À propos de nous' )
,( 2 , 22 , 'Access denied' , 'Accès refusé'  )
, ...   
 ( 11 , 35 , 'About us'      , 'Über uns' )
,( 12 , 35 , 'Access denied' , 'Zugriff abgelehnt'  )
, ...
1 Like

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.

thanks
Karen

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.

thanks
Karen

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:

while($row = mysqli_fetch_array($res)) {
  $arr[$row['words']] = $row['language_words'];
}

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’]

Or how is it generally done?

Thanks

in over 30 years of writing SQL, i have never looped

Based on the schema that r937 provided, i would add a row to my table that says:

id (not important, set to whatever number it is by the database)
lang_id = 1 (assuming English is 1)
words = “hi_world”
language_words = “hi world”

Loop through my results as mentioned in post #7,
and then
echo $arr["hi_world"] will output “hi world”

in the revised table i gave you, TEXT works but has issues – VARCHAR would be much better

That’s awesome! Thank you all :slight_smile:

2 posts were split to a new topic: How to get Database results as object instead of array?