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: Send Mail with Attachments using Local Mail Server

I have modified some codes from the forums for sending email with attachments using local mail server. This script is tested with Gmail and Hotmail. Both mail servers are able to receive the emails.

However, when I include the attachments in the sending mail, Hotmail accounts are unable to receive them. As for Gmail accounts, there are no problems at all.

I am still trying to find out why Hotmail is unable to receive the attachments. If anyone knows how to solve the problem, do let me know. 


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


As usual, here are some snapshots of the output and the code:


1. Message received by Gmail.

2. Message output from server.

Codes:

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. =)

PHP and MySQL

In order for PHP to "connect" with MySQL, the mysqli extension must be enabled.

This can be done by running the configure script with a certain option. This must be done prior to building PHP.

For PHP 5.0, 5.1 and 5.2, the option is:
-with-mysqli=mysql_config_path/mysql_config. 
The mysql_config_path represents the location of the mysql_config program. The above option will enable mysqli and it will use the MySQL Client Library to communicate with the MySQL server.


For PHP 5.3.0 or newer, mysqli uses the MySQL Native Driver by default. For more information about MySQL Native Driver, click here.

How to use MySQL Native Driver with mysqli?
You need to configure the PHP source code using this option: --with-mysqli=mysqlnd

./configure --with-mysql=/usr/bin/mysql_config  \
--with-mysqli=mysqlnd \

If you do not know what ./configure is, do refer to my previous blog post here.
Currently, I am using PHP 5.3. You should see this in your phpinfo(), under configure command.

You can use mysqli in 2 forms. The first is object orientated and the second is procedural.
Do check this page for more information on the classes for mysqli.

Bravo! so now, we have connected PHP and MySQL! Next, I will move on to explain the fundamentals of MySQL first before we start to query the database. I will also start putting in some codes which I have done to share with everyone.

All the best and Happy coding!

PS: I love to learn new things, do drop me a msg if you have any programming problems to discuss!!

Introducing server side softwares!

We will look at some cool softwares which we can use for a Apache, MySQL and PHP setup.

It is a real dread if you want to try to configure PHP, MySQL and Apache from scratch. The very first time when I try to install the 3 components separately on my MacBook, it took me an entire day. If you are considering to take up this challenge, do read the installations for each components carefully. Certain versions of one software can only work with certain versions of another software (especially the latest versions).

While working on my project, I realized that I have to reconfigure PHP so that it can support IMAP and IMAP-SSL. IMAP-SSL is required to access Gmail. I have already written the installation for mac in my first post.

I have tried to use XAMP (for all platforms), MAMP (Mac, Apache, MySQL and PHP) and even WAMP (Windows, Apache, MySQL and PHP) (On my Windows platform). However, the reconfiguring of the 3 softwares to support IMAP-SSL is difficult. Another key consideration is the difficulty in upgrading the individual components. Many websites which provides the solutions to installing the components, are seriously outdated. If you are looking for a client interface program to get some work done, you certainly can consider these 3 softwares.

One important software that you all should consider using is Netbeans. You can also use it to program with HTML, XML, Javascript, Java, C, C++, PHP and many others. I am currently using Netbeans to write my PHP programs.

If you are looking to design your MySQL, you should seriously consider MySQL WorkBench.
MySQL allows you to design the database using the Entity-Relationship Diagram (ERD). The most cool thing about the software is that it allows Reverse and Forward Engineering!!

This means that I can now design a database model, click on Forward Engineering and pesto! The database is built and ready to be used! The Reverse Engineering takes in your codes and put it into a ERD. One powerful component about MySQL WorkBench is that it allows you to connect directly to localhost and at the same time, provide a great graphical interface. This software also enhances server administration process and simplify the import and export of databases process.

Do drop in some comments if you have any other softwares to recommend! =)