Counting Returned Records


The next logical function to discuss is mysql_num_rows(). This function returns the number of rows retrieved by a SELECT query, taking the query result as a parameter.

 $num = mysql_num_rows($result); 

I'll use this function in two different ways. First, I'll modify view_users.php to list the total number of registered users. Second, I'll modify register.php to test if an email address has already been taken before letting a user register with it.

To modify view_users.php

1.

Open view_users.php (refer to Script 7.4) in your text editor.

2.

Before the if ($result) conditional, add this line (Script 7.6)

 $num = mysql_num_rows ($result); 

Script 7.6. Now the view_users.php script will display the total number of registered users.


This line will assign the number of returned rows to the $num variable.

3.

Change the original $result conditional to

 if ($num > 0) { 

The conditional as it was written before was based upon whether the query did or did not work, not whether or not any records were returned. Now it will be more accurate.

4.

Print out the number of registered users.

 echo "<p>There are currently $num   registered users.</p>\n; 

5.

Change the else part of the conditional to read

 echo '<p >There are   currently no registered users.   </p>'; 

The original conditional was based upon whether or not the query worked. Hopefully you've successfully debugged the query so that it is working and the original error messages are no longer needed. Now the error message just indicates if no records were returned.

6.

Save the file as view_users.php, upload to your Web server, and test in your Web browser (Figure 7.15).

Figure 7.15. The number of registered users is now displayed at the top of the page.


To modify register.php

1.

Open register.php (refer to Script 7.5) in your text editor.

2.

Before the INSERT query (line 59), add (Script 7.7)

 $query = "SELECT user_id FROM users   WHERE email='$e"; $result = mysql_query($query); if (mysql_num_rows($result) == 0) { 

Script 7.7. The register.php script will now check if a username is taken.


This query will check if the submitted email address ($e) is currently in the database by attempting to select that record. If the number of rows returned by the result is equal to 0, it's safe to register the new user.

I don't have any debugging code in place here, as I assume the query will run without problems. (Even if the user enters a bad email address and no records are returned, that's not a MySQL error, just a usage problem.) If you have difficulties with this, remember to use the mysql_error() function and print out the query being run.

3.

After the if ($result) conditional ends (line 79 of the original script), add

 } else {   echo '<h1 >Error!     </h1>   <p >The email address     has already been registered.     </p>'; } 

This else is the conclusion of the if (mysql_num_rows($result) == 0) conditional. It reports that an email address is already taken.

4.

Save the file as register.php, upload to your Web server, and test in your Web browser (Figure 7.16).

Figure 7.16. The registration process will no longer allow a user to register an existing email address (each address must be unique in the database).


Tips

  • You can, and probably should, also guarantee unique email addresses by placing a UNIQUE index on the column in MySQL. Once you've done that, trying to insert a duplicate value will cause a MySQL error (which is why you'll still want a PHP check like the one added to register.php).

  • Every registration/login system requires one unique column, be it an email address or a username. During the login process, this unique value, in combination with the password, will verify the user.

  • If you haven't done so already, you should probably use the mysql client to ensure that the email addresses in the users table are unique. If you don't, you might run into problems when using the data in later chapters.




    PHP and MySQL for Dynamic Web Sites. Visual QuickPro Guide
    PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (2nd Edition)
    ISBN: 0321336577
    EAN: 2147483647
    Year: 2005
    Pages: 166
    Authors: Larry Ullman

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net