YOUR WEBSITE

When addressing the need to authenticate a web page (and subsequent pages/resources), integrating user authentication directly into the design of the web application logic is both convenient (in the sense that additional layers of communication is unnecessary) and flexible (in the sense that it is easier to integrate into other applications/scripts when contained in one location). PHP allows three types of authentication: Hard-coded, file-based and database authentication. Authentication Variables Within PHP, there are two pre-defined variables that are used in the authentication of users: • $_SERVER['PHP_AUTH_USER'] - This variable holds the username that is needed for authentication. • $_SERVER['PHP_AUTH_PW'] - This variable holds the password that is needed for authentication. Limitations of Authentication Variables When using the predefined authentication variables, it is important to keep in mind the following limitations: • Both variables must

This is the simplest PHP authentication to implement but has the problem of being limited on flexibility and high on maintenance cost. It literally places the username and password into the script as seen in the following example: <?php if (($_SERVER['PHP_AUTH_USER'] != 'root') || ($_SERVER['PHP_AUTH_PW'] != 'training')) { header('WWW-authenticate: Basic Realm="Photo Album"'); header('HTTP/1.0 401 Unauthorized'); print "You must provide a valid username and password!"; exit; } // Remainder of script ?> In this example, the first portion of the script reads in the username and password variables. If the variables do not match the hard-coded user name or password, then the script prints out some HTTP header information and text saying that the username and/or password was not valid. It then exits the script and terminates the rest of the script processing. If, though, the username and password ar

By default, the time that a session handler remains on the client side is set to 0, meaning that the session identifier will be "lost" when the web browser that initiated the original session handler page is shut down. This can be changed in the php.ini file by the configuration setting of session.cookie_lifetime. This is one of many configuration options that must be considered when setting up session handling for the specific instance of PHP: • session.save_handler - This configuration options defines the method in which the storage of the session will be handled. There are three options to choose from: o files - This method is the default and the most common. When this setting is set, the session handler uses files on the operating system to track the session information. o mm - This method stores the session information in shared RAM. Of the three, this option is the fastest but also the most volatile. o user - This method refers to using user-defined func

There are many ways that session handling can be used to provide a dynamic and non-static experience for the end user. Sessions can be used for providing a more personal experience for the end users by keeping track of them as they roam through a site providing content that is more tailored to their likes and dislikes (by monitoring the choices they make in what they choose to view). Sessions can also be used to make sure that only those who have a valid username and password can gain access to a specific part of a site, or in the case of any online store; keep track of what is in the shopping chart at any given time. These are just some examples of how session handling have added value to the web experience. MySQL is a perfect companion to session handling by being able to help with the process of storing and retrieving the data that gives extra life to the session handling capabilities. With the ability to connect multiple types of information (data stored in the database s

There are times when it is necessary (and good practice) to terminate a session and destroy all data associated with the current session. The function session_destroy is responsible for handling the described actions; however, this function is limited in its ability by not unsetting (or clearing) the global variables tied to the session or the respective cookies. This requires a more detailed approach: <?php session_start(); // Unset all session variables $_SESSION = array(); // Unset all cookie variables if(isset($_COOKIE[session_name()])){ setcookie(session_name(),'', time()-48000,'/'); } ?>

PHP stores all the session data associated with a session ID in a single string and handles the decoding of this string automatically. However, there are times when it may be necessary or beneficial to handle this process manually. The first thing to understand is how the string is put together. In the following example, three session variables are stored (last name, first name and phone number): lname|s:6:"Gordon";fname|s:6:"Jethro";phone|s:10:"7197235674"; The session variables are separated by a semicolon (;) and then can be broken down into their individual components: name of the variable, length of the string that is contained and the value itself. For the session variable lname, the length of the string is 6 characters long and the value that is stored in the string is "Gordon". Encoding the Session Variables The function session_encode allows for all the session variables available to the user to be encoded into a single

Once the session data has been stored, retrieving (or recalling) this information is also very easy to setup. The following demonstrates how this is accomplished: <?php print "Dear ".$_SESSION['fname']." ".$_SESSION['lname'].","; ?>

Once the session has been initialized, the data that must remain persistently can be stored in the superglobal $_SESSION array. The following are examples of storing persistence data to this superglobal array: <?php $_SESSION['fname'] = 'George'; $_SESSION['lname'] = 'Burnes'; ?>

As stated earlier, the process of PHP maintaining information from one session to the next for a visitor is pretty straight forward without lacking in capability. This maintaining of information (getting to know the end user) is accomplished through session handling tasks. Starting a Session It is necessary to tell PHP when to open up its eyes and prepare for the possibility of having to get to know an end user. This process is accomplished through the function session_start(). This function initializes the session data or continues the current session that is being passed by a request, such as a GET, POST or a cookie. <?php session_start(); ?> Setting up the Session "Key" After starting the session process, it is important to identify the "key" by which the user will be remembered. This key is stored on the end users machine and is called when interacting with the end user machine with the same function; session_id(). <?php session_s

What is Session Handling Hyper Text Transfer Protocol (HTTP) is the method on which information is transferred from a server to a client on the world wide web. This protocol is static in nature, that is every request is a new request and no persistence (or memory of a previous transfer) remains within the protocol itself. This has its advantages in the form of safety, the requests by either the client or the server has no long term affects on the other. This provides a level of security that could be related to a friendly acquaintance with each other and no long term affects are felt by the interaction. However, for the application developer (and dare it be said, also for the end user), there are times when a more intimate relationship is needed to add value to the experience. This is where session handling comes in. Basically, when a user interacts with a web page, the application attempts to get to know the person and retain a memory of their visit. Maintaining State I

Even though developers (most at least) strive for an application free of bugs, no application (except minor projects) can be expected to be completely bug-free. For that purpose, a good practice for programmers is to build in code that will trap and display errors in a way that is both meaningful and useful to assist in the correction of the error. PHP offers two functions that can display error messages from MySQL: • mysql_error() - This function displays the error message (if there was one) from the last MySQL function. The language that the message will be displayed in is based on the target language that is setup in the MySQL server. However, even if a fluent speaker of the message language were to read the majority of error messages returned, they would most likely still have a difficult time understanding what was said. Needless to say, error messages from MySQL are not very verbose or informative. <?php // Load variables used in mysql_connect and connect to serv

The most complete way to retrieve metadata is by using a SELECT statement against the INFORMATION_SCHEMA database in the MySQL server. However, there are times that is overkill for the data needing to be collected for a PHP application. With that said, PHP offers multiple functions that provide methods for retrieving a small amount of metadata about the data in the MySQL server. Database Metadata The mysql_list_dbs() function retrieves the name of all the databases found on the server that are accessible by the user requesting the information: <?php // Load variables used in mysql_connect and connect to server include "connect_info.php"; $linkID1 = mysql_connect($dbhost,$dbuser,$dbpass) or die("Could not connect to MySQL server"); // Retrieve the names of the databases accessible by the user $schema_results = mysql_list_dbs($linkID1); print "The databases available to the root user are:<br>\n"; while (list($schema) = mysql_fetch

The INFORMATION_SCHEMA is a virtual database that is automatically created and populated by MySQL, there is no need to create an INFORMATION_SCHEMA database. There also is no associated file structure due to the fact that the contents of the data are actual views, rather than logical data being stored in any one location. Only SELECT statements are allowed against the tables in the INFORMATION_SCHEMA database. The following SELECT example demonstrates some of the metadata that can be viewed: <?php // Load variables used in mysql_connect and connect to server include "connect_info.php"; $linkID1 = mysql_connect($dbhost,$dbuser,$dbpass) or die("Could not connect to MySQL server"); // Execute Query to Collect Metadata $query = "SELECT TABLE_NAME, ENGINE, TABLE_ROWS, UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='world'"; $results = mysql_query($query, $linkID1); // Build HTML Table of Results print "<table

Databases contain data, but information about the way databases are structured is metadata. The primary way of obtaining metadata is by using the INFORMATION_SCHEMA database to access metadata. MySQL produces metadata for several aspects of database structure. To name a few, metadata can be obtained from such database information as names of databases and tables, information about columns and indexes in tables, or stored routine definitions. Database Information In the world of web based applications. getting information about databases is important to be able to create generic and scalable applications. This section will demonstrate how to obtain information concerning the databases, the tables and the columns located in the MySQL server. Due to the fact that the root user is being used to log into the MySQL server, the resulting queries against the INFORMATION_SCHEMA database will result in a display of all information contained on the server. However, the majority of users

The majority of query commands that are sent to MySQL are of the SELECT nature in which a set of data will be returned from the query. Understanding how to deal with the query results from SELECT statements is probably one of the most important tasks associated with PHP and MySQL interactions. There are four main ways to accomplish this: • mysql_result($query_result, $row_id, $column_name) – Using the mysql_result() function will return a single row, single column of data from the query output. The $row_id is an offset numeric identifier (0 being the first row, 1 being the second row and so on). The $column_name is the actual column identifier that is returned by MySQL. In the event that the query used an alias to identify the column, the alias name must be used to identify the column. In a majority of cases, a single row of data is not that useful; however, using a looping construct with the mysql_result() function can prove quite useful: $query = "SELECT * FROM w

The PHP MySQL API includes a function that is able to group the selection of a database and a query against that database. This function is called mysql_db_query() and can be used in place of mysql_select_db() and mysql_query(). The syntax for the proper way to use this command is: $query_result = mysql_db_query($db_name, $query, $link_id); ... or without the $link_id variable ... $query_result = mysql_db_query($db_name, $query);

Retrieving Data The process of retrieving data once a connection has been made to the MySQL server is similar to the process of interacting with the data through the mysql client. The first thing that needs to be addressed is the database that holds the table (which in turn holds the data). Selecting a Database In the mysql client, a database is identified (or selected) by the USE <database> command. In PHP, this is accomplished with a PHP MySQL API function called mysql_select_db(). This function identifies the database that should be used for any subsequent requests to the data, which eliminates the need to qualify every table (or other MySQL resource) called with the database name. There are two ways to use this function: • mysql_select_db($db_name, $link_id) – Using the mysql_select_db function in this fashion ensures that the resource that initiated the connection to the MySQL server would be selected with the $link_id. The variable, that is identified here

For many programmers, the idea of storing connection information (such as username and password) directly in the script itself is a little unnerving and rightfully so. Even though, with the correct privileges assigned to the actual file housing the php script, there would be little concern for the scripts themselves from being seen. As a best practice, it is wise to create a separate PHP script file that would contain variables that would contain the connection information and could be used in the PHP script that is actually connecting to the MySQL server. A typical file containing this connection information would look something like the example below: <?php // MySQL Server Connection Information $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'training'; ?> In this example, the file would be saved as "connect_info.php" and stored in the same folder as our php script (most likely the htdocs folder of the apache server). Using t

The following example demonstrates an example of PHP connecting to the MySQL server, completing a query against the data contained in the MySQL server and then closing out the connection to the MySQL server (the other details of this script will be discussed throughout the remainder of this chapter): <?php /* Connect to MySQL server */ $linkID1 = mysql_connect('localhost','root','training') or die("Could not connect to MySQL server"); /* Query the MySQL Server for Information */ $query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'world'"; $result = mysql_query($query, $linkID1); while ($row = mysql_fetch_array($result)) { print $row[0]."<br>"; } /* Close the connection to the MySQL server */ mysql_close($linkID1); ?> Even though it is not necessary to provide the link identifier in the mysql_close() function seeing there is only one open connect; however, it is be

Even though it is important to understand the process of connecting to the MySQL server itself, it is also important to terminate the MySQL connection properly to ensure that there is no possibility of a connection remaining open beyond the need (which of course could become a security concern). If there is no specific termination function to the MySQL server, PHP's garbage collection feature will close the connection when the script terminates. There are two ways to close a connection to the MySQL server. • mysql_close($link_id) - This use of the mysql_close() function, which would include the $link_id of the connection resource that connected PHP to the mysql server would be closed. By identifying the $link_id, PHP looks for that connection to the MySQL server to close. mysql_close($link_id) • mysql_close() - This use of the mysql_close() function, which does not include the $link_id, the most recently open link is assumed. mysql_close()

PHP and MySQL are two different technologies that have found favor in the community of web developers based on their combined strength. This of course has given rise to both technologies finding great success in the marketplace that they may not have enjoyed otherwise. Those responsible for the two technologies have worked hard to provide the end users easy to use interfaces and technologies to ensure that the integration of the two are smooth and support the creation of next generation websites. User Privileges MySQL has a very strong authentication system that allows multiple security levels for each user that can access the server. MySQL's highly effective security system can cause headaches for those trying to break into the data, but likewise can cause headaches for those that are authorized to access the data. MySQL sees any requests from PHP in the same way that it sees requests from any other API (such as the mysql client or MySQL Query Browser) and will requir

MySQL allows the use of join syntax in UPDATE and DELETE statements to enable updates or deletes that involve multiple tables. They can be useful for storage engines where transactions and foreign keys are not supported, and to make sure that the tables are consistent. These statements can be used to perform the following operations: • Update rows in one table by transferring information from another table • Update rows in one table, determining which rows to update by referring to another table • Update rows in multiple tables with a single statement • Delete rows from one table, determining which rows to delete by referring to another table • Delete rows from multiple tables with a single statement Some of the principles involved in writing joins in SELECT statements also apply to multiple-table UPDATE and DELETE statements. This section provides a brief overview of their syntax. A multiple-table UPDATE is an extension of a single-table statement: • Following the UPDAT

A table name is always ambiguous when table is joined to itself using a self-join. For example, the Country table in the world database contains an IndepYear column indicating the year in which each country achieved independence. To find all countries that have the same year of independence as some given country, a self-join can be used. However, the query cannot be written like this: mysql> SELECT IndepYear, Name, Name -> FROM Country JOIN Country -> ON IndepYear = IndepYear AND Name = 'Qatar'; ERROR 1066 (42000): Not unique table/alias: 'Country' Furthermore, the ambiguity cannot be removed from column references by preceding them with table name qualifiers because the names remain identical: mysql> SELECT Country.IndepYear, Country.Name, Country.Name -> FROM Country JOIN Country -> ON Country.IndepYear = Country.IndepYear -> AND Country.Name = 'Qatar'; ERROR 1066 (42000): Not unique table/alias: 'Country' It

              When tables are joined, it's often the case that the tables contain columns with the same names. If such a column is referred to in the query, it's ambiguous which table the column reference applies to. This ambiguity usually can be addressed by qualifying column names with table names. However, if a join is on a table to itself, even the table name is ambiguous and it's necessary to use aliases to disambiguate table references. This section describes how to address naming issues in queries by qualifying column and table names and by using aliases. Qualifying Column Names                If a column name used in the query appears in more than one table, the name is ambiguous and it's necessary to provide information that identifies which table the column is connected to. This has been accommplished by qualifying the column name with the appropriate table name throughout the examples in this chapter. If name qualification has not been used, then pro

A right join is another type of outer join, written using the RIGHT JOIN keywords. Every RIGHT JOIN corresponds to an equivalent LEFT JOIN. The only difference is that the roles of the tables are reversed relative to the roles in a RIGHT JOIN. That is, the right table is the reference table, so a RIGHT JOIN produces a result for each row in the right table, whether or not it has any match in the left table. The previous example showed the result of a LEFT JOIN . The example below is the same statement, but with a RIGHT JOIN: mysql> SELECT Name, Language -> FROM Country -> RIGHT JOIN CountryLanguage -> ON Code = CountryCode -> WHERE CountryCode IS NULL; Empty set (#.## sec) Note that the join is now being referenced to the right table CountryLanguage, which yields no results (empty set). Syntactically, converting a left join to a right join requires only that the order be reversed in which the tables are named. It isn't necessary to also reverse the

Whereas an INNER JOIN will find combinations of matching rows from joined tables, the OUTER JOIN also finds the instances where a row in one table has no match in another table. An OUTER JOIN find matches (just like an INNER JOIN), but also identifies mismatches. Two forms of outer joins are LEFT JOIN and RIGHT JOIN . These do not use the comma separator or the INNER JOIN keywords. They each answer the same kinds of questions, but differ slightly in their syntax. That is a LEFT JOIN can always be re-written into a equivalent RIGHT JOIN. (In the following sections, the terms left table and right table refer to the tables named first and second in the FROM clause, respectively.)  Left Outer Join A left join is a type of outer join, written using the LEFT JOIN keywords. A left join treats the left table (the first one named) as a reference table and produces output for each row selected from it, whether or not the row is matched by rows in the right table. Like a join writt

The form of inner join syntax just discussed uses the comma operator in the FROM clause to name the joined tables. Another inner join syntax uses the INNER JOIN keywords. With this syntax, those keywords replace the comma operator between table names in the FROM clause. Also, with INNER JOIN,the conditions that indicate how to perform record matching for the tables move from the WHERE clause to become part of the FROM clause. There are two syntaxes for specifying matching conditions with INNER JOIN queries: • Add ON and an expression that states the required relationship between tables. Suppose that a join performs a country code match between the CountryLanguage and Country tables. The join would be written as follows: SELECT Country.Name, CountryLanguage.Language FROM CountryLanguage INNER JOIN Country ON CountryLanguage.CountryCode = Country.Code; • If the name of the joined column is the same in both tables, add USING() rather than ON after the table names, and list

A join that identifies combinations of matching rows from two tables is called an inner join. The joining is done by connecting one table, using the primary key, another table that references it with a foreign key. Inner joins may be written using two different syntaxes. One syntax lists the tables to be joined separated by a comma. The other uses the INNER JOIN keywords.  Comma Separated Inner Join To combine data from two or more tables to find the detail of records referenced in a foreign key , a table join must be performed. The most common way to do this is by specifying a list (comma separated) of tables in the FROM clause of the SELECT and using the WHERE clause to indicate the relationship between the tables. A simple question that might be asked about the information in the world database is, “What are the country and corresponding capital cities (along with ID)?” To answer that question, the following two individual queries could be executed… mysql> SELECT Na

What is a Join? The SELECT queries shown thus far in this training guide retrieve information from a single table at a time. However, not all questions can be answered using just one table. When it's necessary to draw on information that is stored in multiple tables, use a join condition—an operation that produces a result by combining (joining) information in one table with information in another. The contents of two original tables are shown below. These tables can be joined together using a SELECT query which retrieves data from more than one table at a time. table1 table2 +----+----+ | i1 | c1 | +----+----+ | 1 | a | | 2 | b | | 3 | c | +----+----+ 3 rows in set (#.## sec) +----+----+ | i2 | c2 | +----+----+ | 2 | c | | 3 | b | | 4 | a | +----+----+ 3 rows in set (#.## sec) mysql> SELECT * FROM table1 JOIN table2; +----+----+----+----+ | i1 | c1 | i2 | c2 | +----+----+----+----+ | 1 | a | 2 | c | | 2 | b | 2 | c |

To remove records(data) from tables, use the DELETE command. The DELETE statement allows a WHERE  clause that identifies which records to remove. To empty a table entirely by deleting all its records, use the  following syntax: DELETE FROM table_name; To remove only specific records in a table, a DELETE statement needs to be issued with a WHERE clause that  identifies which records to remove: DELETE FROM table_name WHERE …; Note: There is no need to mention any columns since it will delete whole rows at a time. Using DELETE with ORDER BY and LIMIT  DELETE supports ORDER BY and LIMIT clauses, which provide finer control over the way records are  deleted. For example, LIMIT can be useful to remove only some instances of a given set of records. Suppose that the people table contains five records where the name column equals 'Emily'. To keep only one  such record, use the following statement to remove four of the duplicated records. DELETE FROM people WH

If the MySQL-specific option ON DUPLICATE KEY UPDATE is specified, and a row is inserted that  would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is  performed. ON DUPLICATE KEY is like REPLACE but “nicer” in some situations because it doesn't  throw out the original row. For example, if a column is declared as UNIQUE and contains the value 1, the  following two statements have identical effect: INSERT INTO table (a,b,c) VALUES (1,2,3)  ON DUPLICATE KEY UPDATE c=c+1; UPDATE table SET c=c+1 WHERE a=1; The rows-affected value is 1 if the row is inserted as a new record and 2 if an existing record is updated. If column b is also unique, the INSERT is equivalent to this UPDATE statement instead: UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1; If a=1 OR b=2 matches several rows, only one row is updated. In general, avoid using an ON DUPLICATE KEY clause on tables with multiple unique indexes. From the previous example i