How to connect to a MySQL database with PHP (Using MySQLi)

(FYI: this example assumes you know some basic SQL)

There are three main ways that you connect to a MySQL database using PHP: the original MySQL extension (function based), MySQL Improved (MySQLi – Object-oriented) and PHP Data Objects (PDO, Object-oriented). This little piece will be about using MySQLi to insert, update and get data from a MySQL database.

Connecting to the database

To connect to a database, we have to create a “link” that we will pass all of our queries through later on. So, to start all we need is:

The $connection variable will become our “link” to the database from now on. We’ll use it to pass through all of our queries. Within the mysqli_connect function, you’ll have to replace the appropriate string with the MySQL host, the database user’s username and password and the database you’ll be accessing.

I’ve also added   or die(“Error: ” . mysqli_error($connection));   which is a quick and efficient way to kill the connection and echo out the error to us if something goes wrong. (This uses “short-circuit evaluation” so that if the mysqli_connect() function returns true, then the die() function isn’t executed **but **if mysqli_connect() returns false then PHP will try to evaluate the die() function to find out if it’s true or false, thus executing it in the process)

SELECTing data from a database

To extract information from our database, we have to pass a query through the link we made earlier. We start by creating the query and using _mysqli_query() _to execute it.

This _SELECT_s all the data in the table ‘table‘ and places the resulting object in the _$result _variable. What we receive from the _mysqli_query() _function is not, in itself, the result we want from a database so we’ll need to go one step extract what we need.

This executes a while loop that executes for every row of the result we got from the database. So for every row, we fetch the data with _mysqli_fetch_assoc() _from _$result _and give it to the variable _$row. _So each $row will be the appropriate row of our result. _mysqli_fetch_assoc() _puts the data into $row as an associative array so you can call the values by their column titles.

When we’re finished with our data, we run _mysqli_free_result( $result ) _to free the result from memory just to make sure everything runs smoothly.

INSERTing, UPDATEing and DELETEing with MySQLi

Using INSERT, UPDATE and DELETE are pretty much the same process as above except we don’t need to receive the result, we just have to execute the function. So below I’ve given a few examples:




Finishing Up

When everything’s been executed and our little variables are tucked into bed, with a story red and some warm milk consumed, we have to run one more function to quietly slip out the door and leave them to their slumber.