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

April 11, 2014 by in category PHP, Programming tagged as , , , , , with 0 and 0
Home > Blog > PHP > 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:

$connection = mysqli_connect("Database_Host", "Username", "Password", "Database_Name") or die("Error " . mysqli_error($connection));

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.

$query = "SELECT * FROM table";
$result = mysqli_query( $connection, $query );

This SELECTs 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.

while( $row = mysqli_fetch_assoc( $result ) ){
	echo $row['id'];
	echo $row['title'];
	echo $row['column_3'];
}
mysqli_free_result( $result );

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:

INSERT:

$query = "INSERT INTO table(title) VALUES ('I Am A Title')";
mysqli_query($connection, $query);

 DELETE:

$query = "DELETE FROM table WHERE id = '4' ";
mysqli_query($connection, $query);

 UPDATE:

$query = " UPDATE table SET id='2' WHERE title='I Am A Title' ";
mysqli_query($connection, $query);

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.

mysqli_close( $connection );

 Summary:

// Make the connection
$connection = mysqli_connect("Database_Host", "Username", "Password", "Database_Name") or die("Error " . mysqli_error($connection));

// SELECT Queries
$query = "SELECT * FROM table";
$result = mysqli_query( $connection, $query );

while( $row = mysqli_fetch_assoc( $result ) ){
	echo $row['id'];
	echo $row['title'];
	echo $row['column_3'];
}
mysqli_free_result( $result );

// INSERT Queries
$query = "INSERT INTO table(title) VALUES ('I Am A Title')";
mysqli_query($connection, $query);

// DELETE queries
$query = "DELETE FROM table WHERE id = '4' ";
mysqli_query($connection, $query);

// UPDATE Queries
$query = " UPDATE table SET id='2' WHERE title='I Am A Title' ";
mysqli_query($connection, $query);

// Close The Connection
mysqli_close( $connection );
© Evan Smith 2009 - 2017