Separating MySQL content via PHP on a "per-country/user preference" basis

Question

Okay, I'll lay this out simply:

I have one database that has content in it but I need to set up a conditon where if the user has selected Britain instead of the U.S. or vice versa -- some content will not show. This condition can be applied via a checkbox in the backend like so.

"Hello, I'm a paragraph" show in [x] Britian [x] U.S.

I'm not looking into actual IP Addresses or anything of that sort as the site will simply redirect to root/uk or root/us subfolders upon the user's selection on the index page. What kind of a unique parameter would I have incorporate in the db or php?

Thank You!

Answer 1

Add a column in the database display_content, make it a EMUN and set the values to 'britain','usa','all' then make the adjustments in your code to check for Britain/USA. you can either choose to display all, Britain or USA.

Answer 2

That's an interesting problem. Because although it's language oriented, it's not actually I18N related - you're talking about deny access to content based on a language preference, not displaying localized content.

Here's one idea: use a permission model

• Create two permissions such as "read-enUS-content" and "read-enGB-content"
• Based on the user's selection, grant them the correct permission for the length of their session (which you can persist if you wish via cookies or user prefs)
• It would then be the responsibility of the application to allow/deny the content based on their permissions.

Then the question becomes, how do you connect content to permissions? There are a multitude of ways, but one approach is a bitmask column.

00
||
|+--- Read enUS bit
+---- Read enGB bit

Which might look like this when implemented

Articles
+----+---------+-----------------+
| id | content | read_perm_level |
+----+---------+-----------------+
|  1 | foo     |               1 |
|  2 | bar     |               2 |
|  3 | baz     |               3 |
+----+---------+-----------------+

This means that 'foo' is only readable by enUS, 'bar' is only readable by enGB, and 'baz' is readable by all.

But you could also do a full permission's table and connect the content to it that way.

Answer 3

The most straightforward model for this would be an associative table in your database. You'd thus have:

paragraphs (paragraph_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, paragraph_text TEXT, ...) ENGINE=InnoDB;
paragraph_countries (paragraph_id INT UNSIGNED, country_code CHAR(2)) ENGINE=Innodb;

When saving the paragraph settings, perform these steps:

1. BEGIN WORK (to start a transaction)
2. DELETE FROM paragraph_countries WHERE paragraph_id = ...
3. (foreach checked country): INSERT INTO paragraph_countries (...)
4. COMMIT (to commit the transaction)

To select paragraphs relevant to the current country, simply JOIN to the associated table with the appropriate country code.