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

Oracle Certified Associate, MySQL 5.0/5.1/5.5 certification (CMA)

I went to take an Oracle examination on 27th Oct 2010 and successfully obtained an Oracle Certified MySQL Associate (CMA).

Prior to the examination, I bought the guide book MySQL 5.0 Certification Study Guide from Amazon and read through all the chapters required.

Some important notes:

1. Some websites recommended certain chapters or sections to study for the examination. Most of them did not include the Joining Table section.
2. Also, do note that for Joining Tables, only INNER JOIN is tested (At least for my examination. There are 4 questions on INNER JOIN). 

Here are the exam contexts
Exam Contents - Certified MySQL Associate

• Theory, Terminology and Concepts (10%)
  • Client/Server Concepts
  • Database and Database Objects
• Data Definition using SQL (25%)
  • Databases
  • Data Types
  • Tables
  • Constraints and Indexes
  • Views
• Basic Data Manipulation using SQL (25%)
  • Recurring SQL Constructs
  • Adding data
  • Modifying data
  • Removing data
  • Searching data
• Advanced Data Manipulation using SQL (20%)
  • Expressions
  • Grouping and Aggregate Functions
  • Joining Tables
• Transactions (10%)
  • Transaction Concepts
  • SQL for working with Transaction
• Import/Export (10%)
• Tools for Import/Export
• SQL for Import/Export

One key tip for the examination is to practise the questions in the CD and do some practice questions which can be found online. Another tip is to try out the examples on an actual MySQL to understand the topics even better. 

If you want to take a quick look at some of the MySQL syntax, you can check out my other blog here.

You can check out the price of the exam at the Oracle homepage 

I did relatively well for the examination, scoring 92% which means getting 46 out of 50 questions correct. You will know your score immediately after you ended the examination. 

If you have any questions on MySQL, do send me an email so that we can discuss about it. Alternatively, you can post it here so that we can share and discuss! :)

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!!