Getting Connected with Firefox and Chrome - Connecting PHP to MySQL (Page 2 of 4 )
To configure PHP to use the MySQL programming interface, we must modify PHP’s configuration file to load the MySQL libraries. The configuration file, php.ini, is located in the user’s PHP installation directory.
The specific switches to set depend on the versions of MySQL and PHP in use (check the most recent content of the MySQL and PHP web sites for specific entries). We will be using the MySQL Improved extension from our PHP scripts. The settings to enable the MySQL Improved extension are:
Once we have the correct settings, we can verify that Apache, PHP, and MySQL are up and running by using Firefox to open http://localhost/test.php. Scrolling down the window, we see the entries confirming a successful configuration (see Figure4-8 ).
Figure 4-8. Successful configuration of MySQL Improved (mysqli) extension
Calling the MySQLi API
We will use functions for the MySQLi library to compare the user input login and password against the entries in the accounts table.
Using the object-oriented approach to the MySQLi library, we create a database connection object and use that object reference to execute an SQLSELECTstatement against the database.
The results of this statement are contained in a variable pointing to a result object. The result object will contain a collection of all the database rows that were selected. Table 4-2 shows a summary of the mysqliPHP calls that we will be using.
Table 4-2. MySQLi objects and methods
Object/Function
Use
$database= new mysqli('hostname','username', 'password','databaseName');
Creates a database object reference by connecting to the database with the username and password specified. The database is identified by databaseName.
mysqli_connect_errno() mysqli_connect_error()
If the database object creation fails, these functions are used to audit an error code, and to extract the connect_errortext for reporting.
$searchResults =$database-> query('queryString')
Executes an SQL query, returning the result in a mysqli_result object.
$searchResults->num_rows
Returns the number of rows selected from the database as a result of the query.
$row= $searchResults->fetch_assoc()
Fetches the next row from the result object, returning the results as an associative array in which the keys to the array match the names of the row’s columns (e.g., to return the contents of a row’s "status" col-umn, the PHP script would read $row["status"];).
$searchResults->close()
Search results must be closed before attempting any additional queries.
$database->close()
Scripts must close the database prior to exiting.
We will rewrite the PHP scripts to use the database to select the rows for the username and password supplied by the interface. If there is a match (if one row is returned), we will return the proper flag, along with the last time the user logged in.
On the interface, if the user is authenticated, we will display the last login time in the application’s status bar.
The PHP doCommand.php file now has acheckUserfunction that looks like this:
function check_user($name,$pass) { $database = new mysqli('localhost','newssearch_guest','nsgst', 'newssearch'); if (mysqli_connect_errno()) { // failing case $retString = 'retcode=false,message='.mysqli_connect_error(); return $retString; } // failing case
$encryptPass = sha1($pass);
$query = "select status,last_session from account where username = '$name' and password = '$encryptPass'";
if ($theResult = $database->query("$query")) { // we have some kind of result
if ($theResult->num_rows == 1) { // we have our user
$theRow = $theResult->fetch_assoc(); // get the only row that exists $lastLogin = $theRow["last_session"];
if ($theRow['status'] == 'active') { // all OK $retString='retcode=true,last_login= '.$theRow['last_session']; // update the session info $theResult->close(); $curTime = date('c'); $update = "update account set last_session = '$curTime' where username = '$name'"; $theResult = $database->query("$update"); } // account is active
else { // account not active $theResult->close(); $retString = 'retcode=false,message=user account not active'; } // account not active
} // we have our user else { // user not found $theResult->close(); $retString = 'retcode=false,message=user not found'; } // user not cound
} // we have some kind of result
else { // no result returned $retString = 'retcode=false,message=invalid query'; } // no results returned
$database->close(); return $retString;
}
Before building the command string, we see the call to encrypt the password prior to its comparison with the database. Our query statement returns columns forlast_sessionandstatus. If one row is returned, there are statements to verify that the user has an “active” account before building a successful return code. We also call anUPDATEcommand on the database to set thelast_sessionentry to the current date and time.
We can change the XUL interface slightly to add a horizontal box with a status area to show the time of the user’s last login. The file newssearch.xul now looks like this:
Web Standards 
Completing a Configuration for Chrome and a Server
