connecting MySQL database using php script

Advertisements

We have seen a lot of php scripts accessing mysql databases. But ever wondered how they work? Here we will discuss a small php script which can access mysql database(test) and list some columns of the table(people). After reading this you wil know how to connect to mysql using php script from CLI or command line interface. You will need mysqli php module loaded for the php script to work. We will discuss these in detail. In this example we have one Centos 5.2 os installed on a vmware workstation.

Pre-requisites:
you must have mysql server installed and running in your system.
And the php rpms installed

Checking the mysql status:
[root@server ~]# /etc/init.d/mysqld status
mysqld (pid 5601) is running...
[root@server ~]#

Checking the php rpms:
[root@server ~]# rpm -qa | grep -i php
php-cli-5.1.6-20.el5
php-common-5.1.6-20.el5
php-5.1.6-20.el5
php-mysql-5.1.6-20.el5
php-pdo-5.1.6-20.el5
[root@server ~]#

You must have mysqli module installed and loaded. Then only php script can connect to mysql.
[root@server ~]# php -m | grep mysql
mysql
mysqli
pdo_mysql
[root@server ~]#

If not loaded, install it using the following command
[root@server ~]# yum install php-mysql

Now in this example, we will connect to mysql and list the first_name and last_name of the users in people table of the databases test.
This is what we have in mysql.
[root@server ~]# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.45-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.08 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| people         |
+----------------+
1 row in set (0.00 sec)

mysql> select * from people;
+---------------------+-----------+------------+
| first_name          | last_name | mob_number |
+---------------------+-----------+------------+
| Randeep Raman 1234  | NULL      | NULL       |
| Nibul Roshan  5678  | NULL      | NULL       |
| Afilaj Hussain 1357 | NULL      | NULL       |
| Renjith             | menon     | 1234       |
+---------------------+-----------+------------+
4 rows in set (0.00 sec)
mysql>

This will be our result for the script we are going to make.
mysql> select first_name, last_name from people;
+---------------------+-----------+
| first_name          | last_name |
+---------------------+-----------+
| Randeep Raman 1234  | NULL      |
| Nibul Roshan  5678  | NULL      |
| Afilaj Hussain 1357 | NULL      |
| Renjith             | menon     |
+---------------------+-----------+
4 rows in set (0.00 sec)
mysql>

The script is as follows.
[root@server ~]# cat test.php
<?php
/* Connection object */
/* now we will define the connection object */
/* syntax is as follows */
/* $conn_object_name = new mysqli("hostname", "user_name", "Password", "Database_name");*/
$conn1 = new mysqli("localhost","root","redhat","test");

/* Defining the Query to be executed */
/* We want to list the first_name and the last_name entries from the table people */
$query1 = "select first_name,last_name from people";

/* Now executing the query and storing the result */
$result1 = $conn1->query($query1);

/* Printing the output */
while($obj1 = $result1->fetch_object())
        {
        printf("%s %s\n",$obj1->first_name, $obj1->last_name);
        }
?>
[root@server ~]#

Now testing the script as follows.
[root@server ~]# php -q test.php
Randeep Raman 1234
Nibul Roshan  5678
Afilaj Hussain 1357
Renjith menon
[root@server ~]#

It woorks :)