YOUR WEBSITE

1. Login in to the mysql client using the login name and password provided by your instructor (if not already logged in). 2. In the photo_album databases, load the /tmp/photo_album.sql.data file into the database. USE photo_album; SOURCE /tmp/photo_album.sql; 3. Perform an aggregate select against each table to count the number of records located in each table. SELECT COUNT(*) FROM customers; SELECT COUNT(*) FROM comments; SELECT COUNT(*) FROM images; SELECT COUNT(*) FROM logins; 4. Using the customers table, list the customers who are active from closest to farthest termination date (term_date). SELECT * FROM customers WHERE active_customer='True' ORDER BY term_date ASC; 5. Using the logins table, list those with active logins and have logged in during the month of April 2007. SELECT * FROM logins WHERE active_login='True' && last_login LIKE '2007-04%'; 6. Using the images table, list the active images names from the most

1. Login in to the mysql client using the login name and password provided by your instructor. 2. View the existing databases that the MySQL Server is maintaining. SHOW DATABASES; 3. Utilize the photo_album database. USE photo_album; 4. Create the first table called customers that will store the information related to the customers who have purchased access to the online photo album CREATE TABLE customers ( email_customer VARCHAR(50) NOT NULL DEFAULT '', f_name VARCHAR(20) NOT NULL DEFAULT '', l_name VARCHAR(30) NOT NULL DEFAULT '', addr1 VARCHAR(30) NOT NULL DEFAULT '', addr2 VARCHAR(30) NULL, addr_city VARCHAR(25) NOT NULL DEFAULT '', addr_state CHAR(3) NOT NULL DEFAULT '', addr_zip CHAR(10) NOT NULL DEFAULT '', primary_phone CHAR(12) NOT NULL DEFAULT '', secondary_phone CHAR(12) NULL, term_date DATE NOT NULL DEFAULT 0, active_customer ENUM('True', 'False') NOT NULL DEFAULT 

1. Login in to the mysql client using the login name and password provided by your instructor. 2. View the existing databases that the MySQL Server is maintaining. mysql> SHOW DATABASES; 3. There should be only three databases that the MySQL Server is maintaining: INFORMATION_SCHEMA, mysql and test. Remove any additional databases that exist. If need be, mysql> DROP DATABASE db_name; 4. Create a new database called photo_album with a default character set of utf8. mysql> CREATE DATABASE photo_album CHARACTER SET utf8; 5. Using the mysql client, verify that the database was created. mysql> SHOW DATABASES; 6. View the contents of the /usr/local/mysql/data directory. Is there a new sub-directory associated with the database just created? >ls /usr/local/mysql/data/ 7. View the db.opt file located in the associated sub-directory to verify the correct default character set is being used. What collation type has been assigned as default? Does that mak

Due to the fact that PHP's regular expressions are so versatile, many functions that have been designed by PHP developers have been overlooked in the past because the tasks were able to be completed with regular expressions. However, there are two functions that should not be overlooked due to their simplicity in performing a more complex task. • count_chars -                        This built-in function provides information about the number of times that an standard ASCII character appears in the string. <?php $text = "I went to Mississippi for the summer."; $letters = count_chars($text, 1); // 1 counts only the characters that show up more than zero times // 0 is the default and produces the entire listing of standard // ASCII characters, even those not showing up in the string // 2 returns only those ASCII characters that do not show up // 3 & 4 return byte info (all located and all unused, respectively) foreach($letters as $key=>$value)

When performing the duties of creating/manipulating web content, the PHP programmer will need to modify the lengths of strings, either by padding the strings to be a certain length or removing strings to be a certain length. PHP provides numerous built-in functions to accomplish these tasks. The following are the most common functions for modifying string lengths: • ltrim -          The name of this function identifies its purpose, to trim the string content starting from the left portion of the string. By default, the ltrim() function will remove all white space characters which includes the standard space, the horizontal tab(\t), vertical tab (\x0b), new line (\n), carriage return (\r) and NULL (\0). This function will continue to remove these characters until it reaches a non-white space character and then the function removal process will terminate. <?php $text = \n\t Jane wanted to listen to the troupe play."; print ltrim($text); // The new text printed wi

• array_flip -                     This function is the opposite function for the get_html_translation_table() function by reversing HTML special characters back their original string characters. <?php $html_text = "J&uacute;an and Fel&iacute;pe paid 13&cent; a piece for gum." $translation_table = get_html_translation_table(HTML_ENTITIES); $flipped = array_flip($translation_table); print strtr($html_text, $flipped); // The sentence will now read "Júan and Felípe paid 13¢ a piece for // gum" without any HTML translation ?> • strip_tags () -                        This function removes all HTML and PHP tags from a string. <?php $html_text = "<a href=\"http://www.johnsgifts.com\"><strong> John's Gifts</strong></a>"; print strip_tags($html_text); // Returns John's Gifts with no HTML tags ?> In the even that a tag type should remain while all others are removed, that

PHP's strength is in its ability to turn a static web page into a dynamic web page by interacting directly with the HTML. Part of this strength is PHP's ability to convert non-HTML (text only) strings to HTML and HTML strings to non-HTML strings. To make this happen, PHP provides multiple built-in functions designed specifically for HTML conversions. The following is a list of the most commonly used: Text to HTML Conversion Functions • nl2br -             This function name (nl2br) is an acronym for "newline to <br /> tags" due to the fact that this function will convert all newline characters (\n) in strings to their XHTML-compliant equivalent (<br />). <?php $text = "See Spot. See Spot run. See Spot fall. See Spot's friends laugh. Hear Spot growl. See Spot's friends run."; print nl2br($text); // The sentence will now read "See Spot.<br />See Spot run. // <br />See Spot fall.<br />See Spo

Case sensitivity is an issue that the programmer (and the end user) must deal with. Everything from commands to passwords are affected by case sensitivity. In PHP there are four functions that provide the tools to deal with the case sensitivity issue by allowing the string case to be manipulated. • strtolower -                  This string case manipulation function converts all characters that are uppercase characters to lowercase characters. <?php $text = "MySQL http://WWW.MYSQL.COM"; print strtolower($text); // Returns "mysql http://www.mysql.com"; ?> • strtoupper -  This string case manipulation function converts all characters that are lowercase characters to uppercase characters. <?php $text = "MySQL http://WWW.MYSQL.COM"; print strtoupper($text); // Returns "MYSQL HTTP://WWW.MYSQL.COM"; ?> • ucfirst -                   This string case manipulation function converts only the first character of the

While the strcmp() function is the most commonly used string comparison function in PHP, there are other string comparison functions offered: • strcasecmp -                This string comparison function is equivalent to the strcmp() function except that it is not case sensitive. <?php function chk_emails($input1, $input2) { if (! strcasecomp($input1, $input2)) { print "E-mails are the same, proceed<br>"; }else { print "E-mails are not equal<br>"; } } chk_emails("jones@mysql.com","Jones@MySQL.com"); // strcasecmp is case-insensitive and thus these two e-mails // are the same ?> • strspn -               The strspn() function compares two strings to determine how closely related they are to each other. This is accomplished by returning the length of the first string containing characters that are also located in the second string. <?php function passwd_diff($n_passwd, $o_passwd) { if (strspn($n_p

Following the password example just shown, there are many web sites today that force a person to type there password in twice to make sure that they truly are setting it to what they want (the chances of typing it identically wrong twice is very low). This is accomplished using such tools as strcmp() which is a built-in to PHP. <?php Function chk_passwds($input1, $input2) { if (strcomp($input1, $input2) = 0) { print "Passwords are equal, proceed<br>"; }else { print "Passwords are not equal<br>"; } } chk_passwds("doggy1", "Doggy1"); // strcmp is case-sensitive and thus these passwords are not equal ?> The strcomp() function has three possible results: 0 means the two strings are equal, -1 means that the first string is less than the second string and 1 means that the second string is less than the first string. In the example above, all that needs to be tested is if they are equal. It is not necessary to kno