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