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:
mysqli.max_links "-1"
mysqli.default_port "3306"
mysqli.default_socket NULL
mysqli.default_host NULL
mysqli.default_user NULL
mysqli.default_pw NULL
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:
<?xml version="1.0"?>
<?xml-stylesheet href="testStyles.css" type="text/css"?>
<window
id="theMainWindow"
onload="initialize();"
title="Test Window"
orient="horizontal"
xmlns:xlink=http://www.w3.org/1999/ xlink
xmlns="http://www.mozilla.org/keymaster/ gatekeeper/there.is.only.xul">
<script
src="newssearch.js"/>
<!-- main top level container -->
<vbox flex="1">
<hbox flex="1" >
<!-- a container for some kind of list -->
<vbox flex="1" >
</vbox>
<!-- container for messages and tool areas -->
<vbox flex="2" >
<!-- used to display message -->
<hbox id="contentArea" flex="3" >
<spacer flex="1"/>
<vbox > <!-- stack message and login controls vertically -->
<spacer flex="1"/>
<description id="msgDescription">
Waiting for login.
</description>
<label value="User Name:" control="userName"/>
<textbox id="userName"/>
<label value="Password:" control="userName"/>
<textbox id="password" type="password" maxlength="8"/>
<button id="loginButton" label="Logon"/>
<spacer flex="1"/>
</vbox>
<spacer flex="1"/>
</hbox>
<!-- used to display message -->
<!-- used to display typing area -->
<hbox flex="3" >
</hbox>
<!-- used to display tool area-->
<hbox flex="1" >
<spacer flex="1"/>
<vbox>
<spacer flex="1"/>
<hbox>
<button id="B1" label="B1" />
<button id="B2" label="B2"/>
<button id="B3" label="B3"/>
<button label="B4"/>
</hbox>
<spacer flex="1"/>
</vbox>
<spacer flex="1"/>
</hbox>
</vbox>
<!-- container for messages and tool areas -->
</hbox>
<!-- horizontal container for all content (except status info) -->
<hbox >
<!-- stack info and resizer
horizontally -->
<!-- right align our status bar -->
<statusbar id="status-bar" class="chromeclass-status">
<statusbarpanel id="status-text" />
</statusbar>
<spacer flex="1"/>
</hbox>
<!-- main container -->
</vbox>
</window>
Next: Calling the MySQLi API, continued >>
More Web Standards Articles
More By O'Reilly Media
|
 This article is excerpted from chapter four of Programming Firefox, written by Kenneth C. Feldt (O'Reilly, 2007; ISBN: 0596102437). Check it out today at your favorite bookstore. Buy this book now.
|
|
