Basic PHP & MySQL Part 1

by Rajesh Sivaraman • February 3, 2012 • Linux, MySQL, PHP • 0 Comments

PHP and MySQL are two unique open source projects. While PHP is an excellent programming language, MySQL is the Database choice of millions of softwares, websites and companies out there.

In this series, we will look at how to use PHP to connect and manipulate data stored inside MySQL. In the first part, I am going to demonstrate how to connect to a MySQL database and read data from a table and display the results.

For this we need to first install Apache, MySQL and PHP. We will also create a database and a table in MySQL. If you haven’t done this earlier, follow the below shell commands.

sudo apt-get update
sudo apt-get install apache2 php5 php5-mysql php5-cgi mysql-server
mysqladmin -u root -p create testdb

sudo apt-get update

sudo apt-get install apache2 php5 php5-mysql php5-cgi mysql-server

mysqladmin -u root -p create testdb

Remember to set a password for root user when asked. Also use the same password for the mysqladmin command. Now it is time for us to login to MySQL as root and create a table. We will also create a user in MySQL with less privileges to use that database and table. It is not wise to use root access in your applications as it would lead to lot of destruction if someone was able to crack/hack your application. We will also populate one sample entry in our table for PHP to read.

Here is what I did on my terminal.

 mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.1.58-1ubuntu1 (Ubuntu)

mysql> use testdb;
Database changed
mysql> create table example (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(50),
    -> mobile VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> create user phpuser@localhost identified by 'bigpassword';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,insert,delete,update on testdb.example to phpuser@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges ;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into example (name,mobile) values ('John','+919900502392');
Query OK, 1 row affected (0.00 sec)

mysql> \q

mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 47

Server version: 5.1.58-1ubuntu1 (Ubuntu)

mysql> use testdb;

Database changed

mysql> create table example (

-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

-> name VARCHAR(50),

-> mobile VARCHAR(20)

-> );

Query OK, 0 rows affected (0.05 sec)

mysql> create user phpuser@localhost identified by 'bigpassword';

Query OK, 0 rows affected (0.00 sec)

mysql> grant select,insert,delete,update on testdb.example to phpuser@localhost;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges ;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into example (name,mobile) values ('John','+919900502392');

Query OK, 1 row affected (0.00 sec)

mysql> \q

Ok. Now we are all set to start with our PHP code. Make sure that Apache is running by browsing to http://localhost in your computer. You should be seeing a “It Works !” page. We will also create a test.php file in the root directory of our web server and assign rights to our user so that we can edit it.

sudo apt-get update
sudo touch /var/www/test.php
sudo chown panayola:panayola /var/www/test.php

sudo apt-get update

sudo touch /var/www/test.php

sudo chown panayola:panayola /var/www/test.php

Open this file ( /var/www/test.php ) your favorite text editor and paste the following code.

<?php
//First, connect to the DB. Note that we are using the same user name and password we created
//Do not use root username and password here as it is unsafe.

$dblink=mysql_connect('localhost','phpuser','bigpassword') or die('Could not connect to Database Server');

//ok. That means we are connected. Let's select the database.
mysql_select_db('testdb') or die('Could not select DB');

//Now we can prepare a SQL query and send it to the Server
//We are using the $dblink we created using the mysql_connect() function call earlier to send the query.
$query="SELECT * from example";
$result=mysql_query($query,$dblink) or die('MySQL query Failed' . mysql_error());

//We have the result ! Let's fetch the data and display it.
//Note that the data comes as an array, so we need to split it into variables.
$row=mysql_fetch_row($result);

$id=$row['0'];
$name=$row['1'];
$mobile=$row['2'];

echo "<h1> MySQL data : ID $id , NAME $name , MOBILE $mobile  </h1>";

//Close the connection
mysql_close($dblink);
?>

<?php

//First, connect to the DB. Note that we are using the same user name and password we created

//Do not use root username and password here as it is unsafe.

$dblink=mysql_connect('localhost','phpuser','bigpassword') or die('Could not connect to Database Server');

//ok. That means we are connected. Let's select the database.

mysql_select_db('testdb') or die('Could not select DB');

//Now we can prepare a SQL query and send it to the Server

//We are using the $dblink we created using the mysql_connect() function call earlier to send the query.

$query="SELECT * from example";

$result=mysql_query($query,$dblink) or die('MySQL query Failed' . mysql_error());

//We have the result ! Let's fetch the data and display it.

//Note that the data comes as an array, so we need to split it into variables.

$row=mysql_fetch_row($result);

$id=$row['0'];

$name=$row['1'];

$mobile=$row['2'];

echo "<h1> MySQL data : ID $id , NAME $name , MOBILE $mobile </h1>";

//Close the connection

mysql_close($dblink);

Now open your browser and open the url http://localhost/test.php . You should be able to see the page below !.

This was only about reading just one row of data from the example table. In the next part, we will look at how to read multiple rows and display the results as a HTML table.

Hope you enjoyed this article. Feel free to try, share and discuss.