Database


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.

Compound Queries

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.

PHP
 $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 (!) .

Fetch Revisited

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:

PHP
 $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.

PHP
 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.

NOTE

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:

PHP
 MySQL_field_seek(Result, New_Pointer_offset); 

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:

PHP
 MySQL_num_fields(Result); 

We can fetch fields, that is, information about columns, from the result table, using the proper command. This returns the field as an object.

PHP
 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()
PROPERTY NAME VALUE STORED VALUE TYPE NOTES
name Name of field String  
table Name of table String  
type Name of type String Some types, e.g., TinyInt , are reported as Unknown.
blob Is field a blob Boolean  
numeric Numbers only Boolean Times, Dates, Ints, and such are all numeric.
unsigned Int, range 0 “X Boolean Whether an Int is unsigned or not
zerofill Is the column? Boolean  
max_length # of characters Integer All types have this property, digits for numerics.
not_null Can field be null Boolean Is this a required field? Or can it be empty?
primary_key Self-Evident Boolean  
unique_key Self-Evident Boolean  
muliple_key Self-Evident Boolean Key that is not unique

Also, five commands can take this information and return values. They follow the familiar format

PHP
 MySQL_field_FUNCNAME(Result, Offset); 

The five functions that can replace FUNCNAME are listed below:

  • Flags Returns all boolean properties with the exception of numeric, as well as enum and auto_increment as string with each applicable flag written out. Sample output may read " not_null auto_increment unsigned ". The function explode() may be helpful in parsing this string into an array of individual flags.

  • Name Returns a string with the name of the field (that of the column the field is in) as a string.

  • Len Returns as an integer the maximum length in characters/digits that a field can hold.

  • Type Returns as a string the type of field: int , BLOB , timestamp , and so on. Some variations (e.g., TinyInt) are returned as unknown types by MySQL.

  • Table Returns a string with the name of the table this field comes from. An offset is required, because it is possible to have many tables contribute columns.



Flash and XML[c] A Developer[ap]s Guide
Flash and XML[c] A Developer[ap]s Guide
ISBN: 201729202
EAN: N/A
Year: 2005
Pages: 160

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