PHP MySQL Basics Tutorial

May 28th, 20060 Comments ยป

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:

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.

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.

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:

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:

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:

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.
« Common PHP session errors
PHP MySQL Login Script »

Categorized Under

PHP & MySQL PHP Basics

About jatinder

» has written 17 posts

No Comments


*

Popular Posts