The database is the last portion of the solution we must add. In previous chapters, we created much of the code we need. The database accessing PHP code must draw on that work.
Designing the Database
We start by designing a table in which to store the users' account information. Obviously we will need to store the usernames and passwords so that the users can log in. Probably we want to store their e-mail addresses too, either for sending out updates, building a spam list, or informing users of pertinent news, such as the cancellation of an account due to inactivity.
Depending on what kind of system we are building, we may wish to gather marketing data, such as real names , postal service addresses, and such. Let us assume that we are not marketers and the marketers have yet to design our database. So we want to create a system that will work easily with a database that has not yet been designed.
Although more difficult, we can write PHP code that at runtime interprets the structure of the table.
For now, let it suffice to call the table Members. The only two columns we know exist are called UserName and Password, which hold, not surprisingly, the users' names and passwords, respectively.
To create this code, it is necessary that we introduce a few functions we don't already know. Most of them are new variations on old functions that hark back to previous chapters.
We know that to log in, users will need proper user names and passwords. If the pair submitted is not found in our database, an error should be returned. To see if the names and passwords match, we use a logical operation.
$Test = MySQL_query("select * from Members where UserName = $User && Password = $Password"); $Is_Valid_UserName_and_Password = MySQL_num_rows($Test); // 0 for false, anything else for true...
Like a programming language, the query syntax of MySQL allows us to build complex queries with any combination of the AND operator (&&) , the OR operator () , and the NOT operator (!) .
We have learned that a query to the database returns a result table, and we have learned how to fetch a sequence of data from that result. We did not discover earlier that we can also fetch information about the columns themselves . In this way we can move through a result column by column just as we formerly moved through row by row.
We can use automatic indexing, which takes advantage of the internal pointer in every PHP array. This pointer starts at the zeroth element and can advance itself during a fetch. We direct one query command to the database and choose from a myriad of related commands to examine the result table.
The Result that we pass to the fetch_field() , field_seek() , and num_fields() commands (learned later in this chapter) can be one of two kinds. We can use the result table that is returned by the common data record query, like the ones that we learned to do in Chapter 13:
$res = MySQL_query ("select * from Answers where WhichQuestion = $sqlQUIN");
Alternatively, a special command retrieves information that describes the table structure itself rather than the content of the tables.
MySQL_list_fields (Database, TableName, OptionalLinkIdentifier);
This command requires specifying a database, which can allow us to search through databases for a specific table set up in a particular way.
If we want to look through databases for a specific table, we need a list of all the databases. We can get it by using a MySQL_list_dbs() command. Similarly, we can get a list of tables by using MySQL_list_tables(Database) . Both return a result from which MySQL_tablename(Result, Offset) can read individual names. The MySQL_num_rows() command also works to count the number of tables found.
Another command simply moves the internal pointer without returning any data:
It is important that we do not run over the edge of the result in seeking fields. To this end, we can use a simple command to return the number of fields:
We can fetch fields, that is, information about columns, from the result table, using the proper command. This returns the field as an object.
MySQL_fetch_field( Result, Optional_Index);
Starting at 0, the fetch_field() command gets each field in order, silently advancing the internal pointer. This is like the fetch_row() commands. Adding the Optional_Index forces the internal pointer to a new position and then fetches a new field. The pointer advances normally during this fetch, resting at Optional_Index +1.
The fetch_field command returns an object. Table 17.1 lists its properties.
Table 17.1. Properties of the Object Returned by fetch-field()
Also, five commands can take this information and return values. They follow the familiar format
The five functions that can replace FUNCNAME are listed below: