Tuesday, 14 November 2017

PHP & MySQL: Creating a website in your local language smoothly

PHP & MySQL: Creating a website in your local language smoothly


Hello Developer,
You know English is the international language and accepted as international communication. So, most of the websites have been developed in English. But in many other times, a developer needs to work with local languages where they are developing a website in PHP. If you develop in local language, you need to know a small trick and that will show your content properly in all browsers smoothly.
So, what is the technique?
Well. Let me explain step by step.
Step One: An observation
I will show you the source code of two Bengali newspaper. In this newspaper: http://www.prothom-alo.com, the fonts and text comes properly in all browsers. But in this newspaper: http://www.ittefaq.com, it can show text and fonts properly only in Internet Explorer.
Now the matter is we must make sure our content will be displayed properly in any browser. Right?
OK. So what is the difference between the two websites I just gave example?
If you take a look at the source code of the first website’s content, you will find it is like this:সઃপাদক
This is another website that had been developed as smooth site in the same technique: http://www.bdnews24.com/bangla. If you check its code, you will see that it uses same type of code for its text.
So, what are these পাদকীয় things? It is very interesting that these are universal representation of local language in HTML entities. For every character of any local language, there is a unique and fixed symbol defined such as প in HTML entities. When you bring this kind of text in your browser source code, the site content looks smooth without any break and fonts displays properly.
Now if you open the source code of http://www.ittefaq.com, you will see something like AvR beg RvZxq msm` wbe©vP‡b jovB n‡e †RvU-gnv‡Rv‡Ui g‡a¨. The matter is they are also showing Bengali news content, but in a different way that is not useful in cross-broswer platform. This is often totally recognized by only Internet Explorer and often partially recognized by other browsers.
Step Two: Storing your local language content in the database
You see I have some content in my local language (Bangla) in the database.
Step Three: Converting your local language text in Universal code
Well. This is extremely easy.
The displayLocalContent() function is going to display the news and convertToLocalHtml() function converts my utf-8 content to HTML entities.
And here in index.php I am executing my displayLocalContent() function.
As a result, the output is now as à¦¢াকা, ডিসে etc.
Step Four: You need to check my other posting for SELECT, INSERT & UPDATE your local language.
Here goes my other article so that you can perform all required operations for storing and displaying information in browser smoothly:
http://www.tanzilo.com/2008/10/13/php-mysql-unicode-solution-to-chinese-russian-or-any-language/
An alternative way
I have an alternative way in my mind and that is when you store the data in the database, you can first convert them to universal code such as কীয়and then directly show your text.

Thursday, 26 October 2017

How to select distinct rows without using group by statement

A B C
1 1 1
1 1 1
2 2 2
2 2 2
3 3 3
3 3 3
4 4 4
4 4 4
5 5 5
5 5 5
5 5 5
6 6 6
6 6 6
I am to output only the distinct rows without using the group by statement. I cannot use group by because it makes mysql hang. So it should return

1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6

Solutions:
If the name of your table is mytable , select distinct * from mytable will do the trick.

MySQL uses GROUP BY under the hood to execute DISTINCT !!!
If the table is called mytable, do these two things:

First run this

ALTER TABLE mytable ADD INDEX ABC (A,B,C);`
Second, run this query

SELECT A,B,C FROM mytable GROUP BY A,B,C;
GROUP BY actually works better with an index present !!!

Here is sample code to prove it works

mysql> drop database if exists cool_cs;
Query OK, 1 row affected (0.04 sec)

mysql> create database cool_cs;
Query OK, 1 row affected (0.00 sec)

mysql> use cool_cs
Database changed
mysql> create table mytable
    -> (A int,B int,C int, key ABC (A,B,C));
Query OK, 0 rows affected (0.08 sec)

mysql> INSERt INTO mytable VALUES
    -> (1,1,1),(1,1,1),(2,2,2),(2,2,2),(3,3,3),
    -> (3,3,3),(4,4,4),(4,4,4),(5,5,5),(5,5,5),
    -> (5,5,5),(6,6,6),(6,6,6);
Query OK, 13 rows affected (0.06 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> select a,b,c FROM mytable group by a,b,c;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    2 |    2 |
|    3 |    3 |    3 |
|    4 |    4 |    4 |
|    5 |    5 |    5 |
|    6 |    6 |    6 |
+------+------+------+
6 rows in set (0.02 sec)

mysql>

Tuesday, 5 September 2017

Function: Convert Days Into Seconds w/ PHP

## DAYS INTO SECONDS
<?php
function days_to_seconds($days) {
if(is_numeric($days)) {
return 86400*$days;
} else {
return ‘string must be numeric’;
}
}
## PARSE
echo days_to_seconds(8); // NUMBER OF DAYS
## RESULT
// 691200
?>

Wednesday, 30 August 2017

MySQL column: Change existing phone numbers into specific format?

I have a MySQL column that contains phone numbers, the problem is that they're in different formats, such as:
  • 2125551212
  • 212-555-1212
  • (212)5551212
I'd like to know if it's possible to take the existing 10 digits, remove the formatting, and change them all to this format: (212) 555-1212
Not a duplicate, as I'm looking to update several thousand entries instead of masking new entries.

Solution:
Sample data

create table nums ( num text );

insert into nums values 
    ('2125551212'),
    ('212-555-1212'),
    ('(212)5551212');
Query formatting your data

select 
  num, 
  concat('(',substr(num_cleansed,1,3),') ',substr(num_cleansed,4,3),'-',substr(num_cleansed,7)) AS num_formatted
from (
  select 
    num, 
    replace(replace(replace(num,'(',''),')',''),'-','') as num_cleansed
  from nums
  ) foo

Friday, 30 June 2017

“INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”

In case you want to see what this all means, here is a blow-by-blow of everything:

CREATE TABLE `users_partners` (
  `uid` int(11) NOT NULL DEFAULT '0',
  `pid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`,`pid`),
  KEY `partner_user` (`pid`,`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
Primary key is based on both columns of this quick reference table. A Primary key requires unique values.

Let's begin:

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...1 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1);
...0 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1) ON DUPLICATE KEY UPDATE uid=uid
...0 row(s) affected
note, the above saved too much extra work by setting the column equal to itself, no update actually needed

REPLACE INTO users_partners (uid,pid) VALUES (1,1)
...2 row(s) affected
and now some multiple row tests:

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...3 row(s) affected
no other messages were generated in console, and it now has those 4 values in the table data. I deleted everything except (1,1) so I could test from the same playing field

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4) ON DUPLICATE KEY UPDATE uid=uid
...3 row(s) affected

REPLACE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...5 row(s) affected
So there you have it. Since this was all performed on a fresh table with nearly no data and not in production, the times for execution were microscopic and irrelevant. Anyone with real-world data would be more than welcome to contribute it.

Subscribe to: Posts (Atom)