Intro to Working With Database in PHP

PHP supports a variety of Databases available. Some of the databases supported by PHP are MySQL, Oracle SQL, PostgreSQL, mongo, SQLite and more. Though there are more databases supported by PHP which are both commercial and open source, the most popular one to work with PHP is the MySQL Database. MySQL performs well with PHP and is also a good performance database compared to other commercial ones. Since MySQL is the choice of Database to work with PHP and is one of my favorite Database, we'll see on how to work on MySQL with PHP.

NOTE! We will be working with the mysqli_*() version of the PHP functions lists. mysqli_* is the improved version of mysql_* functions. It has different parameters and also differs in parameter ordering than in mysql. See more on MySQL Improved Extension

The most basic steps in working with databases are

  1. Connecting to the Database which requires the,
    • HOST in which it has to operate, if you are developing a site then in might be localhost.
    • USERNAME which has all the privileges for the Database.
    • PASSWORD if it is set in the database.
    • DATABASE if you wish to select the Database to get connected.
  2. Then we will use the connecting string to perform operations such as,
    • QUERYING
    • FETCHING

There are a list of functions that you might want to use when working with Databases. Mostly the function names start with the mysqli_*.

Connecting to MySQL Database

The function used to connect to the database is the

$con = @mysqli_connect('localhost', 'username', 'password', 'database') or 
        die('could not connect to database'.mysqli_connect_error());
        //e.g $con = @mysqli_connect('localhost', 'root', '', 'testdb')...

Now the $con string holds the connection id to the Database that is currently connected. We will be mostly using the $con string for working with other function of mysqli in php. The @ symbol before the mysqli_connect function is the Error Suppression Operator. If the connect function makes an error when we have not given the die function with the error statement, it would suppress it and won't show the error in the browser. The die function is used to show a message when the connect function fails, and the mysqli_connect_error() gives the description of the error that has occurred.

Querying the Database

The next step is to construct the query to be executed against the database.

$query = "SELECT * FROM sampleTable";

Then we will the pass this query to the mysqli_query() function, whose 1st parameter is the database connection string and the second is the query string that we have stored in $query a variable (You could also give the query string directly to the query function).

// syntax mysqli_query(DBConnection, Query);
$result = mysqli_query($con, $query);

Fetching the Result from Database

Now the $result variable holds the executed query result object which can be used to call other functions in for operations such as SELECT. To see if the query has been executed successfully we have some function that should be used in specific cases depending on the situation.

mysqli_num_rows($result) - returns the number of rows returned in the previous query.

mysqli_affected_rows($DBConnectionLink) - Gets the number of affected rows in a previous MySQL operation.

mysqli_fetch_array($result, Type) - Fetch a result row as an associative, a numeric array, or both. Type parameter has a value of MYSQLI_ASSOC - Associative Array, which has column name as the array key, MYSQLI_NUM - Numeric array with numbers as the index starting from 0. MYSQLI_BOTH for getting both in Associative and Numeric array format.

NOTE! Here assume that we have a table named sampleTable having the firstName and lastName column.

The format that i mostly use to check any type of query execution or result fetching is this.

if (mysqli_num_rows($result)) {
    while ($row = mysqi_fetch_array($result, MYSQLI_ASSOC)) {
        echo $row['firstName'] . ' ' . $row['lastName'];
    } // End of result fetching while statement.
} // End of query execution checking if statement.

Now the names will be displayed in the browser till the number of rows fetched in the result ends in the while loop. You could also use the for loop of any other method. But this is a better one.