Getting Information on Database Tables

Finding out information about the database you are working is often as important as the actual data contained within it. For example, it is quite common to need to know something about the table in order to decide what action to perform next. Luckily, retrieving database table information from PHP is just as easy as retrieving data from the tables themselves.

There are several functions you can use to look at the structure of a database table:

• mysql_list_fields(): Call this function first to get information on table fields. It takes three arguments (a database name, a table name, and an optional link identifier) and returns a result pointer that refers to a list of all the fields in the specified table of the specified database. For example, if you want a pointer to list of all the fields in the user table in the sample_db database, you could use:
  
  

  $result = mysql_list_fields("sample_db", "user");
  

  The $result variable now holds a pointer to a list of fields in the user table. You'll see an example of this shortly.
  
  
• mysql_num_fields(): This takes a result pointer (as returned by the preceding function) and returns the total number of fields from the result set to which it points. If you want to get the number of fields in the user table, just pop the pointer $result that you obtained from mysql_list_fields() into the mysql_num_fields() function:
  
  

  $number_of_fields = mysql_num_fields($result);
  

  The variable $number_of_fields contains (surprise!) the number of fields in the user table.
• msyql_field_name(), mysql_field_len(), and mysql_field_type(): These functions return the field's field name, field length, and field type properties, respectively. Each of them takes both a result pointer and a field index as arguments, which specify the table and field (counting fields from zero) in that order. For example, to get the length of the username field in the user table, first note that username is the fourth field in the table and therefore has a field index of 3 (indexes start at zero), and then you could use:
  
  

  $username_length = mysql_field_len($result, 3);
  

  Because you defined username as having a length of 30 characters, $username_length should contain 30. The other two functions work much the same way.
• mysql_field_flags(): This function takes a result set pointer and a field index, and returns a string containing the attribute for the given field. Attributes of a field include NULL or NOT NULL, PRIMARY KEY, UNIQUE, and so on. You can place the attributes of the username field (from the user table) into the variable $attributes using the following code:
  
  

  $attributes = mysql_field_flags($result, 3);
  

  The only attribute of the username field is NOT NULL, so that is the value of $attributes.

Try it Out: Get Information on Fields

Let's take a look at these functions in action. The following script, metadata.php, returns the name, length, and type of each field defined in the user table:

<?php
include_once "./common_db.inc";
$link_id = db_connect();
$result = mysql_list_fields("sample_db", "user", $link_id);

for($i=0; $i < mysql_num_fields($result); $i++) {
   echo mysql_fieldname($result, $i);
   echo "(".mysql_field_len($result, $i) . ")";
   echo " - " . mysql_field_type($result, $i) . "<BR>";
}
?>

How it Works

As usual, start by including the database functions in common_db.inc, and then connect to the server using db_connect():

include_once "./common_db.inc";
$link_id = db_connect();

Then call the mysql_list_fields() function, specifying the database, table, and connection that you want to use (sample_db, user, and $link_id, respectively for this example):

$result = mysql_list_fields("sample_db", "user", $link_id);

The function returns a pointer to a result set that lists all fields in the user table. Store it in the variable $result, and use it with mysql_num_fields() to find out how many fields the table contains. This sets the upper limit of a for...next loop:

for($i=0; $i < mysql_num_fields($result); $i++) {

Using the loop index to specify which field to work with, call the functions mysql_field_name(), mysql_field_len(), and mysql_field_type(), which return the name, length, and type of the fields available:

echo mysql_field_name($result,$i);
echo "(" . mysql_field_len($result, $i) . ")";
echo" - " . mysql_field_type($result, $i) "<BR>";

Some of the field types returned are ambiguous—for example, CHAR and VARCHAR are both returned as string, whereas TEXT is returned as BLOB. To get exact field types (as they are defined in a MySQL table) involves a bit more work. That's where the mysql_field_flags() function comes in, and you can easily modify the code to show the field attributes. Here's the revised code:

$link_id = db_connect();
$result = mysql_list_fields("sample_db", "user", $link_id);

for($i=0; $i < mysql_num_fields($result); $1 ++) {
   echo mysql_field_name($result,$i);
   echo "(" . mysql_field_len($result, $i) . ")";
   echo " - " . mysql_field_type($result, $i);
   echo " " , mysql_field_flags($result, $i) . "<BR>";
}

Finally, there's a single function that can be used to get all the information on a table's fields in one go: mysql_fetch_field(). If you pass this function a result set pointer and a field index (again, counting fields from zero), it returns an object whose properties include the name of the field and the name of the table to which it belongs, along with its length, attributes, and other details. The following table describes the properties of the returned object.

Property	Description
blob	True if the field is a BLOB
max_length	The maximum length of the field
multiple_key	True if the field is a key but not unique
name	The field name
not_null	True if the field cannot be NULL
numeric	True if the field is numeric
primary_key	True if the field is a primary key
table	The name of the table to which the field belongs
type	The field type
unique_key	True if the field is a unique key
unsigned	True if the field is unsigned
def	Gives the default value if specified
zerofill	True if the field is zero-filled. A zerofill attribute is used to force a number to be a certain width by padding it with leading zeros

Try it Out: Use mysql_fetch_field()

You can use this function to produce a result similar to the previous example. Place the following code in a file called fetch_field.php:

<?php
include_once "./common_db.inc";
$link_id = db_connect();
$result = mysql_list_fields ("sample_db", "user", $link__id);

for($i=0; $i < mysql_num_fields($result); $i++) {
   $field_info_object = mysql_fetch_field($result, $i);

   echo $field_info_object->name . "(".
   $field_info_object->max_length . ")";

   echo " - " . $field_info_object->type;

   if($field_info_object->not_null) {
       echo " not_null ";
   }else{
       " null ";
   }

   if($field_info_object:->primary_key) {
       echo " primary_key ";
   }else if($field_info_object:-->multiple_key) {
       echo " key ";
   }else if($field_info_object->unique_key) {
       echo " unique ";
   }

   if($field_info_object->unsigned){
      echo " unsigned ";
   }

   if($field_info_object->zerofill) {
       echo " zero-filled ";
   }

   echo "<BR>";
}
?>

How it Works

This works much the same as the last script, but something looks amiss—the length of each field is reported as 0 bytes. Unfortunately, although it's very versatile, the mysql_fetch_field() function contains a small bug that causes its max_length property to always contain 0—no matter what size the specified field is defined with. At time of writing, the latest version of PHP is the RC1 version of PHP5—we can but hope this bug is crushed in the next release. Until then, just revert to using mysql_field_len() for this particular information:

$field_info_object = mysql_fetch_field($result, $i);

   echo $field_info_object->name . "(" .
   mysql_field_len{$result, $i) . ")";

   echo " - " . $field_info_object->type;

Now the script reports the correct size of each field.

The capability to access metadata on database tables is essential to any general-purpose database management application, which won't know anything beforehand about the table structures with which it has to work. To handle tables correctly every time, it has to rely heavily on functions such as those you've just seen.

Working with Date and Time Type Fields

Arguably, of all the MySQL data types, the trickiest to handle properly are date and time types. You need a lot of practice to work with them comfortably. Fields with these types store date and time values in the following formats:

Data Type	Data Format
DATE	2004-01-01
TIME	12:00:00
DATETIME	2004-01-01 12:00:00
TIMESTAMP	20000101120000
YEAR	2004

If you attempt to insert illegally formatted date and/or time values into fields with these data types, the relevant entry is filled with 0s. For example, inserting the string Pads into a TIME type field results in the value "00000000000000" being saved in the field:

mysql> select accessdate from access_log where userid = 'Pads' AND page ='';

+----------------+
| accessdate     |
+----------------+
| 00000000000000 |
+----------------+
1 row in set (0.00 sec)

The MySQL server provides a set of built-in date- and time-related functions that can help you to ensure that you insert correct values. You've already seen the now() function, which returns the current date and time in DATETIME format:

mysql> select now();
+-------------------------+
| now()                   |
+-------------------------+
| 2004-03-04|19:30:15     |
+----------+--------------+
1 row in set (0.00 sec)

And the curdate() and curtime() functions, which return the same as separate DATE and TIME type values:

mysql> select curdate(), curtime();
+------------+-----------+
| curdate()  | curtime() |
+------------+-----------+
| 2004-03-04 | 19:31:33  |
+------------+-----------+
1 row in set (0.00 sec)

To work with a TIMESTAMP field, you can specify either the date and time to insert in a string of 14 digits, or NULL to store the current system date and time in the same format. To properly update the access log (completing the example begun earlier), you'd say:

mysql> UPDATE access_log
    -> SET visitcount = visitcount + 1, accessdate = NULL
    -> WHERE userid = 'Dodge' AND page = '/score.html';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

The date_format() server function returns a formatted date and time value. It takes a DATE or DATETIME type argument, followed by a formatting string argument. Assuming that lastaccesstime is of the DATE or DATETIME type, you could say:

mysql> SELECT date_format('2004-03-04 22:23:00', '%M %e, %Y');
+-------------------------------------------------+
| date_format('2004-03-04 22:23:00', '%M %e, %Y') |
+-------------------------------------------------+
| March 4, 2004                                   |
+-------------------------------------------------+
1 row in set (0.03 sec)

There are more than 30 specifiers you can use to construct the formatting string. Here are some of the more common ones:

Specifier	Description
%s	Second in 2-digit numeric form (00,01...)
%i	Minute in 2-digit numeric form (00,01...)
%H	Hour in 2-digit 24-hour numeric form (00,01, ...)
%h	Hour in 2-digit 12-hour numeric form (00,01... )
%T	Time in 24-hour form (hh:mm:ss)
%r	Time in 12-hour form (hh:mm:ss AM|PM)
%W	Weekday name (Monday, Tuesday, Wednesday...)
%a	Abridged weekday name (Mon, Tue, Wed...)
%d	Day of the month in 2-digit numeric form (01,02,03...)
%e	Day of the month in numeric form (1,2,3...)
%D	Day of the mouth with an ordinal suffix (1st, 2nd, 3rd...)
%M	Name of the month (January, February...)
%b	Abridged name of the month (Jan, Feb...)
%Y	Year in 4-digit numeric form
%y	Year in 2-digit numeric form

For a complete list, please see the MySQL documentation at www.mysql.com/doc/en/Date_and_time_functions.html#IDX1335.

In the view_record() function of the user record viewer script in the last chapter, you formatted the accessdate field value with the following lines of code:

$accessdate = substr($query_data["accessdate"], 0, 4) . '-' .
  substr($query_data["accessdate"], 4, 2) . '-' .
  substr($query_data["accessdate"], 6, 2) . ' ' .
  substr($query_data["accessdate"], 8, 2) . ':' .
  substr($query_data["accessdate"], 10, 2) . ':' .
  substr($query_data["accessdate"], 12, 2);

which produced the date and time in this format:

2004-01-22 10:31:35

Now you can achieve the same goal with a single query:

mysql> SELECT date_format(accessdate, '%Y-%m-%d % H:%i:%s')
    -> FROM access_log WHERE userid='Dodge';
+------------------------------------------------+
| date_format(accessdate, '%Y-%m-%d %H:%i:%s')   |
+------------------------------------------------+
| 2004-03-11 12:22:49                            |
| 2004-01-25 16:41:33                            |
+------------------------------------------------+
2 rows in set (0.00 sec)

If you want to know the weekday name of the last access date for a given user, make the following query using the dayname() server function that returns the weekday name of a given date value:

mysql> SELECT accessdate, dayname(accessdate) FROM access_log
    -> WHERE userid='Dodge';
+----------------+---------------------+
| accessdate     | dayname(accessdate) |
+----------------+---------------------+
| 20040311122249 | Thursday            |
| 20040125164133 | Sunday              |
+----------------+---------------------+
2 rows in set (0.01 sec)

The to_days() server function calculates the total number of days from the year 0 A.D.:

mysql> SELECT to_days(accessdate) FROM access_log
    -> WHERE userid='Dodge';
+---------------------+
| to_days(accessdate) |
+---------------------+
|             732016  |
|             731970  |
+---------------------+
2 rows in set (0.00 sec)

Suppose you want to know how many days have passed since a user's last visit to your site:

mysql> SELECT userid, to_days(now()) - to_days(accessdate)
    -> FROM access_log GROUP BY userid LIMIT 5;
+--------+--------------------------------------+
| userid | to_days(now()) - to_days(accessdate) |
+--------+--------------------------------------+
| Brian  |                                    7 |
| Dodge  |                                    0 |
| Greeny |                                   47 |
| Mac    |                                   49 |
| Nicrot |                                   47 |
+--------+--------------------------------------+
5 rows in set (0.01 sec)

You can see that the user Mac has not been to the site for 49 days. If you were upset about this, you could delete users who haven't visited your site for more than 48 days by issuing the following query:

mysql> DELETE FROM access_log WHERE to_days(now()) - to_days(accessdate) > 48;
Query OK, 1 row affected (0.01 sec)

This is just a sample of the many date- and time-related server functions MySQL provides. 

Please refer to the MySQL online reference manual for more information.

Updating and Deleting Records in Tables

Let's say that one of your registered users asks you to change his name, complaining that it contains a typo. Instead of replacing a whole record for that user with a new value for the username field, you'd probably just want to change his name in the existing record. As you've seen, this is where the UPDATE command comes into play. You'd use a statement like this:

UPDATE user SET username = 'Darren Ebbs'
            WHERE username = 'Daren Ebbs'

This changes all instances of the value Daren Ebs in the username field to Darren Ebbs.

If you want to throw away an existing record from a table, use the DELETE command:

DELETE FROM access_log WHERE page = 'who.html'

This deletes any record with page = ' who.html'.

The following UPDATE query is an example of what you should not do. It updates every record in the user table in such a way that any user can log on to your site using the password comeonin:

UPDATE user SET userpassword = password('comeonin')

Worse still, the following query empties the user table:

DELETE FROM user

Even an experienced database manager can make this kind of blunder—it just takes a bit of carelessness or the accidental dropping of a pen onto the Enter key while typing in a query, so do be careful when using these commands.

Suppose you want to delete all access log records from the year 1999 because they're no longer needed. You'd use a query that looks like this:

mysql> DELETE FROM access_log WHERE accessdate LIKE '1999%';

A user might want to change his password from time to time. The following query changes his password to guessit:

mysql> UPDATE user SET userpassword = password('guessit')
    -> WHERE userid = 'Greeny';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

The server reports that one record matched the specified condition and has therefore been changed. If the server finds no matching record, no update operation is performed:

mysql> UPDATE user SET userpassword = password('guessit')
    -> WHERE userid = 'Ginger';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

You can utilize existing values to update a record. For example, the visitcount field keeps track of how many times a specific user has visited a certain Web page. If the user visits that page again, you need some way to increment its value. You might do this by fetching the current value from the access_log table, incrementing it, and putting it back again. However, you can make the job a lot simpler by using the existing value in your UPDATE query. The following query does exactly what you want—it increments the visitcount field for user Dodge on the /score.html page:

mysql> UPDATE access_log SET visitcount = visitcount + 1
    -> WHERE user_id = 'Dodge' AND page = '/score.html';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

You'll need to update the lastaccesstime field separately. We'll discuss this later on in the chapter.

String values can be modified in a similar way:

mysql> UPDATE user SET userid = concat(userid, '_1');
Query OK, 1 row affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0

This query uses the MySQL server function to join strings, appending the string _1 to every userid field value. The user ID Dodge, for example, becomes Dodge_1. This method comes in handy when a new user requests a user ID that is already in use, and you want to suggest an alternative.

If you were to use SET userid = userid + '_1', it would set the userid to 0, rather than bundy_1.

Multiple assignments involving the same field are also possible:

mysql> UPDATE access_log
    -> SET visitcount = visitcount + 1, visitcount = visitcount * 2;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0

Assignments are evaluated from left to right, so the second assignment will use the incremented visitcount value from the first assignment. That is, it increments and then doubles every value in the visitcount field.

By now you're probably used to seeing a query report at the MySQL command line. If you use a DELETE or UPDATE query, the server automatically tells you how many rows were affected, how long it took to execute the query, counts for rows matched and changed, and any warnings produced.

You can get at the first of these from your PHP scripts by using the function mysql_affected_rows(). This takes a link identifier and returns the number of rows affected by the previous SQL query—a DELETE query in the following example:

$link_id = db_connect("sample_db");
mysql_query("DELETE FROM access_log
             WHERE page = '/penalties.html'");
$num_rows = mysql_affected_rows($link_id);
echo "$num_rows user(s) have been deleted.";

If just one user had visited the penalties page, this code would echo:

1 user(s) have been deleted.

If you were to delete all the records in a table, mysql_affected_rows() would return 0.

Chapter 11: Using PHP to Manipulate Data in MySQL

In this chapter you learn how to manipulate MySQL database records within PHP. Specifically, you'll see how to insert new records into a database table using PHP, and how to delete and update existing records in a database table.

To understand these functionalities using PHP, you'll create user registration and access logger scripts. You'll also upgrade the user record viewer you built in Chapter 10 to enable you to manipulate user records. This chapter concludes the three-chapter series of adventures into the world of MySQL and PHP MySQL connectivity.

Inserting Records using PHP

You saw how to insert data into a table using the mysql client in the preceding chapter. For example, the following query inserts a record for the user Pads:

mysql> INSERT INTO user VALUES(
    -> NULL,
    -> 'Pads',
    -> Password(12345),
    -> 'Brian Reid',
    -> 'Winger',
    -> 'Stickypads@doggies_rugby.com',
    -> 'A top class ball-handler.');

And you know you can achieve the same goal using PHP:

$result = mysql_query("INSERT INTO user VALUES( ... )");

Normally, you use PHP variables to insert values into a table instead of specifying them directly in a query:

$query = "INSERT INTO user VALUES(NULL, '$userid',
                                   password('$userpassword'),
                                  '$username', '$userposition', '$useremail',
                                  '$userprofile')";
$result = mysql_query($query);

Recall that the usernumber field is of AUTO_INCREMENT type—you specify it as NULL so that each new record you add has a usernumber value that's one greater than the last.

It's sometimes quite useful to get at the resulting value; for example, you might want to show a new user his user number on registration. The auto-incremented number generated by the last INSERT query can be obtained using the mysql_insert_id() function. Here's an example:

$link_id = db_connect('sample_db');
$result = mysql_query("INSERT INTO user (userid)
                       VALUES('sphinx')");
$usernumber = mysql_insert_id($link_id);
echo "Thank you. Your usernumber is $usernumber.";

Special Characters

As you learned in Chapter 10, it's essential to escape every single quote contained in a string value before inserting it into a database table, or it causes an error. In that chapter, you wanted to enter the string "I'm a rugby player." into the TEXT type userprofile field. That particular string works nicely in MySQL as well as PHP because it's surrounded by double quotes. If you were to use single quotes to surround the string, you'd need to escape the quote mark contained within it. Here are some examples:

"I'm a PHP developer." works.

'I\m a PHP developer.' also works.

'I'm a PHP developer.' doesn't work.

But why bother to make the distinction? Surely the best thing to do is to escape all instances of both sorts of quote marks—after all, in PHP, escaped quotes work in both single- and double-quoted string values. The problem is this: it depends upon the program in which you're using it. If you backslashed the single quote in the first example, you'd have this: "I\' m a PHP developer."

If you used PHP to echo this out, you'd get the whole string, backslash and all, which is not what you want. With this problem in mind, PHP provides you with a pair of very useful functions: addslashes() and stripslashes().

These two functions are closely related.addslashes() takes a string argument, prefixes every character that should be escaped in database queries with a backslash, and then returns the modified string.stripslashes() complements it by stripping out these backslashes.

The following lines of code show how you might use these functions to insert a record into the user table, having escaped the values as required:

$link_id = db_connect('sample_db');
$userprofile = addslashes($userprofile);
$query = "INSERT INTO user (userprofile) VALUES('$userprofile')";
mysql_query($query, $link_id);
$userprofile = stripslashes($userprofile);

Assuming that $userprofile initially contains the following:

I'm a PHP developer.

you pass it through addslashes(), after which it contains this:

I\'m a PHP developer.

And now it's safe to pass it to the SQL statement. When you read the value out of the database again, it'll be just as you want it—the server will return the quote character, and not the escape sequence. If you want to use the variable $userprofile in subsequent code, however, you don't want extraneous backslashes messing up your beautifully formatted text, and that's where stripslashes() comes in—you use it to tidy up the string.

htmlspecialchars()

Escaped quotes are by no means the end of the story as far as special characters are concerned. As you know, certain characters have special significance in HTML—<, for example, denotes the start of a tag. If it were contained in a user-entered string that was then presented in an HTML page, it would inevitably throw the browser (or at least the HTML parser program) into a hundred kinds of bother. The parser would assume that everything following it (until a > character was reached) referred to a tag. Suffice to say, it's very unlikely that it would!

To avoid this character looking like the start of a tag, you can represent it in the form of an HTML entity, which looks like this:

<

The PHP function htmlspecialchars()—which you may remember seeing very briefly in Chapter 5—takes care of translating HTML special characters like this into their entity form, so you've no need to worry about translating them yourself. Just as with the slash adding/stripping functions you just saw, htmlspecialchars() takes a string argument, changes all instances of special characters to corresponding safe forms (entity form in this case) and returns the modified string:

$userprofile = htmlspecialchars($userprofile);

The following characters are translated:

Special Character	HTML Entity
& (ampersand)	&
"(double quote)	"
< (less than)	&lt;
> (greater than)	&gt;

Now that you've seen some of the subtleties involved in PHP data insertion, let's move on to look at how you can update or delete existing data.