PHP - My SQL (Chapter - 9: PHP Database Functions)

Introduction MYSQL, Connection, Queries, Handling Query Results

1. Introduction to Built-In MYSQL Functions


If you are thinking "What is a database?" or "Why MYSQL?", head on over to our MYSQL Tutorial. There you will not only learn the answer to these questions, you will also learn the basic commands needed to use MYSQL.

Once you are up to speed you can begin to learn how to integrate MYSQL commands directly into your PHP code.

PHP's MYSQLI class has over 60 built-in functions to meet your MYSQL interfacing needs. Just about anything that you ever wanted to do (and a few that you didn't) can be done with one function or another, but we will, in the next few pages, only concentrate on the fourteen functions that are most suited to our needs:


Function Description
mysqli_affected_rows() Returns the number of affected rows in the previous MySQL operation
mysqli_close() Closes a previously opened database connection
mysqli_connect() Opens a new connection to the MySQL server
mysqli_errno() Returns the last error code for the most recent function call
mysqli_error() Returns the last error description for the most recent function call
mysqli_fetch_all() Fetches all result rows as an associative array, a numeric array, or both
mysqli_fetch_array() Fetches a result row as an associative, a numeric array, or both
mysqli_fetch_assoc() Fetches a result row as an associative array
mysqli_fetch_row() Fetches one row from a result-set and returns it as an enumerated array
mysqli_free_result() Frees the memory associated with a result
mysqli_num_rows() Returns the number of rows in a result set
mysqli_query() Performs a query against the database
mysqli_real_escape_string() Escapes special characters in a string for use in an SQL statement
mysqli_select_db() Changes the default database for the connection

2. MYSQL Connect & Close


The #1 most important step of integrating MYSQL into your PHP script is connecting to the database. And while it is not strictly necessary to close the connection, it is always good practice to tie up any loose ends. Here we will learn how to do both.

The mysqli_connect() function is used to connect. It requires four parameters, in the following order: mysqli_connect(servername, username, password, databasename)

The value of "servername" will specify what server you need to connect to. Since the database is usually on the same server as the script/connection, the default value is "localhost".

The username and password should be self-explanatory. Your web host probably provided them already. The database name is whatever you named your database.

<?php
     $con = mysqli_connect("localhost","my_username","my_secret_password", "database_name");
     if (!$con) { die('Could Not Connect: ' . mysqli_error($con) . mysqli_errno($con)); }
        // Do Database Stuff Here
    mysqli_close($con);
?>

See what we did there? We have connected to our database and stored the connection details/handle in the $con variable for later reference. Then we tested our connection using the handle and told the script to stop working if the connection was faulty. And last but not least, we used mysqli_close() to close the open connection.

Notice the use of mysqli_error() and mysqli_errno(), two functions that help to debug MYSQL-related problems. These functions can be used separately, but used together they will tell you what problem occurred and give you the error number for that problem, enabling you to research the problem in more detail.

What could be more simple? (Don't answer that.)

Summary:


Function Description
mysqli_connect() Opens a new connection to the MySQL server
mysqli_close() Closes a previously opened database connection
mysqli_errno() Returns the last error code for the most recent function call
mysqli_error() Returns the last error description for the most recent function call

3. Running MYSQL Queries In PHP


The mysql_query() function is a "catch all" that can run about any MYSQL query that you give it. Let's look into the execution of some standard insert, select, update and delete statements.


<?php
     $con = mysqli_connect("localhost","my_username","my_secret_password", "database_name");
    if (!$con) { die('Could Not Connect: ' . mysql_error($con) . mysql_errno($con)); }

    $insert = mysqli_query($con, "INSERT INTO table_name (col1, col2) VALUES('Value 1', 'Value 2' );");
    if (!$insert) { die (mysql_error($con)); }

    $select = mysqli_query($con, "SELECT * FROM table_name;");
    if (!$select) { die (mysql_error($con)); }

    $update = mysqli_query($con, "UPDATE table_name SET col2 = 'Value' WHERE col2 LIKE 'Value 2';");
    if (!$update) { die (mysql_error($con)); }

    $delete = mysqli_query($con, "DELETE FROM table_name WHERE col2 LIKE 'Value';");
    if (!$delete) { die (mysql_error($con)); }

    mysqli_close($con);
?>>

As you can see, each time mysqli_query() is used, it can be assigned a handle that we can later use to identify the results of the statement. Also, the function is (optionally) given the opportunity to print out an error message and die if errors occurred during execution of the statement.

Summary:


Function Description
mysqli_query() Performs a query against the database

4. Handling MYSQL Query Results In PHP


Now, how do we handle the results of the MYSQL statements executed? Decisions, decisions... That all depends on what you want to do.

The mysqli_num_rows() function, when handed the result of an executed SELECT or SHOW statement, will return the number of rows that will be returned.

The mysqli_affected_rows() function, when handed the result of a recently executed statement, will return how many rows were affected by the execution of the statement.

<?php      $con = mysqli_connect("localhost","my_username","my_secret_password", "database_name");
     $select = mysqli_query($con, "SELECT * FROM table_name;");
    echo mysqli_num_rows($select) . ' rows were selected.';

    $update = mysqli_query($con, "UPDATE table_name SET col2 = 'Value' WHERE col2 LIKE 'Value 2';");
    echo mysqli_affected_rows($update) . ' rows were updated.';

    mysqli_close($con);
?>

But perhaps the most useful functions are those that can help you identify and read selected data. The two most common functions for this purpose are mysqli_fetch_array() and mysqli_fetch_assoc().

Using a while loop in combination with either of these two functions, we can come up with a simple solution to process multiple results to an executed query.


<?php
     $con = mysqli_connect("localhost","my_username","my_secret_password", "database_name");
    $result = mysql_query($con, "SELECT * FROM table_name;");

    while ($list = mysqli_fetch_assoc($result)) {
        echo 'Label 1: ' . $list['column_name_1'] . '<br>';
        echo 'Label 2: ' . $list['column_name_2'] . '<br><br>';
    }

    mysqli_close($con);
?>

We can read it like this: While there is another row of results being returned from the executed query, assign it to the $list array. Then, since we used the mysqli_fetch_assoc() function, the array will be 'associated' with the name of each column of the database. We use those database column names to identify the data that we want to display/format/use.

Substituting the mysqli_fetch_array() function is different only in the sense that you do not need to memorize the column/field names in your database... You only need to memorize what order they are in, so that they can be identified by number (beginning with '0').


<?php
     $con = mysqli_connect("localhost","my_username","my_secret_password", "database_name");
    $result = mysql_query($con, "SELECT * FROM table_name;");

    while ($list = mysqli_fetch_array($result)) {
        echo 'Label 1: ' . $list[0] . '<br>';
        echo 'Label 2: ' . $list[1] . '<br><br>';
    }

    mysqli_close($con);
?>

One option is as good as the other, so you choose which you prefer. Just keep in mind that when referring back to your code later on, the field names might make more sense than random numbers.

Summary:


Function Description
mysqli_affected_rows() Returns the number of affected rows in the previous MySQL operation
mysqli_fetch_all() Fetches all result rows as an associative array, a numeric array, or both
mysqli_fetch_array() Fetches a result row as an associative, a numeric array, or both
mysqli_fetch_assoc() Fetches a result row as an associative array
mysqli_num_rows() Returns the number of rows in a result set

5. MYSQL Security & Handling User Input


Last, but not least (in fact this might be the most important step of all), I would like to caution you about handling user input, or allowing random people to put random data into your database queries.

Why might you do that? Every time you use a website's search feature to enter in your search criteria, or enter your username and password into a login form, you are introducing 'unknown' data that they will run through their database(s), searching for matches. And those are only a few examples, because the possibilities are endless.

How can this data be trusted? Not only might strange characters appear, but your website's security might also be put at risk.

The term 'SQL injection attack' refers to instances when characters and/or code is entered into a MYSQL query (usually by being entered into a form field that is dumped directly into a MYSQL query) with the intent to hack into a website or server. PHP provides a function that can be used as a preventative measure.

The mysqli_real_escape_string() function should always be used when entering data into a MYSQL query. The basic method (although there are variations) is:


<?php
     $query = "SELECT * FROM pets WHERE name LIKE '" . mysqli_real_escape_string($con, $name) . "';";

     $query = "SELECT * FROM pets WHERE name LIKE '" . mysqli_real_escape_string($con, $_POST['name']) . "';";
?>

This allows the function to escape any characters that may cause the query to be used maliciously.

Summary:


Function Description
mysqli_real_escape_string() Escapes special characters in a string for use in an SQL statement

Continue to Index

Welcome to HoneyBee - The Learning Platform

By Binit Patel

HoneyBee is a learning platform which is an integrated set of informative online services that enable learners involved in education with information, tools and resources to support and enhance Teaching, Learning and Management.

Contact With Me