Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

on the state the customer is from. Pass this value to a Perl script that uses that

parameter in a SQL SELECT query.

The answer to this question can be found in the Appendix C directory located on the CD-

ROM that accompanies this book.

2. Create a Perl Script that adds a user to the database. Hint: use the GRANT statement.
   3. #! /usr/local/bin
   4. use DBI;
   5. $DSN = "DBI:mysql:Meet_A_Geek:10.1.1.50";
   6. $dbh = DBI->connect($DSN,"mark","tacobell");
   7. $dbh->do("GRANT ALL ON . TO Joe@% IDENTIFIED BY "thecircus");
   $dbh->disconnect();

Day 15

1. Write the PHP syntax for connecting to a MySQL database and doing a SELECT on a
   table called products. The table includes the fields name and price. Your query
   should retrieve all products whose price is less than $50. You should allow for an
   error condition and, if need be, call the user-defined error_report function.
   2. <?php
   3.
   4. function error_report () {
   5. echo "Error: ".mysql_errno()."; error description:
   6. ".mysql_error()."
   \n";
   7. }
   8.
   9. $sql = "SELECT name, price FROM products WHERE price < 50";
   10. if ($result = mysql_query ("$sql")) {
   11. while ($row = mysql_fetch_array ($result)) {
   12. echo "Product: ".$row[name]." price $".$row[price]."
   \n";
   13. }
   } else error_report ();

Day 16

1. Write a SELECT statement to output the current time in the format 'hour:minute
   am/pm weekday nth month year'.
   SELECT DATE_FORMAT(NOW(),'%r %W %D %M %Y');


2. Write a SELECT statement to find which weekday the 4th of July will fall on in 2010.
   SELECT DAYNAME('2010-07-04');


3. Write a SELECT statement to add 1,000 days to 1 April 2000, add 1,000 and return
   the date. On what weekday will the subsequent day fall?
   SELECT DATE_ADD('2000-04-01',INTERVAL 1000 DAY);

The day is a Friday. You can get the second result using

DAYNAME(DATE_ADD('2000-04-01',INTERVAL 1000 DAY)).

Day 17

1. Add several users to the Meet_A_Geek database using the techniques that were
   discussed on this day. Add one using the GRANT statement, one using the
   mysql_setpermission utility, and one where you actually manipulate the grant
   tables manually.

Using the GRANT statement:

GRANT ALL ON *.* TO Paul@% IDENTIFIED BY "horatio";

Use the mysql_setpermission command (if you get stuck, use the -help option).