Skip to main content

PHP MySQL Basics Tutorial

PHP and MySql are the most common and popular combination you would come across on the Internet. Affordable Linux hosting, open source nature of these two technologies and the freedom from expensive proprietary licenses are the main reasons for the success of this combination.

Connecting to MySQL through PHP

To connect to a mysql database server, use the following function:

$link = mysql_connect($hostname, $username, $password, $newlink);

A call to mysql_connect returns a link identifier on success or FALSE on failure. Lets go through the arguments one at a time:

$hostname = The domain name of the mysql server. If both the web server and mysql are located on the same machine/computer, you can simply use “localhost”.
$username = Login ID/username of the mysql server
$password = Password for the mysql server

The fourth argument ($newlink) is not used very often and you can skip it in most cases. mysql_connect either opens a new connection to the mysql server or uses an existing connection. Calling mysql_connect multiple times will return the same connection or link identifier which was created by the previous call.

$link1 = mysql_connect("localhost", "user1", "secret");
$link2 = mysql_connect("localhost", "user1", "secret");
$link3 = mysql_connect("localhost", "user1", "secret");
//The three calls to mysql_connect above will return a link identifier to the same connection
$link4 = mysql_connect("localhost", "user2", "secret"); //A new connection will be returned since we have changed the username in the arguments

In the above example, the second and third calls to mysql_connect will return the same connection which was created in the first call. No new connections will be established. This is as long as the same arguments are used in all the three calls. You can force the mysql_connect to create a new connection by setting the value of $newlink argument as TRUE.

Selecting Database

Now that we have a link identifier or connection to the mysql server, we need to “select” a database. A mysql server on a typical shared hosting server may have dozens or even hundreds of databases. So we need to select or specify a database against which we want to run our queries.

//Open a connection to the mysql server
$link = mysql_connect('localhost', 'user', 'secret');
if(!$link) { print('Failed to establish connection to mysql server!'); exit(); } //Select the database $status = mysql_select_db('mydatabase');

Running Queries

We have connected to the database server and selected our database. Now we are ready to run queries using mysql_query function. See the example below:

//Open a connection to the mysql server
$link = mysql_connect("localhost", "user1", "secret");
if(!$link) { 
	print("Failed to establish connection to mysql server!");

//Select the database
$status = mysql_select_db("mydatabase");

//Run query
$query = "SELECT first_name,last_name FROM customers WHERE cust_id=23";
$rs = mysql_query($query);
if(!$rs) {
	print("Query Error: ".mysql_error());
$numrows = mysql_num_rows($rs);
print("Number of rows returned: $numrows");

mysql_query will return a result set on success and FALSE on error. This is true for “SELECT”, “SHOW”, “DESCRIBE”, and “EXPLAIN” queries. For other types of queries, it will return TRUE on success and FALSE on error.

Therefore in our example above, we will either get a result set or FALSE. Even if there is no customer with a cust_id of 23, we will still get a result set. You can check the number of rows returned by our query using mysql_num_rows function. Please note, mysql_num_rows is only meaningful for SELECT queries.

Using the result set

There are a number functions available which we can use to retrieve our values from the result set. If we know for sure that only a single row will be returned by the query, we can use the code below:

$query = "SELECT first_name,last_name FROM customers WHERE cust_id=23";
$rs = mysql_query($query);
if(!$rs) {
	print("Query Error: ".mysql_error());

//Number of rows reqturned by the query
$numrows = mysql_num_rows($rs);
print("Number of rows returned: $numrows");

//Fetch result set as an associative array 
$customer = mysql_fetch_assoc($rs);

mysql_fetch_assoc returns an associative array. The column names correspond to the keys of this array and column values correspond to array values. In our example above, mysql_fetch_assoc will return an array similar to:

$customer['first_name'] = 'Jatinder';
$customer['last_name'] = 'Thind';

Other functions of interest while dealing with mysql databases are :

  1. mysql_unbuffered_query – This uses less memory than mysql_query. The downside is that you can not use mysql_num_rows if you use mysql_unbuffered_query
  2. mysql_real_escape_string – Escapes the special characters. Only available on PHP-4.3.0 and above
  3. addslashes – Similar to mysql_real_escape_string above. This is available in all PHP versions.
  4. mysql_error – Returns the error text from the previously called mysql function.

2 thoughts to “PHP MySQL Basics Tutorial”

  1. what if my error is Warning: mysql_connect() [function.mysql-connect]: Access denied for user ‘jatinder’@’localhost’ (using password: YES) in C:\xampp\htdocs\xampp\PHP-Login\register-exec.php on line 15
    Failed to connect to server: Access denied for user ‘jatinder’@’localhost’ (using password: YES) this please help me

    1. You need to use your own database login credentials. The username/password details given in the tutorial are simply for example.

Leave a Reply

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


This site uses Akismet to reduce spam. Learn how your comment data is processed.