Multilingual support in PHP website

NOTE: This post is written for developers having some knowledge of PHP/MySQL. So, if you find it difficult to understand some part, plz leave your comments. - Nilesh

So many developers thinks that its hard to provide multilingual support to website. Are you one of them? No problem. Here are some helpful tips to provide basic multilingual support for you website.

There are 3 different cases

CASE#1 Provide multilingual to static contents of site.

For e.g. if you have header navigation, left navigation in website OR if you have predefined messages e.g. greeting messages, validation error messages etc then you need to do following things.

Create one configuration file per language.
E.g. if your site has English and Hindi support, then create 2 language configuration files.
(1) en.lang.inc.php
(2) hi.lang.inc.php

Both file will content same data in following format.
(1) en.lang.inc.php

define(‘WELCOME’, ‘Welcome’);
define(‘SIGN_IN’, ‘Sign In’);

NOTE: you need to find all places in your application to check how many strings you need with multilingual support and store in this file.

(2) hi.lang.inc.php

define(‘WELCOME’, ‘<place here hindi version of - Welcome>’);
define(‘SIGN_IN’, ‘<place here hindi version of - Sign In>’);

On user side, you need to provide selection to choose language.
English will be default language so lang=en

Your application request to change language will be xyz.com/file.php?lang=en for English, xyz.com/file.php?lang=hi for hindi.

Once user will make selection store that selection in COOKIE and use that value later so, you don’t have to pass “lang” parameter in each request.

Write code in your application’s common include file (this must be included in every request), to include proper configuration file based on selection.

So if user select English, only en.lang.inc.php will be included and if user selects Hindi, then only hi.lang.inc.php will be included.

Now you need to replace all your static contents with php constants in HTML/template files.

e.g. if your signin page contains.

<h1>Welcome</h1>

Then replace it with <h1><?php echo WELCOME ?></h1>

So, page will load language specific contents.

CASE#2 Provide multilingual to images.

E.g. in header you have used one image for text “about us” then for hindi version you need same image with hindi text.

You can use two directories in your images directory. Image name must be same in two directories.

Images/en/about_us.jpg
Images/hi/about_us.jpg

Then you need to change your code to load proper image based on language selection.

So, your image code

<img src=”images/about_us.jpg”>

will look like

<img src=”images/<?php echo $lang ?>/about_us.jpg”>

CASE#3 Provide multilingual to page contents.


e.g. if you have provision in admin panel to add content for “about us” page. You need to provide selection, for which language you are entering data. So, in database you will have two entries/versions for about us pages for two languages.

e.g. DB entries will be like below.
en | about_us | English contents
hi | about_us | Hindi contents

While displaying it to user, your query must use “language selected by user” to get proper contents from DB.

WYSWYG / TinyMCE editor has multilingual support for entering data. That you can use for entering data, else copy the data from any translator (e.g .google) and paste it into editor.

Database table for storing Unicode contents; must need to have UTF-8 setting >> charset: UTF-8 Unicode (utf8)

Also, you need to add following META into your HTML head.

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

That’s all!

A multi-user blog system in PHP - Bluetrait

Blog systems are now very popular and there are plenty of solutions to implement blogs.

"Bluetrait" allows the one or more users to login and post articles that are stored in a database. Currently it supports storing posts in MySQL and SQLite posts.

It supports post categories, comments, comment spam filtering, generating RSS feeds, trackback handling and plug-in extensions.

Download Here!

Ref: http://www.bluetrait.com

Export data to a CSV file using MySQL command prompt

SELECT tableColumnName1, tableColumnName2 
INTO OUTFILE '/path/to/file/data.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
FROM tableName;

Access Denied

Even if you have been granted the SELECT privilege, you will still need to be granted the FILE privilege, or else access will be denied. Some architects will only grant this privilege to the root user. Another reason that you might receive an access denied message, is because the directory where you are trying to save the CSV file is not writable.

In order to prevent a user from accidentally overwriting an important system file (like etc/passwd), you will be unable to save over a file that already exists on the server.

Edit MySQL stopword list [MySQL full text search]

There are two ways to edit MySQL stopword list. Actually its not editing. Its all about creating a new file with your own stopwords & point MySQL to refer to that file location.

1st option
----------

Create File: e.g. /etc/stopword.txt
* Change permission of this file, so that MySQL can read it.
* Don't put stopword file in /root, because mysql doesn't have permission to access it there.

Edit /etc/my.cnf file
Search for ft_stopword_file
Change Line ft_stopword_file=/etc/stopword.txt

Then run following command to restart MySQL.
service mysqld restart

Run MySQL command, for immediate effect of this action on any table for Full-Text search.
REPAIR TABLE table_name QUICK;

2nd option
----------
You'll need to edit the file myisam/ft_static.c. recompile MySQL, and rebuild the indexes!

See the default list of full-text stopwords @
MySQL default stopwords list

Optimize mysql queries

1. use the explain command
Use multiple-row INSERT statements to store many rows with one SQL statement.

The explain command can tell you which indexes are used with the specified query and many other pieces of useful information that can help you choose a better index or query.

Example of usage: explain select * from table

explanation of row output:
* table—The name of the table.
* type—The join type, of which there are several.
* possible_keys—This column indicates which indexes MySQL could use to find the rows in this table. If the result is NULL, no indexes would help with this query. You should then take a look at your table structure and see whether there are any indexes that you could create that would increase the performance of this query.
* key—The key actually used in this query, or NULL if no index was used.
* key_len—The length of the key used, if any.
* ref—Any columns used with the key to retrieve a result.
* rows—The number of rows MySQL must examine to execute the query.
* extra—Additional information regarding how MySQL will execute the query. There are several options, such as Using index (an index was used) and Where (a WHERE clause was used).

2. use less complex permissions

The more complex your permissions setup, the more overhead you have. Using simpler permissions when you issue GRANT statements enables MySQL to reduce permission-checking overhead when clients execute statements.

3. specific mysql functions can be tested using the built-in “benchmark” command

If your problem is with a specific MySQL expression or function, you can perform a timing test by invoking the BENCHMARK() function using the mysql client program. Its syntax is BENCHMARK(loop_count,expression). The return value is always zero, but mysql prints a line displaying approximately how long the statement took to execute

4. optimize where clauses
* Remove unnecessary parentheses
* COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM and MEMORY tables. This is also done for any NOT NULL expression when used with only one table.
* If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table

5. Run optimize table

This command defragments a table after you have deleted a lot of rows from it.

6. avoid variable-length column types when necessary

For MyISAM tables that change frequently, you should try to avoid all variable-length columns (VARCHAR, BLOB, and TEXT). The table uses dynamic row format if it includes even a single variable-length column.

7. insert delayed

Use insert delayed when you do not need to know when your data is written. This reduces the overall insertion impact because many rows can be written with a single disk write.

8. use statement priorities
* Use INSERT LOW_PRIORITY when you want to give SELECT statements higher priority than your inserts.
* Use SELECT HIGH_PRIORITY to get retrievals that jump the queue. That is, the SELECT is executed even if there is another client waiting.

9. use multiple-row inserts

Use multiple-row INSERT statements to store many rows with one SQL statement.

10. synchronize data-types

Columns with identical information in different tables should be declared to have identical data types so that joins based on the corresponding columns will be faster.

Reset MySQL incase of LOST of Password

In case of MySQL password LOST...

(1)service mysql stop
# wait until MySQL shuts down. Then run

(2)mysqld_safe --skip-grant-tables &
# then you will be able to login as root with no password.

(3)mysql -uroot mysql
# In MySQL command line prompt issue the following command:

(4)
UPDATE user SET password=PASSWORD("abcd") WHERE user="root";
FLUSH PRIVILEGES;
# At this time your root password is reset to "abcd" and MySQL will now
know the privileges and you'll be able to login with your new password:

(5)mysql -uroot -pabcd mysql

Mysql Replication

Follow the steps if you want to do Mysql Replication.

Example:-

MySQL Server A and Server B

(1) Database should be same on both servers.

(2) Create Slave User on each of the 2 servers:-
USE mysql;
INSERT INTO user(User, Passsword, Select_priv, Reload_priv, Super_priv, Repl_slave_priv) VALUES
('USR','PASSWD','Y','Y','Y','Y');
FLUSH PRIVILEGES;

(3) Configuring the MySQL servers:-
- Know IP address of each servers.
- Edit my.cnf or my.ini
Server A
========
server-id = 1
replicate-same-server-id = 0

# Configuration variables that prevent key collisions.
# auto-increment-increment = N [N: No of servers in the replication setup]
# auto-increment-offset = 1
# auto-increment-offset and server-id should be integer, same and consecutive.

auto-increment-increment = 2
auto-increment-offset = 1

master-host = <IP of Server B>
master-user = <slave user>
master-password = <slave password>
master-connect-retry = 60
replicate-do-db = <DB Name>

log-bin = C:\mysql\log\log-bin/log # Change this path as per your system
binlog-do-db = <DB Name>

Server B
========
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2

master-host = <IP of Server A>
master-user = <slave user>
master-password = <slave password>
master-connect-retry = 60
replicate-do-db = <DB Name>

log-bin = C:\mysql\log\log-bin/log # Change this path as per your system
binlog-do-db = <DB Name>

- Restart the 2 servers.

(4) Syncronizing the servers.

slave stop;
show master status; # Use result of Server A to Server B

CHANGE MASTER TO MASTER_HOST='<master's IP>',
MASTER_USER='<slave user>',
MASTER_PASSWORD='<slave password>',
MASTER_LOG_FILE='<master's log file name>',
MASTER_LOG_POS=<master's log file position>;

start slave;
show slave status;

(5) Reseting Replication
- Shutdown both servers
- delete relay logs
- follow steps (1) to (4) again

(6) Testing
- Primary keys generated on Server A should be odd numbers
- Primary keys generated on Server B should be even numbers