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

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

The MySQL-specific statement REPLACE works exactly like INSERT, except that if an old row in the  table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted  before the new row is inserted. REPLACE is a MySQL extension to the SQL standard. It either inserts, or  deletes and inserts. Uses the following general syntax; REPLACE INTO table_name (column_list) VALUES(value_list); In this example, we are replacing a current row of data (containing three columns) in the people database; REPLACE INTO people (id,name,age) VALUES(12,'Bruce',25); Note that unless the table has a PRIMARY KEY or a UNIQUE index, using a REPLACE statement makes  no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a  new row duplicates another. Values for all columns are taken from the values specified in the REPLACE statement. Any missing  columns are set to their default values, just as happens for INSE

UPDATE also allows a LIMIT clause, which places a limit on the number of records updated. For example, if there are two identical people records with the name value of 'Victor' and there is need to change just one of them to 'Vic', use this statement: UPDATE people SET name='Vic' WHERE name='Victor' LIMIT 1; Note: ORDER BY and LIMIT may be used together in the same UPDATE statement. Row contents will be as follows; +-------------------+ | id | name | age | +-------------------+ | 1 | Vic | 21 | +-------------------+

The statement succeeds if it updates id values first by setting 2 to 1 and then 3 to 2. However, it fails if it  first tries to set 3 to 2. That would result in two records having an id value of 2, so a duplicate-key  violation occurs. To solve this problem, add an ORDER BY clause to cause the row updates to occur in a  particular order: UPDATE people SET id=id-1 ORDER BY id; Row contents will be as follows; +-------------------+ | id | name | age | +-------------------+ | 1 | Victor | 21 | | 2 | Susan | 15 | +-------------------+

To renumber the id values to being at 1, the following UPDATE statement: UPDATE people SET id=id-1; Row contents will be as follows; +-------------------+ | id | name | age | +-------------------+ | 2 | Susan | 15 | | 1 | Victor | 21 | +-------------------+

The UPDATE statement modifies the contents of the existing records. To use it, name the table needing to  be updated, provide a SET clause that lists one or more column value assignments, and optionally specify a  WHERE clause that identifies which records to update. The Update statement uses the following general  syntax; UPDATE table_name SET column=expression(s) WHERE condition [options]; For example, to update the Country table by doubling the Population column for every country, the  following statement would be used; mysql> UPDATE Country            -> SET Population = Population * 2; Query OK, 232 rows affected, 1 warning (#.## sec) Rows matched: 239 Changed: 232 Warnings: 1 Note: The above statement returns a warning and completes the update for only most of the rows, but not  all. This is because some of the updated numbers exceed the number of characters allowed for that column. The effects of column assignments made by an UPDATE are subje

In general, if a column has no default value, the effect of omitting it from the INSERT depends on whether it can take NULL values and SQL mode being used: 􀂃 If the column can take NULL values, it is set to NULL. 􀂃 If the column cannot take NULL values, it is set to the implicit default for the column data type if strict SQL mode is not enabled. If the strict mode is enabled, an error occurs. 􀂃 To get the next available number in an AUTO_INCREMENT column, leave the column out of the column list, or give it the value of NULL in the value list. The same applies to TIMESTAMP type columns.

The MySQL-specific option LAST_INSERT_ID(expr) can be used after an INSERT to   after the insert takes place; mysql> INSERT INTO City (name, countrycode) -> VALUES ('Sarah City', 'USA'); Query OK, 1 row affected (#.## sec) mysql> SELECT LAST_INSERT_ID(); +---------------------------+ | LAST_INSERT_ID() | +----------------------------+ | 4080                            | +-----------------------------+ If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row  instead, LAST_INSERT_ID() is not meaningful. However, LAST_INSERT_ID(expr) can be used  to worked around this.

The SELECT command can also be used with insert in order to populate an existing table with matching  column data from another table. In this case, we have created a table like City and called it City2, then  added all the column/row data from the original City table in to this new table; INSERT INTO City2 SELECT * FROM City;

Now that tables have been created, the next step is to populate them with data. Although there are a variety  of ways to get data into MySQL tables, the INSERT statement is the most common method for getting data  into a table. The INSERT statement uses the following general syntax; INSERT INTO table_name (column_list) VALUES(value_list); An insert can include a only the value(s) without a column list (if the exact column order, quantity and types  are known), as follows; INSERT INTO numbers VALUES(250); Row contents will be as follows; +------+ | n         | +------+ | 250    | +------+ To add multiple column data into a table (containing an inventory of used books) on a First Edition copy of  the book A Tale of Two Cities by Charles Dickens, the following would be used; I NSERT INTO used_books (author, title, edition) VALUES  ('Charles Dickens', 'A Tale of Two Cities', 'First Edition'); Row contents will be as follows; +----

Temporal values include dates, times, and date/time values that have both a date and time. More  specialized temporal types are timestamp (commonly used for recording “current date and time”) and year  (for temporal values that require a resolution only to year units). Direct use of temporal values in expressions occurs primarily in comparison operations, or in arithmetic  operations that add an interval to or subtract an interval from a temporal value. Otherwise, most temporal  value operations are performed by using functions. Temporal data may be generated via any of the following means: 􀂃 Copying data from an existing date, datetime, or time column 􀂃 Executing a built-in function that returns a date, datetime, or time column 􀂃 Building a string representation of the temporal data to be evaluated by the server Date Components: Type                       Default Format DATE                        YYYY-MM-DD TIME                          HH:MM:SS DATETIME      

Operators such as = and != are useful for finding values that are equal to or not equal to a specific exact  comparison value. When it's necessary to find values based on similarity instead, a pattern match is useful.  To perform a pattern match, use value LIKE 'pattern', where value is the value to test and  'pattern' is a pattern string that describes the general form of values to match. Patterns used with the LIKE pattern-matching operator can contain two special characters (called  “metacharacters” or “wildcards”) that stand for something other than themselves: • Percent (%) Character - The percent character matches any sequence of zero or more characters. For example, the pattern 'a%' matches any string that begins with 'a', '%b' matches any string  that ends with 'b', and '%c%' matches any string that contains a 'c'. The pattern '%' matches  any string, including empty strings. • Underscore