Import csv file from Select of MySQL

When you want to export csv file from MySQL, you may use phpMyAdmin as first choice. But you may use select clause to export csv file as another choice. The following is one example.

Table

name
Sam
Bush
....
Jone

mysql > select * from test_table where name LIKE "good"   INTO OUTFILE "/tmp/export.csv" fields terminated by ',';

After you execute the above code, you mat get export.csv at your tmp directory. On the contrary, you may import from csv file.


mysql > load DATA LOCAL INFILE  "/tmp/export.csv" INTO TABLE test_table  fields terminated by "," LINES TERMINATED BY "\r\n";

connect mysql based on PHP

Before we use MySQL, we should create instance for MySQL. Now I give one example based on PHP source code.

Based on classic ways
1. connect MySQL

$link = mysql_connect("localhost", "root", "abc123456");
if (!$link) {
    die('Could not connect: ' . mysql_error());
}

2. Select database
mysql_select_db("test_db", $link);

3. Execute select, update, insert .....


$result = mysql_query('SELECT email, name, pass FROM users ');
while ($row = mysql_fetch_assoc($result)) {
    var_export($row);
}


4. Close MySQL
mysql_close($link);

Based on Security ways

1. connect MySQL
$link = new PDO('mysql:host=localhost;dbname=test_db', 'root', 'abc123456');

2. Execute select, update, insert .....
$sth = $pdo->query('SELECT email, name, pass FROM users');
foreach($sth as $row) {
    var_export($row);
}

Install Centos 6 and httpd+php5.3 +mysql5.5 on Windows Home 7

In order to install centos 6 with your windows, please prepare the following software

1. Download VM VirtualBox
http://www.oracle.com/technetwork/server-storage/virtualbox/downloads/index.html

2.  Download Centos 6
http://ftp.jaist.ac.jp/pub/Linux/CentOS/6/isos/x86_64/


3.  Download VirtualBox 4.2 for Linux
http://download.virtualbox.org/virtualbox/4.2.0_RC4/


Install Process
1. Install VM VirtualBox

2. Install centos

3. update yum
yum update
yum install gcc kernel-devel kernel-headers


4. install virtualLinuxAddtions

If you meet error with[Building the OpenGL support module  [FAILED]] , please use the following command

export MAKE='/usr/bin/gmake -i'

./VBoxLinuxAdditions.run


if you still have errors, please try

/etc/init.d/vboxadd setup


5. install httpd
yum install httpd
#auto start 
chkconfig httpd on 

6. install php
yum install php

7. install mysql
## Remi Dependency on CentOS 6 and Red Hat (RHEL) 6 ##
##  http://mirrors.ustc.edu.cn/fedora/epel/6/
rpm -Uvh http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-XXX.noarch.rpm

## CentOS 6 and Red Hat (RHEL) 6 ##
rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm

yum --enablerepo=remi,remi-test install mysql mysql-server
#auto start when PC start
chkconfig mysqld on

install PHP5.3 on centos 5 or centos 6

Before you install PHP.3, you should update extra reposi. Please use the following url

$ wget http://dl.fedoraproject.org/pub/epel/5/x86_64/epel-release-5-4.noarch.rpm
$ wget http://rpms.famillecollet.com/enterprise/remi-release-5.rpm

$ sudo yum install php php-cli php-gd php-mysql php-mbstring

get the value of more one PHP constants dynamically

using php's constant function

When you define many constants, it is difficult to call them when you use foreach or for clause. Now we may use constant function to realize them.

For example, we define

define('A_1','A');
define('A_2','B');
........
define('A_1000','KKKKKK');


when we use

$a = 88;

echo constant('A_'.$a );

or

for($i=50;$i<70 i="i" p="p">   echo constant('A_'.$i);
}

array_diff not support ojbect

array_diff for parameters does not support objects


e.g.
$a = array(
   'a',
   'b',
);
$b = array(
   'b',
   'c'
);

$c = array_diff($a, $b);

$c = array(1=>'b');

------------------------------

for object, 

$a = array(
   new stdClass(),
   'b',
);
$b = array(
   'b',
   'c'
);

$c = array_diff($a, $b);

you will get error message
Catchable fatal error: Object of class stdClass could not be converted to string in...............

multi-language setting gettext for php

The following code creates multiple language site based on gettext from PHP.

Language:
English and Japanese
English:  Hello
Japanese: こんにちは

Step 1:  Get supported language from client

Method 1, auto-detect
<?php
$language = substr($_SERVER['HTTP_ACCEPT_LANGUAGE'], 0, 2);
if ($language == 'ja') {
    $language = 'ja_JP';
}else{
    $language = 'en_US';
}
?>

Method 2, $_GET-detect

<?php
$language = $_REQUEST ['lan'];
if ($language == 'ja') {
    $language = 'ja_JP';
}else{
    $language = 'en_US';
}
?>


Step 2:  Set locale and default domain
<?php
putenv("LANG=$language");
setlocale(LC_ALL, $language);
$domain = 'domain_file_name';
bindtextdomain($domain, "/var/www/html/locale");
textdomain($domain);
?>

Step 3:  Output
Method 1, 
<?php
echo _('Hello');
?>
Method 1,
<?php
echo gettext('Hello');
?>

Now you may save all codes above with one php file with any name, here I use index.php, which looks like
 ------------------index.php--------------------------------
 

<?php

$language = $_REQUEST ['lan'];
if ($language == 'ja') {
    $language = 'ja_JP';
}else{
    $language = 'en_US';
}

putenv("LANG=$language");
setlocale(LC_ALL, $language);
$domain = 'domain_file_name';
bindtextdomain($domain, "/var/www/html/locale");
textdomain($domain);

echo _('Hello');
?>

 -------------------------------------------

Step 4:   Create po file based on $domain setting, the command looks like

xgettext -d domain_file_name index.php --from-code=utf-8

After executing the command, you may get one new file, whose name is 'domain_file_name.po'. Open it
-------------------------domain_file_name.po------------------------------
# SOME DESCRIPTIVE TITLE.
# Copyright (C) YEAR THE PACKAGE'S COPYRIGHT HOLDER
# This file is distributed under the same license as the PACKAGE package.
# FIRST AUTHOR , YEAR.
#
#, fuzzy
msgid ""
msgstr ""
"Project-Id-Version: PACKAGE VERSION\n"
"Report-Msgid-Bugs-To: \n"
"POT-Creation-Date: 2012-10-12 14:41+0900\n"
"PO-Revision-Date: YEAR-MO-DA HO:MI+ZONE\n"
"Last-Translator: FULL NAME \n"
"Language-Team: LANGUAGE \n"
"MIME-Version: 1.0\n"
"Content-Type: text/plain; charset=CHARSET\n"
"Content-Transfer-Encoding: 8bit\n"

#: index.php:16
msgid "Hello"
msgstr ""

---------------------------------------------------------

Then you may change 

1.  charset=CHARSET  -> charset=utf-8

2. msgstr "" -> msgstr="こんにちは"

------------------------------------------------------------

Step 5:   Create mo file based on domain_file_name.po, the command looks like
 msgfmt domain_file_name.po 

Now you have created one file, whose name is  domain_file_name.mo

Step 6:  Move domain_file_name.mo to special directory like

locale

       /en_US

                 /LC_MESSAGES

                                          / domain_file_name.mo

      /ja_JP

                 /LC_MESSAGES

                                          / domain_file_name.mo


Now you may access the index.php from your browser by 

index.php?lan=ja_JP or index.php

and you may get different message.