27 April 2013

more SQL tutorial



Let us build a database  'test' with table  'person' inside it, having column  name, age, gender. see how to do this


Insert a line into table:
INSERT INTO person (name, gender) VALUES ('omphu', 'female')
This will add a new line or data into the table  'person' on column  name, gender with value omphu, female respectively and will leave the value of age blank.

Select line using WHERE:
SELECT * FROM person WHERE gender='female'

Select specific column:
SELECT name FROM person WHERE gender='male'
This will output the value of column name where gender is female.

Specify more argument to refine search:
SELECT name, age FROM person WHERE age='21' AND gender='male'
Will output result if this two condition are true,  age & gender.

SELECT name, age FROM person where age='21' OR gender='female'
This will output result if one of the two condition is true,  age & gender.

Limit output to 5 result:
SELECT * FROM person LIMIT 5

Sort result by Ascending/Descending order:
SELECT * FROM person ORDER BY name ASC
SELECT * FROM person ORDER BY name DESC

Query name which contain the letter  a.
SELECT * FROM person WHERE name LIKE '%a%'

Query only unique name:
SELECT DISTINCT(name) FROM person
This query is useful when we have many duplicate data of the same value.

Count total line in the table:
SELECT COUNT(name) FROM person

19 April 2013

Basic SQL tutorial



INTRO:
SQL is a programming languages designed for managing data. It is used for data storage or database storage. MySQL is the most popular among database management storage system. Others are Oracle, Windows Access, PostgreSQL, SQLite etc. Database are use from Office productivity to storage of various data type.

This tutorial will cover all the basic of the above and more database system..

SQL look like this..
SELECT * FROM table

Create a database:
CREATE DATABASE test
This will create a database with name  'test'.

Create a table:
CREATE TABLE person ( `name` VARCHAR(12), `age` VARCHAR(3), `gender` VARCHAR(6) )
This will create a table with name  'person' having column  name, age, gender.

Add a data to table:
INSERT INTO person (name, age, gender) VALUES ('anil', '18', 'male')
This will add a line into the table  person  to the column  name, age, gender  with value  anil, 18, male  respectively.

Check if the data/line has been added:
SELECT * FROM person
Output:
name age gender
anil 18 male


Note:
* the number in  VARCHAR(12)  signify only 12 characters will be saved and rest omitted. If we enter a name  'verylonglongname' (16 char), the saved name will only be  'verylonglong' (12 char), last 4 char omitted.


11 April 2013

BACKUP FACEBOOK PROFILE



Facebook is the leading social network, there is no doubt. It has more than 1 billion registered user. There is hardly any one who had not heard (or using) it.

This tutorial is an example on how to backup/download 'facebook profile' (our own profile). Frustated at not finding a single tutorial on how to backup my user profile. i went ahead and created this tutorial. back to the point, we will use 'cURL' (and 'wget') for this purpose as it is available for almost all system.

Facebook data are available from 'https://graph.facebook.com'. Basic info like id, username, name, link, gender & locale are available publicily. Throw in an access token you have all data that is in public or visible to friends. Example below:
https://graph.facebook.com/jor.teron

Required:
* cURL or wget
* User ID (UID)
* facebook access_token
* Basic idea on FQL and FQL tables (optional)

This method work by invoking graph explorer...
https://graph.facebook.com/fql?q=FQL_QUERY&access_token=ACCESS_TOKEN
where  FQL_QUERY  is the FQL query implemented by facebook. If  FQL_QUERY  is..
select description from group where gid=XXXXXXXX
..then url will be like.
https://graph.facebook.com/fql?q=select+description+from+group+where+gid=XXXXXXXX&access_token=ACCESS_TOKEN


STEP TO FOLLOW:
1) Goto Graph API Explorer, https://developers.facebook.com/tools/explorer. Login if necessary. Click 'Get Access Token' button. Select appropriate permission. save it. Copy the access token (those jumbled alphabet).

2) Note down your UID.

3) Open 'Command Prompt' and type in the following.
for cURL:
curl --user-agent 'Firefox 10' \
--output file.txt \
"https://graph.facebook.com/fql?q=select+uid,username,name+from+user+where+uid=UID&access_token=ACCESS_TOKEN"

for wget:
wget --user-agent='Firefox 10' \
--output-document=file.txt \
"https://graph.facebook.com/fql?q=select+uid,username,name+from+user+where+uid=UID&access_token=ACCESS_TOKEN"

note:
* Replace UID and ACCESS_TOKEN with its respective values you got in step 2 and 1 respectively.
* ACCESS_TOKEN is valid only for 1 hour, so you will need to get it (again) later if needed.

In the above example it'll download User Profile (containing id, username, name) and save it to 'file.txt'. You can specify more column if needed.


To download photos:
curl --user-agent 'Firefox 10' \
--output photo.txt \
"https://graph.facebook.com/fql?q=select+src_big+from+photo+where+owner=UID&access_token=ACCESS_TOKEN"
Sort out photos links using grep, sed, awk or regex to download them. In these way one can download facebook data from profile to photos, comments to likes, pokes to messages etc.


If FQL intimate you, there is an easier way to fetch data by using Graph Search....
https://graph.facebook.com/USER?fields=GRAPH_OBJECT&access_token=ACCESS_TOKEN
Example:
https://graph.facebook.com/jor.teron?fields=id,username,name&access_token=ACCESS_TOKEN

This tutorial will benefit those who want to backup their facebook data. Alternatively if you find this process hard to follow you can use facebook own feature 'Copy Facebook'.