PHP Programming/PHP and MySQL

Introduction

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, MySQLi integration is not enabled by default and you should add it manually, see here for installation instructions, PHP4 has it enabled by default.

Note: mysql_*() functions are deprecated in PHP 5.6+ and removed in PHP7+ . Use PDO or mysqli instead.

Let's get started!

Connecting to a MySQL server

To connect with a MySQL server, you should use the mysqli_connect() function or mysqli() class. It is used in the following manner:

mysqli_connect(servername, username, password,database);

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

database - The database name you want to select. It's optional.

Multiple MySQL connections

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

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

Selecting your database

In order to perform most actions(except for creating, dropping and listing databases, of course), you must select a database. To do so, use mysqli_select_db() only if you didn't defined it in mysqli_connect().

mysqli_select_db(db_name);

Where db_name is the database name.

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

$con = mysqli_connect("localhost", "root", "123");
$con2 = mysqli_connect("example.com:3306", "root", "123");
mysqli_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 = mysqli_connect("localhost", "root", "123");
$con2 = mysqli_connect("example.com:3306", "root", "123");
mysqli_select_db("database1", $con);

Executing a query

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

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

Important: mysqli_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 = mysqli_query($con,"UPDATE table1 SET column1='value1', column2='value2' WHERE column3='value3'");

Functions for SELECT queries

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:

mysqli_fetch_row()

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

For example:

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

$person = mysqli_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 = mysqli_query($con,"SELECT id, name, address FROM phone_book");

while($person = mysqli_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
}

mysqli_fetch_array()

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

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

$person = mysqli_fetch_array($con,$query1);

print_r($person);

Should output something like this:

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

mysqli_num_rows()

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 = mysqli_query($con,"SELECT id, name, address FROM phone_book");

while(mysqli_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 = mysqli_query($con,"SELECT id, name, address FROM phone_book"); 
$counter = mysqli_num_rows($query1);

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

Functions for other queries

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

mysqli_info()

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

mysqli_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.

mysqli_affected_rows()

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

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

mysqli_insert_id()

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

$result = mysqli_query($con,"INSERT 'Bob' INTO names(firstname)");
$new_id = mysqli_insert_id();

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

Closing a connection

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

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