Connecting PHP to MySQL

Content of lesson:

PHP and MySQL

When creating WWW pages you might want to use large amount of structured data (for example goods in e-shop, books in library, ...). The best way to organize and administrate this data is to insert it into a database. The PHP language can communicate with many types of databases (like Oracle, MySQL, ...) but the most used one nowadays is MySQL. We are going to learn how to work with this database and how to connect to it using a PHP script.

We will need a database server with our database - use the same database as in the previous lessons. The whole database can be downloaded here: databaze.txt.

Connecting to Database

The first step to be able to communicate with a database is connecting to it. To connect to a database you should use the function MySQL_Connect. To be able to use this function properly, you have to know 3 details - these are the input parameters of this function - address of the database server, username and password.

MySQL_Connect("server", "username", "password");

The important question is what should be written as the input parameters. The server is "localhost" in our case, username is "root" and password is "" (empty string because our database does not use any password). Our function will look like the following command:

MySQL_Connect("localhost", "root", "");

In case that we use a server with public access (that means a server where our final page will be presented) these details will differ. The server "localhost" is used only if the database is located on the same computer as the website (you can use an IP address or a domain instead of "localhost"). The username "root" and empty password can be used for our local computer which cannot be accessed by anyone except us but these settings are not very clever for a public server. Anyone might get access to all data inside our database and would be able to change or delete it.

Similarly as some other functions, the function MySQL_Connect returns a value (a result). The result will be an identifier of the connection. What will happen in case the connection fails? Then the result is set to false and you can handle this situation. To be able to work with the result, you should save it to a variable. Our code will look like the following one after these adjustments:

$spojeni = MySQL_Connect("localhost", "root", "");

However, this adjustment is not the last one. We saved the result of our function inside a variable but we have to process it somehow. At first you should consider the situation when the connection fails (because of wrong address, username, password, ...). It is necessary to write an information about this error and to stop the PHP script. You can use the function die for this purpose (you order your PHP script to die). This function has one parameter - the text which will be written to browser. After calling this function the text is written and the PHP script is terminated. Adjust the PHP script to the following appearance:

$spojeni = MySQL_Connect("localhost","root","");
               
if (!$spojeni) 
{
	die('Could not connect: ' . mysql_error());
}
else
{
	echo 'Connected successfully';
}

Take a look at the following condition. At first the function MySQL_Connect tries to connect to the MySQL database and saves the result inside the variable $spojeni (as described before). Then this value is checked by the condition if. In case the variable is false, the PHP script is terminated immediately (using the die function). In case of any error we use the function mysql_error() which displays details of the error. You can use this function for the following SQL commands too.

In case the $spojeni contains the identifier of connection, the else part is executed. In our case only the text "Connected successfully" is written to browser.

Setting Character Set

Before working with data you have to set the character set for communication with the database (according to the character set used in our database). Our database uses the character set UTF-8 (you can learn more information about character sets in lessons Text Encoding 1 and Text Encoding 2). To set the character set you can use the function mysql_query (which is used to run a SQL command) using the following syntax:

mysql_query("SET CHARACTER SET utf8");

This function sets the character set to utf8. In case you want to use another encoding, you should change utf8 to one of those values:

• utf8
• cp1250
• latin1
• latin2
• ...

In case you do not write this command, you risk that a wrong encoding will be used (default encoding is usually latin1). Letters of Czech alphabet would not be displayed correctly (as in the following image).

$spojeni = MySQL_Connect("localhost","root","");
               
if (!$spojeni) 
{
	die('Could not connect: ' . mysql_error());
}
else
{
	echo 'Connected successfully';
	mysql_query("SET CHARACTER SET utf8");
}

Choosing Database

Before you can run any command you have to choose the database which will be used. To choose a database you should use the function mysql_select_db. The only parameter is the name of database. You can use a similar notation:

mysql_select_db("jmeno");

You should add this command inside the previous script:

$spojeni = MySQL_Connect("localhost","root","");
               
if (!$spojeni) 
{
	die('Could not connect: ' . mysql_error());
}
else
{
	echo 'Connected successfully';
	mysql_query("SET CHARACTER SET utf8");
	mysql_select_db("renome");
	commands for tables can be placed here
}

Additional Texts

Questions

1. What is the meaning of the function MySQL_Connect? Which parameters does it use?
2. What is the meaning of the command SET CHARACTER SET and why is it necessary to use it when connecting to database?