Full Code Listing for Part 5
<html>
<head>
<title>CDs Bought</title>
</head>
<body>
<?php
if (!$_REQUEST['Submit']) {
html_form();
} elseif ($_REQUEST['Submit'] == "ViewCds") {
select_cd();
} elseif ($_REQUEST['Submit'] == "Edit") {
get_data();
} elseif ($_REQUEST['Submit'] == "Update") {
update_cd();
}
function my_conn() {
/* set's the variables for MySQL connection */
$server = "localhost:3306"; // this is the server address and port
$username = "username; // change this to your username
$password = "password"; // change this to your password
/* Connects to the MySQL server */
$link = @mysql_connect ($server, $username, $password) or die (mysql_error());
/* Defines the Active Database for the Connection */
if (!@mysql_select_db("cdlibrary", $link)) {
echo "<p>There has been an error. This is the error message:</p>";
echo "<p><strong>" . mysql_error() . "</strong></p>";
echo "Please Contact Your Systems Administrator with the details";
}
return $link;
}
function html_form() {
$conn = my_conn();
$SQL = "SELECT DISTINCT cd_releases.Artist FROM cd_releases;";
$result = mysql_query($SQL, $conn);
if (!$result) {
echo("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}
?>
<p>Please enter the search term for the artist</p>
<form name="cds" method="post" action="<? echo $_SERVER['PHP_SELF']; ?>">
Name of Artist: <select name="artist">
<?
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo("<option value=\"" . $row["artist"] . "\">" . $row["artist"] . "</option>\n");
}
?>
</select>
<input type="submit" name="Submit" value="ViewCds" />
</form>
<?
/* Closes Connection to the MySQL server */
mysql_close ($conn);
}
function select_cd() {
?>
<h4>Cds Bought</h4>
<?
$conn = my_conn();
/* Sets the SQL Query */
$sql = "SELECT * FROM cd_releases";
$sql .= " WHERE (cd_releases.Artist = '{$_POST['artist']}')";
/* Passes a Query to the Active Database */
$result = mysql_query($sql, $conn);
if (!$result) {
echo("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}
/* Starts the table and creates headings */
?>
<table>
<tr>
<td><strong>Artist</strong></td>
<td><strong>Title</strong></td>
<td><strong>Year</strong></td>
<td><strong>Label</strong></td>
<td><strong>No of Tracks </strong></td>
<td></td>
</tr>
<?
/* Retrieves the rows from the query result set
and puts them into a HTML table row */
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo("<tr>\n<td>" . $row["Artist"] . "</td>");
echo("<td>" . $row["Title"] . "</td>");
echo("<td>" . $row["Year"] . "</td>");
echo("<td>" . $row["Label"] . "</td>");
echo("<td>" . $row["No_of_tracks"] . "</td>");
echo("<td><a href=\"" . $_SERVER['PHP_SELF'] . "?releaseID=" .$row['releaseID'] . "&Submit=Edit\">Edit</a></td></tr>\n\n");
}
/* Closes the table */
?>
</table>
<?
/* Closes Connection to the MySQL server */
mysql_close ($conn);
html_form();
}
function get_data() {
/* Calls our connection function */
$conn = my_conn();
/* Defines query */
$sql = "SELECT * FROM cd_releases WHERE releaseID = " . $_REQUEST['releaseID'] . ";";
/* Passes query to database */
$result = mysql_query($sql, $conn);
if (!$result) {
echo("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}
/* creates our row array with an if statement to report errors */
if ($row = @mysql_fetch_array($result, MYSQL_ASSOC)) {
/* prints out the artist and title */
print "<h4>$row[Artist] - $row[Title]</h4>";
/* prints out our HTML form '\"' */
print "<form name=\"CDs\" method=\"post\" action=\"$_SERVER[PHP_SELF]\">";
/* Prints out hidden releaseID - we don't put this in the HTML form
so that the uer cannot edit the Key value in error */
print "<input type=\"text\" name=\"releaseID\" value=\"$row[releaseID]\">";
/* prints out our HTML table and fields 'escaping' any double quotes '\"' */
print "<table width=\"600\">
<tr>
<td width=\"150\"><strong>Artist</strong></td>
<td width=\"350\"><input type=\"hidden\" name=\"artist\" value=\"$row[Artist]\"></td>
<td rowspan=\"5\" valign=\"top\"><input type=\"submit\" name=\"Submit\" value=\"Update\">
</td>
</tr>
<tr>
<td width=\"150\"><strong>Title</strong></td>
<td width=\"350\"><input type=\"text\" name=\"title\" value=\"$row[Title]\"></td>
</tr>
<tr>
<td width=\"150\"><strong>Year</strong></td>
<td width=\"350\"><input type=\"text\" name=\"year\" value=\"$row[Year]\"></td>
</tr>
<tr>
<td width=\"150\"><strong>Label</strong></td>
<td width=\"350\"><input type=\"text\" name=\"label\" value=\"$row[Label]\"></td>
</tr>
<tr>
<td width=\"150\"><strong>Tracks</strong></td>
<td width=\"350\"><input type=\"text\" name=\"tracks\" value=\"$row[No_of_tracks]\"></td>
</tr>
</table>
</form>";
/* Second SQL query */
$sql_count = "SELECT * FROM cds_bought WHERE cds_bought.releaseID =" . $_REQUEST['releaseID'];
/* Passes count query to database */
$result_count = @mysql_query($sql_count, $conn);
if (!$result_count) {
echo("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}
/* Counts the number of rows (therefore copies) */
$count = mysql_num_rows($result_count);
if ($count != 1) {
print "<p>There are $count copies of this CD</p>";
} else {
print "<p>There is $count copy of this CD</p>";
}
} else {
echo("There has been an error" . mysql_error());
}
/* closes connection */
mysql_close ($conn);
}
function update_cd() {
/* Calls our connection function */
$conn = my_conn();
/* Defines query */
$sql_update = "UPDATE cd_releases SET ";
$sql_update .= "cd_releases.Artist = '" . $_REQUEST['artist'] . "', ";
$sql_update .= "cd_releases.Title = '" . $_REQUEST['title'] . "', ";
$sql_update .= "cd_releases.Year = '" . $_REQUEST['year'] . "', ";
$sql_update .= "cd_releases.Label = '" . $_REQUEST['label'] . "', ";
$sql_update .= "cd_releases.No_of_tracks = '" . $_REQUEST['tracks'] . "' ";
$sql_update .= "WHERE (cd_releases.releaseID = " . $_REQUEST['releaseID'] . ")";
/* Passes query to database */
$result = mysql_query($sql_update, $conn);
if (!$result) {
echo("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}
/* Prints succes message */
print "<p> Successfully Updated</p>";
/* closes connection */
mysql_close ($conn);
/* Calls get_data() function */
get_data();
}
?>
</body>
</html>
