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!

tranverse files under one directory using C++ on ubuntu

#include <string>
#include <iostream>
#include <stdlib.h>
#include <stdio.h>
#include </usr/include/i386-linux-gnu/sys/types.h>
#include </usr/include/i386-linux-gnu/sys/stat.h>
#include <dirent.h>
#include <errno.h>
using namespace std;
int main(void)

{
   DIR *dp;
   int n=0;
   int len=0;
   struct dirent *dirp;
   string str0;
   dp=opendir("/home/HSS/topic_modeling/topic_1/");

   while((dirp=readdir(dp))!=NULL)
   {   
      str0=string(dirp->d_name);
      len=str0.size();
      if( len>4) 
// for txt files and this will filter out the '.' and '..' file
      {
            // do what you want to do !
      }
   }
return 0;

}

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.