We exclusively write PHP code in this chapter (although one of the PHP commands we learn can take MySQL code as a parameter). PHP has several functions that allow a script to connect to a MySQL server, so we must have access to one before we can play with this code. In addition to connecting to the MySQL database via a series of functions, PHP also has a collection of functions that act on the returned data. The results of queries are stored by PHP as variables , so once the query is actually collected, it is quick to go through. Starting with the code in Chapter 10, we have to replace only the constructor of the Question class. Then our PHP code will be serving questions out of a database.
Connecting to a Server
To use PHP to interface with a MySQL database, the first thing we have to do is to connect to the server that hosts that database. The standard command used to connect to a server is
MySQL_connect( URL, UserName, Password);
All three of these parameters are optional and each one is prohibited if any of the prior parameters has been omitted. The Password is assumed to be null. The UserName is assumed to be the same as that of the user who owns the process that is executing this command on the server. The default URL is the local host. In later versions of PHP it has become accept able to add to the URL either a port (as :port ) or a path to a socket (as :/path/to/socket ).
It is important to note that this function returns a value called a link identifier. Other than MySQL_connect , every MySQL command deals with an already existing connection or an already existing result.
We can open simultaneous connections to various servers. (If we connect twice to the same server, both connections return the same pointer.) When we have multiple connections, we must add a last parameter to every MySQL PHP query command. This is the link identifier for the selected server. Otherwise, the default is the last opened connection. Our simple project does not require multiple servers, so the optional end parameter is left off all com mands in our examples.
The connection remains open until the script that called it ends or we prematurely close the connection using the close command:
This closes the appropriate link. It does not affect persistent connections. Such connections can be easily created.
If you want to make a persistent connection that will not close either with this command or with the ending of the script, use
MySQL_pconnect(URL, UserName, Password);
This has some value if the same connection is opened by a script many many times, and it is more efficient merely to never close the connection.
H ANDLING C ONNECTION E RRORS
Many PHP coders use a graceful style of error checking with lines like
MySQL_connect() or some_error_handler
It is conditional execution coded as a compound conditional expression. If the MySQL command returns nonzero (if it has not experienced an error), the rest of the or statement will be skipped . If there is an error, the handler is called.
Sometimes errors and warnings are undesirable. They can interrupt communications between client and server (or scare users.)
If we add the prefix @ to the connect command, the error messages generated will not display. Of course, it may help to know what the error we are catching is. MySQL_errno() returns the error number (or 0 if none) of the last MySQL_XXX command. MySQL_error() will return the text of the error message.
Most of the time we do not have the access required to add or delete (drop) databases. Nor do we desire to have PHP do this, when doing so by hand is much simpler and less prone to bugs .
This command creates a new database on the server that we are logged onto with the name New_DB_Name.
This command deletes a database.
A much more useful command is one that selects the database we want. For instance, over the course of this book, all our code has been stored in one database on one server. We've already learned how to connect to a server. To enter our database so that we can access all the tables within, we type