PHP Data Object / PDO and Prepared Statement

One of the comments made to my previous post is to use PHP Data Object (PDO), specifically prepared statements instead of mysqli. After some reading and testing, I decided to modify all of my PHP scripts.


I will recommend 2 websites to read before you consider making changes to your existing system.

1. Eric Werzer 
2. PHP website

Eric Werzer's website provides very simple examples and usage methods. After reading and using his codes, I picked up PDO in a very short time.

The PHP website illustrates the advantages of using a Prepared Statement. In summary, there are 2:

1.  The query will need to be prepared once and it can be execute many times. This means that prepared statements use fewer resources and thus run faster. In the long run, this can improve on the efficiency of the system.
2. No SQL injection will occur. This will further enhance the security of the database system, especially key information of our customers.

One side note, do ensure that '--with-pdo-mysql' is configured for your php. You can find it when you run your phpinfo(). '--with-pdo-mysql' will be inside the Configure Command box.

Thanks to the valuable advice given by Mike - who commented in my previous post, I have discovered this new skill. =) Do drop in more comments!

PHP: Gmail Class

I have created a class for Gmail using PHP.This class allows file attachments to be automatically downloaded into your server file directory.
Currently, I am still writing the functions for sending mail.

Do take note that in order for the server to be able to access gmail, imap-ssl must be configured for your PHP. You can check whether imap-ssl is installed by running a PHP which contains phpinfo(). Under the 3rd column, Configure Command, you should be able to see: ' --with-imap-ssl'
If you do not see that, please reconfigure your PHP installation to include that.

You can download the PHP files for the class here and the class tester here.
Alternatively, you can visit my website here.

Here are some snapshot of the output and the codes.

Output:

When there is a same file in the directory in which the file is to be downloaded into.
The 2nd one shows a file being downloaded.

 

Here are some snapshots of the code...

This is the PHP to test the Gmail Class

PHP: Vigenere Cipher

I needed to use Vigenere Cipher to encrypt my data for my server-side applications. After a quick research,  I got a code from a forum and made some modifications to them. Now, the default code is also able to encrypt more than just 26 alphabets. An EnsureKeyValid function is added to ensure that only valid indexes are used.

You can download the Vigenere Cipher Class here and the class tester here.
Alternatively, you can go to my personal website to download them.

The output of my tester is:
attackatdawn = lxFopveFrnHr = attackatdawn

'lxFopveFrnHr' is the encrypted form of 'attackatdawn'

Here are some snapshots:

To test the class:

Output:

PHP and MySQL Part 2

There are 2 methods of using mysqli. One method of using it is by object orientated and another way is by procedural.

Personally, I prefer to use object orientated.

Here is a function which I have created to connect to the database. It will return an Object if it connects successfully. If not, it will return a false which indicates that the connection failed.
$result = new mysqli ('hostname','username','password','database_name');

The 4 string values to provide mysqli are the hostname (such as localhost), username, password and the name of the database. The username and the password must be registered as a user of the database and have sufficient privileges to access the database.

Below is a simple database connect function.

We are interested to perform a query on the database so that we can obtain the results we need. How do we do so? For the object orientated method, mysqli->query will return an Object. The object will contain certain attributes such as num_rows (no. of rows), error, close, etc. For a whole list of available attributes, you can read here.

Below is a simple query function.

Now, we are able to connect to the database and perform a simple query using PHP. I will try to include some MySQL tutorials soon. =)

HTML, PHP and MySQL overview

PHP is a server-side scripting language. It is often used together with HTML to "pass" information to the server. MySQL is a Relational Database Management System (RDMS). In simpler terms, it is a database management system which is used to store data into tables. These tables are linked together by relationships (Hence it is called a Relational Database). 

Here is a basic application for a HTML form. The form data is saved into the database. Below shows the sequential flow for a simple data insert into the database.

As seen in the flowchart above, 

1. After the user keys in the data on the HTML form, it will be sent to PHP. 
2. PHP gathers the data and connects to MySQL (How it connects, we will discuss in further blog entries). 
3. After the connection is done, PHP sends the data to MySQL. MySQL receives the data and stores it.

Another application is when a user wants to search for things inside a database. For example, you might want to search for the availability of a book in the library. Below shows how a user starts the search and how the results are displayed to the user.

1. The User enters a search request in HTML. 
2. PHP gathers data from HTML, connects and send the query to MySQL. Query is like a search request. For example, the results that you are interested to find, are books which are published in the year 2010 only. With so many books in the library's database, PHP must provide a query to MySQL so that it can return the correct information.
3. Once MySQL receives the query from PHP, it will perform the search in its database. 
4. After the search, it will return the results to PHP. 
5. PHP will retrieve the results from MySQL and send it back to HTML. 
6. HTML will now display the results for the user to see.

Hope you all enjoy my HTML, PHP and MySQL overview! Do subscribe to my blogspot! Do share with me if I make any mistakes. If you have any topics to discuss, do let me know so that I can post it up! =) 

PHP 5.3.2, MySQL 5.1.49, Apache 2.2.16 on MAC OS X 10.6.4

I need to compile PHP, MySQL and Apache on my Macbook Pro. It is really not easy! Besides trying to follow the numerous out-dated posts in the entire internet, I have to contend with a lot of technical and software issues with regards to my MAC (I am a new Mac user).
The versions in which I used are
PHP 5.3.2
MySQL 5.1.49
Apache 2.2.16
on Mac OS X 10.6.4
There are some websites which I have referred to and I will definitely recommend you to read.

1. Sean Coates’ Blog – I figure out how to download PHP and Apache using the terminal PLUS the installation of libiconv-1.13.1. The file iconv caused such a great headache when I tried to compile things using MAMP. He also solved another headache by teaching how to download libpng, libjpg, libxml2, … All these took me several days to solve.
2. Zero Inverse’s Blog – I require IMAP-SSL so that I am able to use Gmail from my PHP. Initially, I am using MAMP. But MAMP does not support IMAP-SSL. (It supports IMAP and OpenSSL but you need to recompile the Apache to enable IMAP-SSL support).

Here are my installation steps:

• Download and install Xcode. You need Xcode to enable the Make command (and many other developer tools).
• Install Homebrew. I am not using Macports.

1. $ curl http://gist.github.com/raw/323731/572b315c4f7ee78244de70e7ad703c8ae324da7a/install_homebrew.rb > install_homebrew.rb
2. $ ruby install_homebrew.rb

• Install your own iconv. Although Apple does provide, I faced a lot of problems when I tried to use it.

1. $ curl http://ftp.gnu.org/pub/gnu/libiconv/libiconv-1.13.1.tar.gz | tar -zx -
2. $ cd libiconv-1.13.1
3. $ ./configure –prefix=/opt && make && make install
4. $ cd ..

• Download and install Apache-HTTPD

1. $ curl http://apache.mirror.iweb.ca/httpd/httpd-2.2.16.tar.bz2 | tar -jxf -
2. $ cd httpd-2.2.16/
3. $ ./configure –enable-rewrite –enable-ssl && make && make install
4. $ cd ..

• Install PHP dependencies using Homebrew

1. $ echo “gd jpeg libpng libxml2 libzzip mcrypt mysql” | xargs brew install
2. $ echo “libpng libxml2 readline” | xargs brew link

• Install PHP from source. A patch is required else the iconv won’t work.

1. $ curl -L http://ca2.php.net/get/php-5.3.2.tar.bz2/from/this/mirror | tar -jxf -
2. $ cd php-5.3.2
3. $ curl http://www.php.net/~scoates/patches/php-5.3.1-Makefile.global-iconv.patch | patch -p0
4. $ ./configure –prefix=/usr/local –with-xsl –with-gd –with-zlib-dir –enable-sockets –enable-exif –with-mcrypt –enable-soap –enable-embedded-mysqli –with-mysqli=mysqlnd –with-mysql –with-pdo-mysql –with-curl –with-libedit –with-apxs2=/usr/local/apache2/bin/apxs –enable-mbstring –with-openssl –with-imap –with-imap-ssl –with-iconv=/opt && make && make install
5. $ cd ..

• Configure Apache

1. Make sure that the module is loaded. It should be loaded by default.
   LoadModule php5_module modules/libphp5.so
2. In /usr/local/apache2/conf/httpd.conf, add these:
   If you are using terminal, type this: cd /usr/local/apache2/conf/ [enter] and pico httpd.conf [enter]. Under LoadModule php5_module modules/libphp5.so
   AddType application/x-httpd-php .php
   AddType application/x-httpd-php-source .phps DirectoryIndex index.html index.php
3. Before checking phpinfo, it is a must to check whether there are any other applications using the default port 80. My port 80 was occupied by the built in apache and cannot be accessed. I solved it by switching to port 8888. Here are the 2 lines which I edited in my HTTPD.conf file.
   Listen 8888 and ServerName localhost:8888
4. Now test Apache + PHP by creating php.info().        $ echo “<?php phpinfo(); ?>” > /usr/local/apache/htdocs/index.php
   $ ln -s /usr/local/apache2/bin/apachectl /usr/local/bin/apachectl
   $ sudo /usr/local/bin/apachectl restart
5. Now visit localhost:8888/index.php. You should see this:

Most importantly, I saw this…

Hooray! My IMAP-SSL is up!!