Last modified on 31 January 2010, at 17:57

PHP Programming/php and mySQL

IntroductionEdit

Note: You should know SQL to use mySQL. You can learn that in the SQL book.

PHP integrates well with mySQL, and contains a library full of useful functions to help you use mySQL. There are even many database managers written in PHP.

mySQL is not a part of the server that runs PHP, it is a different server. mySQL is one of many database servers, it is open source and you can get it here.

As of PHP5, mySQL integration is not enabled by default and you should add it manually, see here for installation instructions, PHP4 has it enabled by default.

Let's get started!

Connecting to a mySQL serverEdit

To connect with a mySQL server, you should use the mysql_connect() function. It is used in the following manner:

mysql_connect(servername, username, password);

servername - The name or address of the server. Usually 'localhost'. username, password - The username and password used to login to the server.

Multiple mySQL connectionsEdit

Though not commonly used, you can connect to more than one database server in one script. On a successful connection, mysql_connect() returns a reference to the server, which you can capture with a variable:

$con = mysql_connect("localhost", "root", "123");
$con2 = mysql_connect("http://www.example.com/", "root", "123");

Selecting your databaseEdit

In order to perform most actions(except for creating, dropping and listing databases, of course), you must select a database. To do so, use mysql_select_db().

mysql_select_db(db_name);

Where db_name is the database name.

By default, mysql_select_db() will try to select the database on the last mySQL connection opened. So in the following code, mysql_select_db() will try to select the database on the "example.com" server.

$con = mysql_connect("localhost", "root", "123");
$con2 = mysql_connect("example.com:3306", "root", "123");
mysql_select_db("database1");

The function takes a second, optional, parameter that you can use to select a different database then the one last opened:

$con = mysql_connect("localhost", "root", "123");
$con2 = mysql_connect("example.com:3306", "root", "123");
mysql_select_db("database1", $con);

Executing a queryEdit

To execute a query, use mysql_query(). For example:

mysql_query("UPDATE table1 SET column1='value1', column2='value2' WHERE column3='value3'");

By default, mysql_query() will use the last mySQL connection opened, if you want to use a specific connection, send it to the function as a second parameter:

mysql_query("UPDATE table1 SET column1='value1', column2='value2' WHERE column3='value3'", $con);

Important: mysql_query() returns a resource link which you will need for certain operations. So you should capture it by storing the result in a variable:

$query1 = mysql_query("UPDATE table1 SET column1='value1', column2='value2' WHERE column3='value3'", $con);

Functions for SELECT queriesEdit

Executing a SELECT query is all good and well, but just sometimes, we may want the result (people are strange like that). The PHP developers are those strange people, and they added to PHP a few functions to help up with that:

mysql_fetch_row()Edit

Returns the next row in the result. It is returned as an array, so you should capture it in a variable.

For example:

$query1 = mysql_query("SELECT id, name, address FROM phone_book");

$person = mysql_fetch_row($query1);

print_r($person);

This should output something like this:

Array
{
    [0] => 1
    [1] => Sharon
    [2] => Helm, 3
}

This function will always return the next row in the result, until eventually it runs out of rows and it returns false. A very common use of this function is with a while loop, for example:

$query1 = mysql_query("SELECT id, name, address FROM phone_book");

while($person = mysql_fetch_row($query1))
{ 
    print_r($person);
    echo "\n";
}

This should output something like this:

Array
{
    [0] => 1
    [1] => Sharon
    [2] => Helm, 3
}
Array
{
    [0] => 2
    [1] => Adam
    [2] => 23rd street, 5
}
Array
{
    [0] => 3
    [1] => Jane
    [2] => Unknown
}

mysql_fetch_array()Edit

This one does exactly what mysql_fetch_row() does, except for the fact it returns an associative array.

$query1 = mysql_query("SELECT id, name, address FROM phone_book");

$person = mysql_fetch_array($query1);

print_r($person);

Should output something like this:

Array
{
    [id] => 1
    [name] => Sharon
    [address] => Helm, 3
}

mysql_num_rows()Edit

Sometimes we want to know how many rows we get in the result of a query. This can be done by something like this:

$counter = 0;
$query1 = mysql_query("SELECT id, name, address FROM phone_book");

while(mysql_fetch_row($query1))
{
    $counter++;
}

$counter now stores the amount of rows we got from the query, but PHP has a special function to handle this:

$query1 = mysql_query("SELECT id, name, address FROM phone_book"); 
$counter = mysql_num_rows($query1);

$counter stores the same value, but wasn't that easier?

Functions for other queriesEdit

The following functions are not just for SELECT queries, but for many types of queries. Those queries can be useful in many cases.

mysql_info()Edit

Will return information about the last query executed, or about the query you send it a resource of:

mysql_info();    //For the last query executed
mysql_info($query); //For $query, what ever that is...

The information is returned as string, and though it's templated, it's not normally to be analyzed by the script, but to be used in output.

mysql_affected_rows()Edit

Returns the number of rows affected by a query, only works with INSERT, UPDATE or DELETE queries:

mysql_affected_rows();       //For the last query executed
mysql_affected_rows($query); //For $query, what ever that is

mysql_insert_id()Edit

Returns the id mysql assigned to the auto_increment column of the table after an INSERT query.

$result = mysql_query("INSERT 'Bob' INTO names(firstname)");
$new_id = mysql_insert_id();

Note: You should call mysql_insert_id() straight after performing the query. If another statement is issued in between mysql_insert_id() will return NULL!

Closing a connectionEdit

You should use mysql_close() to close a mySQL connection. This would typically close the last connection opened, but, of course, you can send it a connection identifier.

mysql_close();  //Close the last connection opened
mysql_close($con); //Close connection $con