PHP and Mysql (Connection and Users)

When you are developing using a database, no matter if it’s a web or Window application, one of the first things that you want to do is connect to the database.

In this little tutorial we are going to focus on php & mysql connection and user privileges.

If you are a php developer you may have seen this function before: mysql_connect(“”,””,””,….)  which take  five parameters (server, username ,password ,new_link, client_flags) but we use the first three most often (“server”,” username” ,”password”) . The role of this function is to authenticate the user who is trying to connect to the given host, specified by the first parameter mysql_connect(“host name =  localhost or any”).

$con  = mysql_connect(“localhost”,”some_user”,”some_password”);
	If (!$con)
{
 // the key word die will terminate the execution of the php script.
// ” mysql_error() Returns the error text from the last MySQL function
   die(“No connection: ”. mysql_error());
}

When running the script if you get an error like the following:

No connection: Access denied for user ‘ some_user’@'localhost’ (using password: YES).

This means that we need to create a user.

A user can be created by different ways: phpmyadmin, mysql command prompt, or any other MySQL GUI but the result is going to be the same.

See example bellow

CREATE USER 'User name'@ '' IDENTIFIED BY 'password';

Explanation:

  • CREATE USER:  assign the name for the user.
  • “@ ” :  specify the name of the host or server (localhost or  any = “%” or  IP  ).
  • IDENTIFIED BY : password is assigned to the user

Assuming that you were able to connect, now we need to select a database using mysql_select_db(“db_name”,”link_identifier”). This function takes the name of the database to be used in the php script and a link.

$con  = mysql_connect(“localhost”,” user”,”some_password”);
	If (!$con)
{
 // the key word die will terminate the execution of the php script.
// ” mysql_error() Returns the error text from the last MySQL function
   die(“No connection: ”. mysql_error());
}
// Note: if the link is not specify the function is going to take the last link opened by //mysql_connect()

             $db =   mysql_select_db("db_name");
if (!$db)
{
 die ("Can not select DB : ".mysql_error());
}

If you get the following error while running the php script

Cannot select DB : Access denied for user ‘user’@'localhost’ to database ‘db_name’

This means that we need to grant privileges to the previous user.

See example:

GRANT SELECT, INSERT, UPDATE ON `db_name`.* TO 'user'@'any';

Explanation:

GRANT :  give the user the privilege of SELECT, INSERT, UPDATE

ON :  specify the database .

See more privileges at:
http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html

Hope you like it!!

This entry was posted in Mysql, PHP and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>