Install Phalcon PHP framework in openSUSE

Phalcon is a web framework implemented as a C extension offering high performance and lower resource consumption”. From the benchmark page, “The compiled nature of Phalcon offers extraordinary performance that outperforms all other frameworks measured in these benchmarks”. Personally i find it very easy to work with, less dirs and files for the project. And it’s documentation is quite organized and useful. Installation in Linux distros is quite easy for most of the parts. Let’s install in openSUSE 12.3 and 13.1. 1st of all let’s thank Mariusz Łączak for his useful work to provide repositories. Fire up a terminal and become root by su – .

If you are on openSUSE 12.3, add this repo:

[cc lang=”bash” escaped=”true” nowrap=”false” noborder=”true” line_numbers=”false” lines=”2″ tab_size=”4″]

zypper ar -f http://download.opensuse.org/repositories/home:/mruz/openSUSE_12.3/ Phalcon

[/cc]

For openSUSE 13.1:

[cc lang=”bash” escaped=”true” nowrap=”false” noborder=”true” line_numbers=”false” lines=”2″ tab_size=”4″]

zypper ar -f http://download.opensuse.org/repositories/home:/mruz/openSUSE_13.1/ Phalcon

[/cc]

zypper-add-repo-phalcon

Refresh the repos:

[cc lang=”bash” escaped=”true” nowrap=”false” noborder=”true” line_numbers=”false” lines=”2″ tab_size=”4″]

zypper ref

[/cc]

It will ask for the key to trust for the new repo, just type a to trust always. We are almost there.

Before installation of phalcon, we need to make sure that these dependencies are installed: mbstring, mcrypt, opensll, pdo/MySQL

If not, no worries install it by using this command:

[cc lang=”bash” escaped=”true” nowrap=”false” noborder=”true” line_numbers=”false” lines=”2″ tab_size=”4″]

zypper in php5-mbstring mcrypt openssl php5-pdo

[/cc]

Time to install phalcon:

[cc lang=”bash” escaped=”true” nowrap=”false” noborder=”true” line_numbers=”false” lines=”2″ tab_size=”4″]

zypper in php5-phalcon

[/cc]

And that’s it, but before jumping to your web dir to start a demo project, restart apache:

[cc lang=”bash” escaped=”true” nowrap=”false” noborder=”true” line_numbers=”false” lines=”2″ tab_size=”4″]

systemctl restart apache2.service

[/cc]

Phalcon documentation are self explanatory and easy to follow. Start here for a test project.

Backup and Restore MySQL databases [Part 4]

In the last article we discuss how to backup data in csv format via PHP and command line. In this part we will backup MySQL databases via mysqldump command. I assume you are logged in to your Amazon instance.

How to backup:

1- Normal database backup

mysqldump -u root -p databasename > /var/www/html/databasename.sql

Enter your root password, change database name to the database you want to backup. Change the path from /var/www/html to your path if necessary. Change databasename.sql to your database.sql[any name can be given].

2- Backup MySQL database without data

mysqldump -d -u root -p databasename > /var/www/html/databasename_wo_data.sql

Same as 1 except it will dump database without any data.

3- Backup MySQL database with one table

mysqldump -u root -p databasename tablename > /var/www/html/database_table.sql

Here we will have one addition, enter the table name which we want to backup.

4- Backup MySQL database with one table without data

mysqldump -d -u root -p databasename tablename > /var/www/html/database_table_wo_data.sql

Same as 3 except this will dump it without any data.

How to restore:

mysql -u root -p databasename < /home/ec2-user/databasename.sql

Enter root password, change databasename and path to the backup sql file. Remember to create the database 1st if your sql file does not have the create database command. Login to MySQL via: mysql -u root -p and create database:

create database databasename default character set utf8;

That will do it, just change the database name.

Continuation:

Part-1: Setup web server on Amazon AMI or CentOS

Part-2: Backup MySQL data into CSV

Part-3: Backup MySQL data into CSV with PHP

Part-5: Speed up your website loading time by using PHP APC

Backup MySQL data into CSV with PHP [Part-3]

In the previous article we discuss how to “Backup MySQL data into CSV” in terminal. In this post we will discuss how to create csv file with PHP and force it to download. We will be pulling data from MySQL database. 1st of all we will need connection to database, for that we will use this script. Change the necessary details and save it as conn.php. Now lets start with the csv.

[cc lang=”php” escaped=”true” nowrap=”false” noborder=”true” line_numbers=”true” lines=”40″ tab_size=”4″]
<?php
require_once ‘conn.php’;

$sql = $dbh->prepare(“SELECT u.name, u.email, u.phone, u.city, s.filename FROM_UNIXTIME(s.sub_time, ‘%M %D %Y – %l:%i %p’) from users u join submissions s on u.uid=s.uid where s.uid != ” and s.filename != ” order by s.sub_time desc”);
$sql->execute();
$row = $sql->rowCount();

if ($row > 0){

$columns = array(‘NAME’,’EMAIL’,’PHONE’,’CITY’,’FILENAME’,’TIME’);

$filep = fopen(‘php://output’, ‘w’);
if ($filep) {
$file = uniqid().’_’.time().’.csv’;
header(“Content-Type: text/csv”);
header(“Content-Disposition: attachment; filename=$file”);
header(“Pragma: no-cache”);
header(“Expires: 0”);
fputcsv($filep, $columns);

while ($rows = $sql->fetch(MYSQL_NUM)) {
fputcsv($filep, $rows);
}
die;
}
}
?>
[/cc]
The script is quite straight forward and simple to use/follow. Little bit explanation: we are joining two tables here and then checking if any record found with rowCount function, if so we proceed to create csv. The array store the headers which will be column headers in the csv file. Giving a unique name to the file we will download and then using PHP fputcsv function to write data, quite easy and painless.

Continuation:

Part-1: Setup web server on Amazon AMI or CentOS

Part-2: Backup MySQL data into CSV

Part-4: Backup and Restore MySQL databases

Part-5: Speed up your website loading time by using PHP APC

Backup MySQL data into CSV [Part-2]

In my last article i showed you how to “Setup web server on Amazon AMI or CentOS“, to continue that article further i will discuss how to backup MySQL data into CSV format. So let’s start, login into your machine through terminal and login to MySQL server and choose database by command ‘use databasename‘.  Let’s dump a table to csv:

SELECT * INTO OUTFILE ‘/var/www/html/users.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ ESCAPED BY ‘\\’ LINES TERMINATED BY ‘\n’ FROM users;

The above command will output all the columns and data into a csv file which will be saved in /var/www/html, change this according to your need.

Now let’s say we have multiple tables and they are connected with each other through keys. For example user table with votes table.

SELECT ‘Name’, ‘Email’, ‘Time’,’Phone’,’City’ UNION SELECT u.name, u.email, FROM_UNIXTIME(v.vote_time, ‘%M %D %Y – %l:%i %p’) AS timestamp, u.phone, u.city INTO OUTFILE ‘/var/www/html/users_votes.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ ESCAPED BY ‘\\’ LINES TERMINATED BY ‘\n’ from users u join votes v on u.uid=v.uid;

We are joining two tables here on uid, which is user id. Name,Time,Phone,Email,City will be the column headers in CSV for each column selected from database. vote_time i assume is stored in db as unix timestamp. That is quite straight and simple, nothing fancy there.

Of course you can join many tables to output your desire data. That’s it for now. In the next article i will show you how to save/download data in CSV format/file using PHP and MySQL with fputcsv function. Stay tune.

Continuation:

Part-1: Setup web server on Amazon AMI or CentOS

Part-3: Backup MySQL data into CSV with PHP

Part-4: Backup and Restore MySQL databases

Part-5: Speed up your website loading time by using PHP APC

Setup web server on Amazon AMI or CentOS [Part-1]

If you are using Amazon aws with ec2 instances, then i assume you already have the pem file to connect to the instances. Normally you will have different instances for your projects. I normally setup either Amazon AMI or Cent OS, prefer Amazon AMI as it is also based on Cent OS. Cent OS is best and easy OS for servers. I love the configuration and level of interaction it provides.
Let’s say you want to install a web server with MySQL and PHP, run the following command:

sudo yum install httpd mysql-server php php-mysql mysql php-xml php-pdo php-odbc php-soap php-common php-cli php-mbstring php-bcmath php-ldap php-imap php-gd mod_ssl

That will install all the necessary modules and packages to run a fully functional server.
In some cases we don’t need/want a web server, just database server will do it:

sudo yum install mysql-server mysql

That will only install database server on the instance.
Now let’s say we dont need either PHP or MySQL:

sudo yum install httpd

Some time we don’t need database server on the same web server instances:

sudo yum install httpd php php-xml php-mysql php-pdo php-odbc php-soap php-common php-cli php-mbstring php-bcmath php-ldap php-imap php-gd mod_ssl

That will install all necessay packages excluding MySQL.

Now if you have a MySQL server, for sure you would like to make it secure, that is easy:

sudo /usr/bin/mysql_secure_installation

The above command will ask you some questions, answer the questions carefully and you will get through it.
Don’t forget to remove anon users and test database while running the above command.

If you have .htaccess file and want to utilize mod rewrite, enable it in /etc/httpd/conf/httpd.conf in the server directory section:

AllowOverride None
to
AllowOverride All

To auto restart your server and database server in case the system is restarted:

sudo chkconfig httpd on
sudo chkconfig mysqld on

Manually start the servers:

sudo service httpd start
sudo service mysqld start

Some interesting information i will share in upcoming articles: Load balancer for multiple instances, configuring SSL on load balancer, creating databases and changing password, Dumping databases and saving table/database to csv and save only database schema. And separating your database server from web servers with load balancer and adding the instances to database for security purpose to read/write into database instance.

So stay tune, there are more and plenty to come.

Continuation:

Part-2: Backup MySQL data into CSV

Part-3: Backup MySQL data into CSV with PHP

Part-4: Backup and Restore MySQL databases

Part-5: Speed up your website loading time by using PHP APC

How to insert csv file data into MySQL database

MySQL have this load data function, where we can insert data to a table from external files like csv, txt etc.
The process is quite simple, 1st lets login to mysql via terminal/console.

mysql -u root -p

Enter your MySQL password. Change root to any username(MySQL users) you would wish to login to MySQL.

mysql>

Now lets run the command.

mysql> LOAD DATA LOCAL INFILE ‘/var/www/site/site_users.csv’
INTO TABLE site_users FIELDS
TERMINATED BY ‘;’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’
IGNORE 1 LINES;

You will see this if command ran successfully:

Query OK, 370 rows affected (0.00 sec)
Records: 370  Deleted: 0  Skipped: 0  Warnings: 0

In the 1st line, we load a file from local dir and that is the reason we used ‘LOCAL’.
2nd line will insert the data into site_users table.
In the 3rd line we tell the command to break the fields by ; . You can check your csv file in txt format that whether its ; or comma(,).
4th line means that data is enclosed with the double quotes.
And lines are terminated by \n.
Last line will ignore the 1st line, which usually represents the data headers.

One thing i would like to mention here, make sure that the table fields and csv columns sequence matches.
Like name, address, phone are in the table, csv will also be name, address, phone. Which means data will be inserted into the right field.
And yeh column name can be changed, that does not matter like in table first_name and in csv name. What i mean to say here is that the sequence of fields need to match that right data goes to the right field.

That’s it.