reserved keywords in mysql

today,  I created 3 mysql tables for my facebook app, to save user "status" "comment" "like" infomation.

I did not think too much before i created the 3 tables with name: status, comment, like respectively.
then, I found I could insert content into status and comment table while always failed the like table .

/*php code*/

mysql_query("INSERT INTO like (object_id,user_id,type) VALUES ('$comment_id', '$like_user_id','comment')");//error

I did know the 'like' is one mysql reserved keyword, however, i did not think that way at that very moment

then when I changed the table name from 'like' to 'likes', then it works.

/*php code*/

mysql_query("INSERT INTO likes (object_id,user_id,type) VALUES ('$comment_id', '$like_user_id','comment')");//correct

//or
mysql_query("INSERT INTO my_like (object_id,user_id,type) VALUES ('$comment_id', '$like_user_id','comment')");//correct

Here is the official mysql reserved keyword.
http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

however, I think it's safe to name your table or column as: my_xxx.

facebook fql format

pay attention to the "+" in the fql

 // 1.

 $access_token="XXX";

 $url0 = "https://graph.facebook.com/"

    . "fql?q=SELECT+message,time,status_id+FROM+status+WHERE+uid=10000+"

    . "AND+time>=1365481000+AND+time<=1365483292+LIMIT+0,1"

    . "&access_token=" . $access_token;



 $res0 = json_decode(file_get_contents($url0));






//2.



$url1 = "https://graph.facebook.com/"
                   . "fql?q=SELECT+fromid,time,text,object_id+FROM+comment+WHERE+object_id+"

                   ."IN+(SELECT+status_id+FROM+status+WHERE+uid=$uid)+"

                   ."AND+time>=$tm1+AND+time<=$tm2"
                   . "&access_token=" . $access_token;

$res1 = json_decode(file_get_contents($url1));

get realtime update of user infomation using facebook app

you can configure the framework according to the official document at:

http://developers.facebook.com/docs/reference/api/realtime/

if succeed, you can get the infomation like:

{ "object": "user", "entry":
    [    
         { "uid": 1335845740,      
           "changed_fields":
               [ "status" ],
           "time": 1365483292 }
   ]
}

It means the user(whose id is 1335845740 ) updated his status (most likely to post a new status).

what I want to say is, the 'time' 1365483292 you got is not exactly correct.

I use fql to crawl the status data and get the status created date is around 1365483292, but not exactly the same, say 1365483291. However, for some other times, they are  same.

So, if you got the update info as above, you should pay attention that "time" infomation.
if you still want to use this "time" info to crawl some latest data, you'd better use:

WHERE time BETWEEN $time-1 AND $time  // or something similar 

issue on inserting text or varchar values on mysql

when we insert text or varchar values into tables on mysql, we usually use the following format

mysql_query("INSERT INTO user (userid, name, gender) VALUES ('$user_id', '$user_name', '$user_gender'");

instead of

mysql_query("INSERT INTO user (userid, name, gender) VALUES ($user_id, $user_name, $user_gender");

yes, the difference is '$user_name' and  $user_name.

the advantage of former is that:
if your name is zhiguang cao (yes, there is one blank space between given name and surname )

when you insert  $user_name, it will be considered as two items: zhiguang  and cao respectively, and thus the result would not be correct generally.

while if you insert '$user_name', it will be considered as one item: 'zhiguang cao' would be one item instead of two.

I took me hours to find this problem although it seems not a big deal. thanks chenbo's help!

windows+apache+mysql+php(WAMP) for 64 bits

1. widows:
      we process this on 64 bit windows 7:
2. apache:
     I chooose: httpd-2.4.4-win64.zip
     create one folder:   C:/apache64,  unzip the file to that directory, and thus we can access httpd.conf file by :

   C:/apache64/conf/httpd.conf .
  
   modify this file:

   ServerRoot "C:/apache64"

   ServerName localhost:80

  DocumentRoot "D:\CZG\PHP_WEB"

  <Directory "D:\CZG\PHP_WEB">

   DirectoryIndex index.html index.htm index.php

   ScriptAlias /cgi-bin/ "C:/apache64/cgi-bin/"

 thus, when you input: http://localhost in your broswer, it will redirct you to D:\CZG\PHP_WEB

add your apache to system path by:
add  C:/apache64/bin/ to the path variable of your system.

then you can use the following on your cmd:

httpd.exe -k install

httpd.exe -k start

finally  you open bin folder and double click the ApacheMonitor.exe file.
If you input: http://localhost in your broswer now, it will display: it works!

when you run into problems with this, such as : the requested operation has failed.
you can using the following command line to check the reason:
httpd.exe -w -n "Apache2" -k start
it will give the specific error

3. php:
i choose: php-5.4.3-Win32-VC9-x64.zip
and unzip it to:  C:/php .
then in C:/apache64/conf/httpd.conf to add this or make it effective

LoadModule php5_module "C:/php/php5apache2_4.dll"   // for this case, is 2_4 not 2_2
 AddType application/x-httpd-php .php
 # configure the path to php.ini
 PHPIniDir "C:/php"

then rename the file php.ini-development to php.ini, and add or  make the following effective

extension_dir = "C:/php/ext/"
 allow_url_fopen = Off
extension=php_gd2.dll
 extension=php_mysql.dll;
extension=php_zip.dll

Set sendmail from e-mail address:

sendmail_from =xxx@gmail.com

Some settings for MySQL:  

mysql.default_port = 3306
 mysql.default_host = localhost

Some settings for session:

session.save_path = "C:/my_session" 

//to same sesssion file and creat one folder like C:/my_session

4.mysql:

i choose: mysql-essential-5.1.68-winx64.msi
install it by defauly, and do not forget the user name and passwork during the installing process
i choose username: root, password: 1234(anyone you like, as long as you can remeber)

then in the D:\CZG\PHP_WEB, create one test.php with the following content:

<?php

$con = mysql_connect("localhost:3306","root","1234");
if (!$con)
  {
  die("Could not connect: " . mysql_error());
  }
  
else
  {
    echo "it is connected to database!";
  }
mysql_close($con);


?>

then run it @ http://localhost/test.php

in my case, I can user localhost:3306 or 127.0.0.1:3306   but when I remove :3306, it dose not work.
i know the port for mysql is 3306, but I am sure, for my previous 32 bit version configuration, it dose not need to add the port in the php code. 

one more thing: for windows 7, the database would be by default stored in:

C:\ProgramData\MySQL\MySQL Server 5.1\data

you can modify this directory at:

C:\Program Files\MySQL\MySQL Server 5.1\my.ini

datadir="C:/ProgramData/MySQL/MySQL Server 5.1/Data/"

5. workbench

to better visualize the mysql  database, I installed workbench:mysql-workbench-gpl-5.2.42-win32.msi.
for first time, it needs the same user name and password, then you can see the table contents of your database like using excel.

using filezilla for amazon ubuntu server

when you create ubuntu instance on amazon ec2, you will get user name(say, root or ec2-user) and one .pem file
then in your local ubuntu, you can access the server by:

chmod 400 xxx.pem    #make this file publicly visible
ssh -i xxx.pem ec2-user@xxx.xxx.xxx.xxx  


# xxx.xxx.xxx.xxx  is the ip address amazon assigned to you

thus you can access the ubuntu server on amazon.
however, for transfering files between local machine and server, we usually use filezilla.
the default internet access directory is  /var/www/html  however, the general user do not have rights to upload files directly to this folder, so we can:

sudo chmod 777  /var/www/html 

thus we can use filezilla to transfer files directly to that folder.
after installing filezilla, we should configure first:
1)open the site manager->new site. then we name it as something you like, and for host, input the ip address, for port:input 22, for protocol: choose SFTP-SSH file...    for logon type: normal   for user: input your user name
2)edit-> settings->connection->SFTP, press: add keyfile. thus select your pem file, and  will convert it to ppk file.
after that, back to step 1), press "connect", then your local machine would connect to your amazon ubuntu server. You can now use mouse to drag files from your local machine to the server and vice versa.

post on user's timeline by app

<?php

  session_start();
  require_once('AppInfo.php');
  require_once('utils.php');
  require_once('sdk/src/facebook.php');
  
  $facebook = new Facebook(array(
   'appId'  => 'APP_ID',
   'secret' => 'APP_SECRET',
  ));


$app_token="XXXXXX"; //long life access_token should be better

 $attachment = array
 (
 "access_token"=>"$app_token",
 "message" => "Interesting App",
 "name" => "MyApp",
 "caption" => "About MyApp",
 "link" => "http://facebook.com/",  //your own url
 "description" => "Awesome App",
 "scope" => "publish_stream"
 );
 
 $result = $facebook->api("/USER_ID/feed","POST",$attachment);

print_r($result );
 ?>

send request from app to user using javascript

<html xmlns="http://www.w3.org/1999/xhtml"
  xmlns:fb="https://www.facebook.com/2008/fbml">
  <head>
    <title>MyNTU Request</title>
  </head>

  <body>
    <div id="fb-root"></div>

    <script src="http://connect.facebook.net/en_US/all.js"></script>

    <script>

      FB.init({
        appId  : 'xxxxxxxxxxx',    //your app id
        frictionlessRequests: true
      });
   
      FB.ui({method: 'apprequests',
          message: 'My Great Request',
          to: 'xxxxxx'                    //user id you want to send to
        }, requestCallback);

      function requestCallback(response) {
        // Handle callback heres
      }
    </script>
  </body>
</html>

send request from app to user using php

<?php


  $app_id = "YOUR_APP_ID";
  $app_secret = "YOUR_APP_SECRET";

  $token_url = "https://graph.facebook.com/oauth/access_token?" .
    "client_id=" . $app_id .
    "&client_secret=" . $app_secret .
    "&grant_type=client_credentials";

  $app_access= file_get_contents($token_url);
  $app_access_token = json_decode($app_access, true);

  $user_id ="USER_ID";

  $apprequest_url ="https://graph.facebook.com/" .
    $user_id .
    "/apprequests?message='INSERT_UT8_STRING_MSG'" . 
    "&data='INSERT_STRING_DATA'&"  .   
    $app_access_token . "&method=post";

  $result = file_get_contents($apprequest_url);
  $obj = json_decode($result, true);
  print_r($app_access_token);
  print_r($obj);
   
?>

get user realtime update for your app

First, you should go to your app setting page in facebook developer home page.

when you reach the app setting panel or dashboard,

choose  Settings-Realtime Updates

then in the dashboard, you can
1) in fields: input some the field you like, such as hometown_location
2) in callback: input your app url
    if your app url is http://myapp.com
    and your file to preocess the realtime update is  update.php
    then you should input: http://myapp.com/update.php
  
3) in verify token, you should input one string you like, e.g.: myfacebookapp123

save the changes.

Then you should prepare the update.php file on your server, you could copy the following code to your update.php file

<?php

  $verify_token = myfacebookapp123';
  if ($_SERVER['REQUEST_METHOD'] == 'GET' && isset($_GET['hub_mode'])
    && $_GET['hub_mode'] == 'subscribe' && isset($_GET['hub_verify_token'])
    && $_GET['hub_verify_token'] == $verify_token) 
 {
      echo $_GET['hub_challenge'];
   } else if ($_SERVER['REQUEST_METHOD'] == 'POST')
   {
    $post_body = file_get_contents('php://input');
    $obj = json_decode($post_body, true);
    $myFile = "testFile.txt";
    $fh = fopen($myFile, 'a+') or die("can't open file");
    $stringData = "Something updates, pls go to get it!\n";
    fwrite($fh, $stringData);
    fclose($fh);
  }
?>

Now you can go to facebook and modify one user's(who has authened your app before) hometown. after 1-2 minutes, your server could generate one testFile.txt with "Something updates, pls go to get it!" This post dose not analyze the content in  $obj . it is something like this:

  /*
  {
  "object": "user",
  "entry": [
    {
      "uid": 1335845740,
      "changed_fields": [
        "name",
        "picture"
      ],
      "time": 232323
    }, {
      "uid": 1234,
      "changed_fields": [
        "friends"
      ],
      "time": 232325
    }
  ]
}
  */

you can try to get them respectively use php. One thing we should pay attention to is that: through realtime update, we can only get the which filed has been updated, such as hometown. but we do not know what is the user's latest howntown unless we write codes to crawl that data.

get the access token and extend its lifetime for your app

1. the basic idea is : use your 'state' get 'code', then use 'code' get 'access_token', finally use current access_token to get the 60-day long access-token. thus, even the user is not logged into  facebook, you can still access his data:

<?php 
   
   $app_id = "YOUR_APP_ID";
   $app_secret = "APP_SECRET";
   $my_url = "YOUR_URL";
   session_start();
   
   $code = $_REQUEST["code"];
   if(empty($code)) {
     $_SESSION['state'] = md5(uniqid(rand(), TRUE)); // CSRF protection
     $dialog_url = "https://www.facebook.com/dialog/oauth?client_id=" 
       . $app_id . "&redirect_uri=" . urlencode($my_url) . "&state="
       . $_SESSION['state'] . "&scope=user_birthday,user_hometown, read_stream, friends_likes, email, user_status, publish_stream, status_update,offline_access";

     header("Location: " . $dialog_url);
  }
   
   if($_SESSION['state'] && ($_SESSION['state'] === $_REQUEST['state'])) {
     $token_url = "https://graph.facebook.com/oauth/access_token?"
       . "client_id=" . $app_id . "&redirect_uri=" . urlencode($my_url)
       . "&client_secret=" . $app_secret . "&code=" . $code;

     $response = file_get_contents($token_url);
     $params = null;
     parse_str($response, $params);

     $_SESSION['access_token'] = $params['access_token'];
     $graph_url = "https://graph.facebook.com/me/feed?access_token=" 
       . $params['access_token'];
     $user = json_decode(file_get_contents($graph_url),TRUE);//get all the data related with 'feed'
  echo "</br>";
  echo "<h3>Your infomation are:</h3>";
  echo "</br>";
  print_r($user);
  echo "</br>";
  echo "</br>";
   }
   else {
     echo("The state does not match. You may be a victim of CSRF.");
   }

 ?>

2. use the short-term token to get the 60-day access token

<?php

 $my_token_url="https://graph.facebook.com/oauth/access_token?  
    grant_type=fb_exchange_token&           
    client_id=YOUR_APP_ID&
    client_secret=YOUR_APP_SECRET&
    fb_exchange_token=YOUR_CURRENT_ACCESS_TOKEN";

 header("Location: " . $my_token_url);



?>

If your current token is not expired yet, you are likely to get the same token but with longer life(60 days) you can also see how long it will last every time you refresh the page.

redirect out of iFrame

In that case, you should use:

echo "<script>self.parent.location.href = 'YOUR_URL'</script>";

instead of

echo "<script>location.href = 'YOUR_URL'</script>";

But first, you should realize that you are located in one iFrame then.

all the 'scope' options in facebook app development

our app always ask for user permission, but you should type the correct offcial permission in the scope parameter. e.g

    function authUser() {
        
        FB.login(checkLoginStatus, {scope:'user_hometown, read_stream, friends_likes, email, user_status, publish_stream, status_update,offline_access'});
      }
    

if you want get the user's hometowm permission, you should type user_hometown instead of just hometown, otherwise you can never get this permission.
all below is all the latest scope options supported by facebook

   /*
   Supported scopes: ads_management create_event create_note email export_stream friends_about_me friends_activities friends_birthday 

   friends_checkins friends_education_history friends_events friends_games_activity friends_groups friends_hometown friends_interests 

   friends_likes friends_location friends_notes friends_online_presence friends_photo_video_tags friends_photos friends_questions 

   friends_relationship_details friends_relationships friends_religion_politics friends_status friends_subscriptions friends_videos 

   friends_website friends_work_history manage_friendlists manage_notifications manage_pages offline_access photo_upload publish_actions 

   publish_checkins publish_stream read_friendlists read_insights read_mailbox read_page_mailboxes read_requests read_stream rsvp_event share_item sms 

   status_update user_about_me user_activities user_birthday user_checkins user_education_history user_events user_games_activity user_groups user_hometown 

   user_interests user_likes user_location user_notes user_online_presence user_photo_video_tags user_photos user_questions user_relationship_details user_relationships
 
   user_religion_politics user_status user_subscriptions user_videos user_website user_work_history video_upload xmpp_login
   */
  

user login using FB for facebook app development

All you need to do is to replace the Uppercase Variables with your own real parameter. If the user dose not log into facebook, then it will redirect the user to the facebook login page; if the user user has loged into facebook but not authened your required permission, then it will prompt the user permission pannel. if the uer has loged into facebook and authened all your permission, it would direcly redirect the user into your app;

develop web version facebook app using heroku

there is official document to do this, it is applicable both to linux and windows.

the link is :

https://devcenter.heroku.com/articles/facebook

It is not very diffcult althoug sometimes you do not know what dose it mean. Heroku helps us save the time to setup a server thus we could focus on the desining the app code.

But for most of the beginner, I think you would run into the following problems if it is your first time to use the above link.

For Heroku
1. we use heroku toolbelt, git version of control system to interact with the server with our local machine. So, pls remeber to add  your %path%.

2. if you still run into:

fatal: Not a git repository (or any of the parent directories): .git

pls remember to do the following:

git init
git add . #the '.' after add should be typed
git commit -m 'Initial commit'

3.if you need to change the configure of git, you can find it in the .git folder, which is a hidden child folder of your app folder.

4. if your local os is linux, it will redirect you to: https://toolbelt.heroku.com/debian to install the heroku toolbelt.
you should paste this to your terminal and run:
 wget -qO- https://toolbelt.heroku.com/install-ubuntu.sh | sh

in the mid of it, you will have to agree with one license term where it shows you <ok>. if you directly enter return, it will fail, then pls press "tab" first ,then press return.


5. when you run into:

The authenticity of host 'heroku.com (50.19.85.132)' can't be established.
RSA key fingerprint is *some.random.fingerprint.
some people suggest:
in .git/config, changed the project name in [remote "heroku"] to the name given by heroku

I do not know what dose it exactly mean, and I just jump it and without modifying anything, so far, the app still works fine.

6. if you have modified the codes, and you want to run the two commands:
$ git commit -am "changed greeting"
$ git push heroku
before you do that, make sure you have cd into your app folder. otherwise, it may not detect any modification of your app

For Localhost
If you want to develop the app locally, this post seems fine for me:
http://www.7tech.co.in/php/how-to-create-a-facebook-application-using-php/

you should build you local machine as a server first, the normal
WAMP is ok.

then when you choose to create new app, you should remeber the app id and app secret, and do not select using heroku as server.

while in the site url: you should input: http://localhost/myapp/
and for the canvas page url you could use the same addrss
for canvas secure url you could use: https://localhost/myapp/ instead.

about the php session for ajax

In fact, there is nothing serious, i just want to say that the php session  in index.html and in the php files which is used to respond to the ajax could share the same $_SESSION[];

however, you should not forget one thing, in the php files responding to the ajax, should write:

session_start();

I only know this today, which grealy faciliate my work!