In my previous article I’ve shown how to get rank using mysql query. Now I’m showing how to retrieve result based on today, week and month using mysql query. Actually I also implemented this in my quiz project so I’m sharing this with you.

For the table structure please look my previous article http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query/

Task 1: I’ve to retrieve those users rank who played the game today.
Solution: Look at the query

SELECT uid, participated, correct, wrong from quiz_user
    WHERE DAYOFMONTH(CURDATE())=extract(day from updated)
    ORDER BY correct DESC, participated ASC
    limit 30

Continue

Some days ago I was working in a quiz project. Where user will play quiz and for each correct answer they will earn points. One of the task of this quiz application was, to get rank of a particular user. Now I am showing how could I solve this problem using mysql query.

Here is the table structure:

CREATE  TABLE IF NOT EXISTS `quiz_user` (
`uid` BIGINT UNSIGNED NOT NULL ,
`participated` SMALLINT UNSIGNED NULL DEFAULT 0 ,
`correct` SMALLINT UNSIGNED NULL DEFAULT 0 ,
`wrong` SMALLINT UNSIGNED NULL DEFAULT 0 ,
`created` DATETIME NULL ,
`updated` DATETIME NULL ,
PRIMARY KEY (`uid`) )
ENGINE = InnoDB

Continue

Sometimes clients want that the information they collected from the user should be encrypted and stored in database. Data encryption and decryption is a common technique for secured data. In this article I’ll show how could you use mysql’s built in function to encrypt and decrypt data.

Suppose you’ve a table where you want to encrypt user’s name & address. So look below the structure of the table. In this table we will store name and address as encrypted.

CREATE  TABLE `user` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`first_name` VARBINARY(100) NULL ,
`address` VARBINARY(200) NOT NULL ,
PRIMARY KEY (`id`)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

You may be expected the table structure should be:

CREATE  TABLE `user` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`first_name` VARCHAR(50) NULL ,
`address` VARCHAR(100) NOT NULL ,
PRIMARY KEY (`id`)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

Continue

Someday ago i was working 2 facebook projects. When I was working on the 2nd facebook project, I just copied the first project architecture. Then I setup the mysql user account for the second project. As the two projects are similar, I thought to remain the username for mysql same for the 2 projects, but when I did that I created a great mistake that I’m describing here.

When I setup my 1st project’s database I use Continue

a nice article to learn about scalable applications with memcached and mysql. a 15 page article but worth to read. download and read

Today, in one of my project I’ve to check empty fields.

The field name is: answer it’s type is TEXT and Default value is NULL

In my SQL query I was checked at first answer != NULL (if the answer is not null, i.e if the answer is not empty), But this was not showing the correct result.

Then I changed it to answer != ” ( i.e ” means empty string) then it showed the correct result.
Then I test with this answer != ‘NULL’, and it also showed the correct result.

Visual database design is very essential. Almost every web application needs database. And by designing visual representation, relation of tables makes an developer easy to understand on the database. There are many tools to design database like DBDesigner, SQLYog. Recently I found another application that is called MySQL Workbench. It can be used to efficiently design, manage and document database schemata. After using it’s functionality, I found this is a really good tool for design database specially for MySQL.

Continue

GROUP_CONCAT() function is used to concatenate column values into a single string. It is very useful if you would otherwise perform a lookup of many row and then concatenate them on the client end.

For example if you query:

mysql> SELECT Language FROM CountryLanguage WHERE CountryCode = 'THA';

It outputs:

To concatenate the values into a single string, you query:

mysql> SELECT GROUP_CONCAT(Language) As Languages FROM CountryLanguage WHERE CountryCode = 'THA';

Then the output will be:

You can also use some format of GROUP_CONCAT(). Like

• SELECT GROUP_CONCAT( Language SEPARATOR ‘-’ )… It will use ‘-’ instead of ‘,’
• SELECT GROUP_CONCAT( Language ORDER BY Language DESC )… To change the order and shorting output

One thing to remember: GROUP_CONCAT() ignores NULL values.

When we use a relational database system, we should know some important things. Numeric data type is very essential for a table. Here I describe some important points of Numeric Data type in MySQL:

MySQL has 9 numeric data types

• Integer:            TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
• Floating Point:  FLOAT, DOUBLE
• Fixed Point:      DECIMAL
• Other:              BIT, (ENUM maybe)‫‏‬

Some poor design:

• INT(1)‫‏‬
• BIGINT AUTO_INCREMENT
• no UNSIGNED used
• DECIMAL(31,0)‫‏‬

INT(1) – 1 does not mean 1 digit.   (1) represents client output display format only. INT is 4 Bytes. TINYINT is 1 Byte . TINYINT UNSIGNED can store from 0 – 255. BIT is even better when values are 0 – 1

BIGINT is not needed for AUTO_INCREMENT

• INT UNSIGNED stores 4.3 billion values
• You should be partitioning when at billions of rows
• BIGINT is applicable for some columns e.g. summation of values

Best Practice

• All integer columns UNSIGNED unless there is a reason otherwise.
• Adds a level of data integrity for negative values