Section 1:  PHP
<?php

if ($_POST[\'send\']) //process the form
{
	$headers = \"From: \" . $_POST[\'from\'] . \"\\r\\n\" .
  	\"Reply-To: \" . $_POST[\'from\'] . \"\\r\\n\" .
  	\"X-Mailer: PHP/\" . phpversion();

 	//send the email
	mail($_POST[\'to\'], $_POST[\'subject\'], $_POST[\'message\'], $headers);

	//also, put in in the db
	$db = @mysql_connect(\'localhost\',\'username\',\'password\');
	@mysql_select_db(\'email_log\',$db);

	$query = \"insert into contact_log values(\'\" . $_POST[\'from\'] . \"\', \'\" . $_POST[\'subject\'] . \"\', \'\" . $_POST[\'message\'] . \"\')\";
	@mysql_query($query);
}
else //print the form
{
	include(\'header.php\'); //HTML stuff.
?>
	<form class=\'contactform\' action=\"<?= $_SERVER[\'PHP_SELF\'] ?>\" method=\'post\'>
		<input type=\'hidden\' name=\'to\' value=\'info@pacsupplyco.com\'>
		Enter your Email Address:<br>
		<input type=\'text\' name=\'from\' size=\'35\'><br><br>
		Subject:<br>
		<input type=\'text\' name=\'subject\' value=\'Feedback for Pacific Supply Group\' size=\'35\'><br><br>
		Message:<br>
		<textarea name=\'message\'> </textarea>
		<input type=\'submit\' name=\'Send\' value=\'Send\'>
	</form>
<?php
	include(\'footer.php\'); //Rest of the HTML
}
?>


1.  What does this code do?



2.  What\'s bad about it?



3.  What would you do to fix it?




Section 2:  Mysql

1.  A slowdown on a website has been traced to the mysql daemon.  How do you find out which query is causing the problem?

Assume the found slow query is as follows.  \"search_string\" refers to a variable given by a user.

select * from table_products p
	left join table_manufacturers m on p.manufacturer_id = m.manufacturer_id
	left join table_suppliers s on p.supplier_id = s.supplier_id
	left join table_products_meta_data pmd on p.products_id = pmd.products_id
	where ( p.product_name like \"%search_string%\"
		or p.product_description like \"%search_string%\"
		or p.product_model like \"%search_string%\"
		or pmd.product_keywords like \"%search_string%\" )
		and p.product_status = 1;

This query refers primarily to the table_products table.  Assume other tables are perfect.  Here\'s the create statement for table_products, as well as one for table_meta_data (for later):

CREATE TABLE `table_products` (
`product_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`product_name` TEXT NOT NULL ,
`product_description` TEXT NOT NULL ,
`product_model` TEXT NOT NULL ,
`manufacturer_id` INT NOT NULL ,
`supplier_id` INT NOT NULL ,
`product_status` INT NOT NULL ,
`price` FLOAT NOT NULL,
`quantity` INT NOT NULL
) ENGINE = MYISAM ;

CREATE TABLE `table_products_meta_data` (
`products_meta_data_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`product_id` INT NOT NULL,
`product_keywords` VARCHAR(30),
`product_added` VARCHAR(30),
`product_last_viewed` VARCHAR(30),
) ENGINE = MYISAM ;


2. How would you find out why this query is slow?


3. What changes would you make to the query and/or the tables to make it run faster?


4.  In table_products_meta_data, what does VARCHAR(30) mean?

5.  In table_products_meta_data, product_added and product_last_viewed store the date and time the product was added and last viewed, respectively.  What\'s wrong with these fields?

6.  Given the table definitions above, write queries to do the following:

a) Select up to 50 products with product_models field having the form xxx-xxxx where x is any letter or number.

b) Retrieve the names of all products that have keywords in the table_products_meta_data table.

c) Total up the retail value of all products currently in stock.  Assume `price` is the retail price and `quantity` is how many we have in stock.