YOUR WEBSITE

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