Splitting MySQL content via PHP on a per country / user basis
Ok, I'll just explain this:
I have one database that has content, but I need to set up a condition if the user has selected the UK instead of the US, or vice versa - some content will not show. This condition can be applied using a checkbox in the backend for example.
"Hello, I'm a paragraph" in [x] Britian [x] US
I am not looking at actual IPs or anything, as the site will simply redirect to root / uk or root / us subdirectories when a user is selected on the index page. What unique setting would I include in db or php?
Thank!
source to share
This is an interesting problem. Because although it is language oriented, it is not related to I18N - you are talking about denying access to content based on language preference, not displaying localized content.
Here's one idea: use a permissions model
- Create two permissions like "read-enUS-content" and "read-enGB-content"
- Based on the user's choice, give them the correct permission for the length of their session (which you can save via cookies or custom prefs if you want).
- In this case, the application will have to allow / deny content based on their rights.
The question then becomes, how do you connect content to permissions? There are many ways, but one approach is bitmax column.
00
||
|+--- Read enUS bit
+---- Read enGB bit
What will look like when implemented
Articles
+----+---------+-----------------+
| id | content | read_perm_level |
+----+---------+-----------------+
| 1 | foo | 1 |
| 2 | bar | 2 |
| 3 | baz | 3 |
+----+---------+-----------------+
This means that "foo" is read-only enus, "bar" is read-only enGB, and "baz" is available to everyone.
But you can also make a complete permission table and connect content to it this way.
source to share
The simplest model for this would be an associative table in your database. Thus, you must:
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 you save your paragraph settings, do the following:
-
BEGIN WORK
(to start a transaction) -
DELETE FROM paragraph_countries WHERE paragraph_id = ...
- (for verified country):
INSERT INTO paragraph_countries (...)
-
COMMIT
(to commit the transaction)
To select paragraphs related to the current country, simply go JOIN
to the appropriate table with the appropriate country code.
source to share