PHP & MySQL Tutorial MySQL with PHP: Part I - User and Password

bogotobogo.com site search:

MySQL with PHP I

Setting UP Users and Privileges

A MySQL system can have many users. The root should be used for administration purposes only for security reasons.

One of the best features of MySQL is that it supports a sophisticated privilege system. A privilege is the right to perform a particular action on a particular object and is associated with a particular user. The concept of privilege is similar to file permission. When we create a user within MySQL, we grant the user a set of privileges to specify what the user can and cannot do within the system.

The GRANT and REVOKE commands enable you to give rights to and take them from MySQL users at these four levels of privilege:

Global
Database
Table
Column

We can add a user by entering a grant statement into MySQL monitor as the root user.

This will create a new user with root status. The new user bogo was created in the localhost domain with a password bogopass.

Privileges for Users

Privilege	Column	Context
CREATE	Create_priv	databases, tables, or indexes
DROP	Drop_priv	databases, tables, or views
GRANT OPTION	Grant_priv	databases, tables, or stored routines
REFERENCES	References_priv	databases or tables
ALTER	Alter_priv	tables
DELETE	Delete_priv	tables
Index	Index_priv	tables
INSERT	Insert_priv	tables or columns
SELECT	Select_priv	tables or columns
UPDATE	Update_priv	tables or columns
CREATE TEMPORARY TABLES	Create_tmp_table_priv	tables
LOCK TABLES	Lock_tables_priv	tables
CREATE VIEW	Create_view_priv	views
SHOW VIEW	Show_view_priv	views
ALTER ROUTINE	Alter_routine_priv	stored routines
CREATE ROUTINE	Create_routine_priv	stored routines
EXECUTE	Execute_priv	stored routines
FILE	File_priv	file access on server host
CREATE USER	Create_user_priv	server administration
PROCESS	Process_priv	server administration
RELOAD	Reload_priv	server administration
REPLICATION CLIENT	Repl_client_priv	server administration
REPLICATION SLAVE	Repl_slave_priv	server administration
SHOW DATABASES	Show_db_priv	server administration
SHUTDOWN	Shutdown_priv	server administration
SUPER	Super_priv	server administration
ALL[PRIVILEGES]	-	server administration
USAGE	-	server administration

Connection to MySQL

To connect to MySQL, we use mysql_connect() function. This function requires three arguments: domain name, user name, and password. It returns true at successful connection. The example below shows how to write a confirmation after the successful connection.

#connection.php

<?php 	$user="bogo"; 
	$connect = mysql_connect("localhost", $user,"bogopass");
	if($connect) {
		$message = 
		"Congratulations! <b>\"$user\"</b><br /> 
		 You are now connected to MySQL";
	}	
?>
<html>
<head> <title>Connecting a user to MySQL</title> </head>
<body>
	<p><?php echo $message; ?></p>
</body>
</html>

Because the user details are case-sensitive, changing the value of the $user variable to BOGO means that the connection attempt will fail and PHP will give us a warning message written in the Apache err.log file located in Apache's logs directory. It shows detailed information about the failure.

Here is the part of err.log file:
[Sun Sep 26 12:48:37 2010] [error] [client 127.0.0.1] PHP Warning: mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: Access denied for user 'BOGO'@'localhost' (using password: YES) in C:\\server\\www\\apacheserver.dev\\public_html\\connecting.php on line 2.

Listing Databases

We used show databases; command to list SQL databases in the previous chapter. The equivalent of the command in PHP requires three functions:

mysql_list_dbs()
This function returns a result set of information about all databases, which can be assigned to a variable.
mysql_num_rows()
The details of each database are held in separate rows inside the result set, so the total number of databases can be determined using mysql_num_rows() function to discover how many rows are in the result set. This function takes the result set as its argument.
mysql_tablename()
The name of each database can be extracted from a result set by this mysql_tablename() function. This function is also used to extract the names of each table from a particular database. A result set and row number are required argument to get the database names.

#listing_databases.php

<?php 	$user="bogo"; 
	$connect = mysql_connect("localhost", $user,"bogopass");
	$database_list = null;
	if($connect) {
		$rs = mysql_list_dbs($connect);
		for ($row = 0; $row < mysql_num_rows($rs); $row++) {
			$database_list .= mysql_tablename($rs, $row) . "<br /> ";
		}
	}	
?>
<html>
<head> <title>Listing Databases</title> </head>
<body>
	<p><b>Listing Databases</b></p>
	<p><?php echo $database_list; ?></p>
</body>
</html>

Listing Tables

Listing Tables is similar to listing databases. We use mysql_query() function which returns a result set of information about all tables in a database.

The following example displays the names of three tables which have been added to the database test.

But before we do anything, we should make tables for test database.

<?php
$username="bogo"; 	
$password="bogopass";
$dbname = 'test';

if (!mysql_connect('localhost', $username, $password)) {
    echo 'Could not connect to mysql';
    exit;
}

$sql = "SHOW TABLES FROM $dbname";
$result = mysql_query($sql);

if (!$result) {
    echo "DB Error, could not list tables\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

echo "Database: <b>$dbname</b> <br /><hr>";
while ($row = mysql_fetch_row($result)) {
    echo " $row[0] <br />";
}

mysql_free_result($result);
?>