Skip to main content

Posts

Showing posts from June, 2012

SQL Joins

1. Login in to the mysql client using the login name and password provided by your instructor (if not already logged in). 2. View the existing databases that the MySQL Server is maintaining. SHOW DATABASES; 3. Utilize the photo_album database. USE photo_album; ---- If Need be, recreate the database and load the SQL data in that will be used for this lab CREATE DATABASE photo_album; SOURCE /tmp/photo_album.sql; 4. Query the database to display the active primary customer (email, first and last name) and all the associated active login names associated with that customer. SELECT customers.email_customer, customers.fname, customers.lname, logins.login_name  FROM customers, logins WHERE customers.email_customer = logins.email_customer  AND customers.active_customer = 'True'  AND logins.active_login = 'True';

SQL DML Commands

1. Login in to the mysql client using the login name and password provided by your instructor (if not already logged in). 2. View the existing databases that the MySQL Server is maintaining. SHOW DATABASES; 3. Utilize the photo_album database. USE photo_album; ---- If Need be, recreate the database and load the SQL data in that will be used for this lab CREATE DATABASE photo_album; SOURCE /tmp/photo_album.sql; 4. Search the customers records for the customer with the following e-mail address: hollywood@truedog.com SELECT * FROM customers WHERE email_customer = 'hollywood@truedog.com'\G 5. Search the logins records for logins associated with the customer identified in step 4. SELECT * FROM logins WHERE email_customer = 'hollywood@truedog.com'; 6. In the customers table, update the customers e-mail address, identified in step 4, to howleewood@truedog.com. UPDATE customers SET email_customer = 'howleewood@truedog.com' WHERE email_custo

SQL Expressions

1. Login in to the mysql client using the login name and password provided by your instructor (if not already logged in). 2. View the existing databases that the MySQL Server is maintaining. SHOW DATABASES; 3. Utilize the photo_album database. USE photo_album; ---- If Need be, recreate the database and load the SQL data in that will be used for this lab CREATE DATABASE photo_album; SOURCE /tmp/photo_album.sql; 4. Calculate how many days ago each person logged into the photo_album system. Note: Difference in dates are returned in microseconds when directly subtracted from each other. SELECT login_name, ROUND((NOW() - last_login)/24/60/60/60) FROM logins; 5. List the actual day name (Monday, Tuesday, etc.) for the last logins to determine if there is a trend in the day of the week for the last time a person logged into the photo_album system. SELECT login_name, DAYNAME(last_login) FROM logins; 6. Modify the statement above by having the output show 'Weeke

SQL SELECT Commands

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

Managing Database Tables

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 

Managing Databases

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

Counting Strings

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)

Modify String Length

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

HTML to Text Conversion Functions

• 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

HTML Conversion

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

Manipulating String Cases

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

Other String Comparison Functions

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

Comparing Two Strings

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

Comparing Strings

As stated, PHP has poweful regular expressions that provide programmers with the tools necessary to complete difficult searching tasks. In addition to this regular expression capability, PHP provides built-in functionality for performing a wide range of string-specific tasks. Many of these tasks are based on string comparison operations. String Length One of the simplest comparison operators of strings is determining if the lengths are equivalent. This involves determining the length of the strings and then acting upon those lengths. The length function built-in to PHP is strlen() and is one of the more widely used string expressions. <?php function chk_passwd($input) { if (strlen($input) < 7) { print "Password is too short"; } else if (strlen($input) > 10) { print "Password is too long"; }else { print "Password is the proper length, proceed<br>"; } } chk_passwd("doggy1"); chk_passwd("Diabolical");

Perl-style regular expressions(preg_split )

• preg_split -                     This Perl-style function is equivalent to the split() function previously discussed with the exception of allowing Perl-style regular expressions. The third optional component of the preg_split() function can be entered to limit the number of changes that take place. Failing to enter a numeric limit (or a -1), ensures that there is no limit on the changes that can take place. <?php $csv_text = "5, \"Amsterdam\", \"NLD\", \"Noord-Holland\", 731200"; $csv_array = preg_split("/\,/",$csv_text); foreach ($csv_array as $csv_column) { print "$csv_column<br>"; } ?> The script above creates an array called $csv_row that contains the following element: [0] = 5, [1]="Amsterdam", [2]="NLD", [3]="Noord-Holland", [4]=731200). The foreach iterative control statement prints each of these elements on the screen on their own lines. A third component

Perl-style regular expressions( preg_replace_callback)

• preg_replace_callback() -                                            With preg_replace(), the function itself is responsible for handling the replacement procedure. With preg_replace_callback(), the handling of the replacement procedure is handed off to another function to take care of. The syntax is identical to preg_replace() except where the replacement text would be located, a function name is present. This function is responsible for handling any of the replacement changes to the text that matches the pattern searched. <?php $text = "Dear <pnm>S</pnm>Ortiz,<br>&nbsp;&nbsp;&nbsp;&nbsp;I would like to thank you and <pnf>F</pnf>Picard for taking the time in talking to me today. Please feel free to contact me if you have any additional questions.<br>Sincerely,<br><pnm>E</pnm>Bob Riker"; function proper_name_m($matches) { $titles = array( 'E' => 'Mr. ', 'S'

Perl-style regular expressions( preg_replace)

• preg_replace() -                              This Perl-syntax function is equivalent to the ereg_replace() function. This function works by replacing all occurrences of the pattern searched with the replacement characters and then returns the modified result. The fourth parameter is optional, but when a number is entered it determines how many occurrences of the pattern searched will be replaced. If no number is entered, all occurrences of the search pattern will be replaced. <?php $url = "MySQL http://www.mysql.com"; print preg_replace("/http:\/\/(.*)/","<a href=\"\${0}\">\${0}</a>", $url); ?> The script above changes the http reference to be replaced with the HTML code required to create a web link. The three components of this function include the pattern to search for ("/http:\/\/(.*)/"), the replacement string ("<a href=\"\${0}\">\${0}</a>") and then finally the

More on Perl-style regular expressions

• preg_match_all() -                                This function is similar to preg_match(); however, rather than searching for just one occurrence of the search parameter, this function will find all occurrences. In addition, each occurrence found will be placed into an array variable entered into the third input parameter. The array created will contain various sections of the subpatterns contained in the search pattern. Just like preg_match(), if there is a match, the function returns a TRUE, if the regular expression does not find a match, a FALSE is returned. <?php $text = "mysql_close, mysql_connect and mysql_error are all PHP functions used when working with MySQL."; if (preg_match_all("/mysql\w+/",$text,$mysql_commands) { print_r($mysql_commands); } else { print "No mysql commands were found"; } ?> • preg_quote() -                         This function places a backslash in front of all special regular expression charact

Using Perl-style regular expressions

Within PHP there are seven functions used for searching strings using Perl-style regular expressions: • preg_match() -                          This function utilizes the Perl-style regular expressions to search strings provided for a match. If there is a match, the function returns a TRUE, if the regular expression does not find a match, a FALSE is returned. <?php $phone_number = "1900Atlanta"; if (preg_match("/.\D/",$phone_number)) print "The Phone Number must contain only numeric digits"; ?> • preg_grep() -                        This function is similar to the preg_match() function, but works only on arrays. The preg_grep() function is also useful in that it creates a new array based on those matches found in the search array. <?php $product_codes = ("NZ3456", "SUS5678", "SNZ3294", "US4678"); $NZ_Codes = preg_grep("/NZ/", $product_codes); print_r($NZ_Codes); //NZ3456 and

Perl-style

The Perl programming language is exceptional at parsing strings by providing a comprehensive regular expression language to the programmer. Rather than creating their own regular expression language, the developers of PHP made the Perl regular expression syntax available to PHP users. The Perl-style regular expression syntax was built from the POSIX regular expression syntax and thus hold many of the same features. In fact, PHP programmers can use many of the same POSIX regular expression syntax when using Perl-style regular expression syntax. The basic Perl-style regular expression syntax involves using forward slashes (/ /) to identify the pattern that will be searched for: /mysql/ will find any string that contains the pattern "mysql", /m+/ will find any string that contains the letter "m" followed by one or more characters (mysql, mom, mudd, my, etc.) and /m{2,4}/ will find any string that contains the letter "m" followed by 2 or 4 character

POSIX regular expressions(SQL)

• sql_regcase -                         This function converts each alphabetical character into its equivalent lower and upper case representation surrounded by brackets. For those characters that are not alphabetical, no change is taken place. <?php $text = "MySQL's website is http://www.mysql.com"; print sql_regcase($text); // Outputs [Mm][Yy][Ss][Qq][Ll]'[Ss] [Ww][Ee][Bb][Ss][Ii][Tt] // [Ee] [Ii][Ss] [Hh][Tt][Tt][Pp]://[Ww][Ww][Ww].[Mm][Yy][Ss] // [Qq][Ll].[Cc][Oo][Mm] ?>

POSIX regular expressions(SPLIT)

split -           This function divides strings based on the pattern that it searches for and stored the separated text into an array. The searches are case-sensitive when dealing with alphabetical characters. <?php $csv_text = "5, \"Amsterdam\", \"NLD\", \"Noord-Holland\", 731200"; $csv_array = split("[,]",$csv_text); foreach ($csv_array as $csv_column) { print "$csv_column<br>"; } ?> The script above would create an array called $csv_row that contains the following element: [0] = 5, [1]="Amsterdam", [2]="NLD", [3]="Noord-Holland", [4]=731200). The foreach iterative control statement would print each of these elements on the screen on their own lines. A third component can be placed in the split function that would limit the number of array elements that would be created. By adding 3 to the split function in the above script (split("[,]",$csv_text, 3) the

More on POSIX regular expressions

• ereg_replace()-                          This function is equivalent to ereg() in its case-sensitive searching capabilities; however, it adds the ability to replace the characters it is searching for with new characters instead of just simply locating them. If the search pattern is not found, the string it searched against is unchanged. <?php $url = "MySQL http://www.mysql.com"; print ereg_replace("http://([a-zA-Z0-9./-]+)$", "<a href=\"\\0\">\\0</a>", $url); ?> The script above would change the text "MySQL http://www.mysql.com" to "MySQL <a href="http://www.mysql.com">http://www.mysql.com</a>". This change would allow the http reference to be replaced with the HTML code required to create a web link. The three components of this function include the pattern to search for ("http://([a-zA-Z0-9./-]+)$"), the replacement string ("<a href=\"\\0\&qu

Using POSIX regular expressions

Within PHP there are seven functions used for searching strings using POSIX-style regular expressions: • ereg () -               This function utilizes the POSIX-style regular expressions to search strings provided for a match and is case-sensitive. If there is a match, the function returns a TRUE, if the regular expression does not find a match, a FALSE is returned. <?php $phone_number = "1900Atlanta"; if (ereg("[^0-9]",$phone_number)) print "The Phone Number must contain only numeric digits"; ?> The result of the above php script, which tests for any characters in the $phone_number variable that are not numeric digits, would be the printing of "The Phone Number must contain only numeric digits". Another way to use the ereg() function is to utilize the optional parameter regs (the third parameter) to break up a string. The following demonstrates how this would work: <?php $phone_number = "515-435-6789"; $

POSIX

This form of regular expression searching is compliant with extended regular expressions (EREs) defined in IEEE POSIX 1003.2. Extended regular expressions are similar in syntax to the traditional Unix regular expressions, with some exceptions. The true power of the ERE's is their ability to be combined together to form searches. The POSIX ERE's are utilized in three methods: Brackets, Quantifiers and Predefined Character Ranges. •  Brackets -                      Brackets ([ ]) provide the capability to find ranges of characters when searching through strings. In non-extended regular expressions, when a search is made against a string for the term "dog", only words that contain the letters d-o-g in sequence are found. With brackets, the search using the [dog] will find any words that contain the letter "d", or the letter "o" or the letter "g". The following are a list of the most commonly used character ranges: ô€‚ƒ [0-9] mat

PHP'S STRINGS AND REGULAR EXPRESSIONS

A regular expression is a pattern of characters that are used to match a set of strings when performing searches. Regular expressions, when created, are enclosed in forward slashes and tested against a string. PHP offers functions associated with two types of regular expression tools: POSIX and Perl-style.