Archive | MySQL RSS feed for this section

User’s Demographic Data from Facebook

By on May 14, 2010 in Facebook, MySQL

facebookSometimes you may need to collect facebook user’s basic data for your website or application. Then you’ve to call graph api or legacy api or fql query to collect some specific data. This is a basic requirements for most of the fbconnect base website or facebook application.

For this reason, here I’m sharing the code, how could I collect those basic data and store them in database.

Before proceeding check the demo.

If you’re automatically logged in the site, then first logout and relogin and approve all the permissions. Now I’m showing the database table schema first

Continue Reading →

Comments { 55 }

Using MySQL Stored Procedure to create sample data

By on April 26, 2010 in MySQL

mysqlMySQL stored procedures are programs that are stored and can be executed on the MySQL server. You can call stored procedure from any application over a distributed network. Stored procedures provide a means of interacting in a prescribed way with the database without placing any additional traffic on the network. Here I’m describing a stored procedure that I used to create some sample data.

To learn more about stored procedure checkout this link.

Suppose you may need to create a large number of dataset for a table and your table structure looks like this

CREATE TABLE IF NOT EXISTS `dictionary` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `word` varchar(100) NOT NULL,
  `mean` varchar(300) NOT NULL,
  PRIMARY KEY (`id`)
);

Now you’ve to add 110000 dummy data to this table. You can dump some dummy data to the table by the following way.

Continue Reading →

Comments { 2 }

MySQL random data selection

By on April 26, 2010 in MySQL, PHP

mysqlSome days ago I was working in a vocabulary game and dictionary. The dictionary contains 1,10,000 words and meanings. I developed a vocabulary game where I had to randomly choose 10 words out of 1,10,000 dataset. Here I’m describing the possible solutions for this problem and which solution I used.

Data table

Table name is dictionary and it has id, word and meaning fields. id contains auto incremented id and it is unbroken sequence 1,2,3…..n.

id word meaning
1 aback Having the wind against the forward side of the sails
2 abandon Forsake, leave behind
… ….. ….

Continue Reading →

Comments { 15 }

Getting rank today, this week and this month

By on February 28, 2010 in MySQL

mysqlIn 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 Reading →

Comments { 1 }

How to get rank using mysql query

By on February 28, 2010 in MySQL, PHP

mysql 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 Reading →

Comments { 57 }

Encrypt MySQL data using AES techniques

By on January 12, 2010 in MySQL

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 Reading →

Comments { 18 }

never forget when setting up a mysql user account

By on September 7, 2009 in MySQL

mysql_tips 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 Reading →

Comments { 0 }

designing & implementing scalable applications with memcached and mysql

By on February 22, 2009 in MySQL

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

memcache_22-02-2009

Comments { 2 }

MySQL – NULL vs ” vs ‘NULL’

By on January 10, 2009 in MySQL

mysql_tipsToday, 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.

Comments { 10 }

MySQL Workbench – Next-generation visual database design application

By on November 25, 2008 in MySQL

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 Reading →

Comments { 1 }