PHP & MySQL® Web Development All-in-One Desk Reference for Dummies®

5.6. Using Other Helpful mysqli Functions

Other useful mysqli functions are available for you to use in your PHP scripts. The following subsections describe how to use mysqli functions to count the number of rows returned by a query, determine the last automatically made entry, count rows affected by a query, and escape characters.

5.6.1. Counting the number of rows returned by a query

Often, you want to know how many rows your SQL query returned. Your query specifies criteria that the information must meet to be returned, such as state must equal TX or lastName must equal Smith. The function mysqli_num_rows tells you how many rows were found that meet the criteria.

Login pages frequently use this function. When a user attempts to log in, he or she types an account and a password into an HTML form. Your PHP script then checks for the account and password in a database. If it is found, the user name and password are valid. You might use code similar to the following:

$query = "SELECT * FROM ValidUser
          WHERE acct = '$_POST[userID]
          AND password = '$password'";

$result = mysqli_query($cxn,$query);
$n = $mysql_num_rows($result);
if($n < 1)
{
   echo "User name and password are not valid";
   exit();
}

In this code, the SQL query looks for a row with the user ID and password provided by the user in the form. The code then tests the query result to see how many rows it contains. If the result doesn't contain any rows, that is less than one row, a user with the provided account ID and password ...