Login | Contact Us | Site Map | Resources

PHP Database Functions (MYSQL)

Home > PHP Tutorial > Handling MYSQL Query Results In PHP

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