How to access Mysql database using php

It is quite easy task to access mysql database using php. The detail procedure is discussed step by step.

1)Establish Connection to Mysql:
The first thing is need to establish connection to Mysql database. With the mysql_connect() function a connection is opened to Mysql server. The syntax to use mysql_connect() function is,

$link=mysql_connect('mysql_host:port', 'mysql_user', 'mysql_password')
or die('Could not connect: ' . mysql_error());

where,
mysql_connect – is a built in function to connect to a database.

mysql_host – tells the server to connect which host. May be a localhost or remote host. You can provide IP address of the server too. The default host is localhost.

port - tells the server to connect through which port. The default port is 3306.

mysql_user – this is the username for the database to connect. Default value is defined by mysql.default_user. In SQL safe mode, this parameter is ignored and the name of the user that owns the server process is used.

mysql_password – This is the mysql user password. Default value is defined by mysql.default_password. In SQL safe mode, this parameter is ignored and empty password is used.

Or die – It will return an error if we cannot connect to the database.

mysql_error - It is another mysql built in function which will return the text of the error message from previous MySQL operation. So if mysql_connect is unsuccessful mysql_error will be populated with error messages.

2)Select mysql database:
The second step is to select a database. As in the mysql server there may be many databases but we need to specify on which database we will do operation. With mysql_select_db built in function a mysql database is selected.

The usage syntax is,
mysql_select_db('my_database') or die('Could not select database');
where,
my_database - is the name of the mysql database to use.

or die - If it can't select the database it's portion will be executed.

3)Perform SQL query to access database.
Now that we have a connection to the database, we will need to build our query. We will be selecting user, password field from user table of mysql database.

So our query will look like,
$query = 'SELECT USER, PASSWORD FROM user';

Note that the query string should not end with a semicolon.

Now in order to send this query to mysql database and store the result into variable $result issue,
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

4)Display results in the browser.
We have the results of the query inside variable $result. So we need to display it into browser.

With built in function mysql_fetch_array() we can fetch a result row as an array and moves the internal data pointer ahead. We can use the following code to display result as html.

echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   echo "\t<tr>\n";
   foreach ($line as $col_value) {
       echo "\t\t<td>$col_value</td>\n";
   }
   echo "\t</tr>\n";
}
echo "</table>\n";

5)Free result memory:
Our task is now completed and hence we need to free up memory that is occupied by $result. By calling mysql_free_result() function we can free all memory associated with the $result. The syntax of calling the function is,

mysql_free_result($result);

6)Close Mysql database connection:
The mysql_close() closes the non-persistent connection to the MySQL server that's associated with the specified link identifier.
The syntax of closing database connection is,
mysql_close($link);

Here is our final code.

<?php

#Mysql database username is root and password is test

$link=mysql_connect('localhost', 'root', 'test')
or die('Could not connect: ' . mysql_error());

#We are selecting mysql database itself for query.
mysql_select_db('mysql') or die('Could not select database');

#A query is written to select username and password from table user.
$query = 'SELECT USER, PASSWORD FROM user';

#Execute query and store result into variable $result.
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

#Html table to display result well.
echo "<table style=\"margin-bottom: 1px;\" border=\"0\" cellpadding=\"0\" cellspacing=\"1\" width=\"100%\">\n";

#While loop to fetch row one by one.
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   echo "\t<tr>\n";
   foreach ($line as $col_value) {
       echo "\t\t<td bgcolor=\"#dadce1\">$col_value</td>\n";
   }
   echo "\t</tr>\n";
}
echo "</table>\n";

#Free up Memory for $result.
mysql_free_result($result);

#Close database connection.
mysql_close($link);
?>

And output displayed on the screen is,

root	*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29
root
pma
magento	*38EFCD3985CA39B52BDBC1C002C4DF9CB86BB039
magento	*38EFCD3985CA39B52BDBC1C002C4DF9CB86BB039
momin	*7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E
robert	*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29
richard	*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29
rober4t	*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29

Related Documents
http://arjudba.blogspot.com/2009/05/creating-and-using-database-in-mysql.html
http://arjudba.blogspot.com/2009/05/connecting-to-and-disconnecting-from.html
http://arjudba.blogspot.com/2009/05/create-user-in-mysql.html