In this assignment, you shall create tables that store data about audio albums -
ID: 3859006 • Letter: I
Question
In this assignment, you shall create tables that store data about audio albums - CDs, cassettes, LPs, DVDs, etc. Create 1 or 2 MySQL table using any of the online medias.
Requirements
-Design your database. Keep it simple – One or two tables - albums and artists.
-You shall create tables in your database as needed.
-Create a login section for users to login ***extra credit if you can make this login functional such as some password in database *******
-You shall write the following HTML and PHP documents:
-a createTables.php script that creates the tables
-a form for the user to enter data for a new album
-a PHP script that handles the form
-a form for the user to enter data for a new artist
-a PHP script that handles the form
-a PHP script that handles the form
-a form that allows the user to search the database or show contents
Keep the database design simple - not too many tables. You may design a create and retrieve operations on a MySQL database using PHP functions. Please provide an interface to the user. This is a full three-tier small web app.
Home Work FAQS
- Do you care what type of artist/album information is in the tables? Any artist and album material is ok
- What do you mean by "use any of the above medias?" Online Media
- If PHP creates the tables, are they recreated every time? All tables should be stored away in a database; You can also write a query to search and retrieve from the database.
- What is the login supposed to do? After the user is logged in user can now view their material also you can redirect the user to a second page to view materials.
Explanation / Answer
db.php
<?php
$con = mysqli_connect("localhost","root","","user");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>
create table.php
CREATE TABLE `user`.`album` (
`id` INT NOT NULL AUTO_INCREMENT ,
`title` VARCHAR( 225 ) NOT NULL ,
`typeofalbum` VARCHAR( 225 ) NOT NULL ,
`material` VARCHAR( 225 ) NOT NULL ,
`price` INT( 11 ) NOT NULL ,
`trn_date` DATETIME NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS register.`users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`email` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
`trn_date` datetime NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `user`.`artist` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`artistname` VARCHAR( 255 ) NOT NULL ,
`artistemail` VARCHAR( 255 ) NOT NULL ,
`artistphone` INT( 11 ) NOT NULL ,
`date` DATETIME NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;
login.php
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Login Form</title>
<link rel="stylesheet" href="css/style.css" />
</head>
<body>
<?php
require('db.php');
session_start();
if (isset($_POST['username'])){
$username = stripslashes($_REQUEST['username']);
$username = mysqli_real_escape_string($con,$username);
$password = stripslashes($_REQUEST['password']);
$password = mysqli_real_escape_string($con,$password);
$query = "SELECT * FROM `users` WHERE username='$username' and password='".md5($password)."'";
$result = mysqli_query($con,$query) or die(mysql_error());
$rows = mysqli_num_rows($result);
if($rows==1){
$_SESSION['username'] = $username;
header("Location: index.php"); // Redirect user to index.php
}else{
echo "<div class='form'><h3>Username/password is incorrect.</h3><br/>Click here to <a href="login.php">Login</a></div>";
}
}else{
?>
<div class="container">
<div class="header"><h1>Welcome to Our Site</h1></div>
<div class="form">
<h2>Log In Form </h2>
<form action="" method="post" name="login">
<input type="text" name="username" placeholder="Username" required />
<input type="password" name="password" placeholder="Password" required />
<input name="submit" type="submit" value="Login" />
</form>
<p>Not registered yet? <a href="registration.php">Register Here</a></p>
</div>
<?php } ?>
<div class="footer"><h6>@copyrights- 2017</h6></div>
</div>
</body>
</html>
registration.php
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Registration Form</title>
<link rel="stylesheet" href="css/style.css" />
</head>
<body>
<?php
require('db.php');
// If form submitted, insert values into the database.
if (isset($_REQUEST['username'])){
$username = stripslashes($_REQUEST['username']); // removes backslashes
$username = mysqli_real_escape_string($con,$username); //escapes special characters in a string
$email = stripslashes($_REQUEST['email']);
$email = mysqli_real_escape_string($con,$email);
$password = stripslashes($_REQUEST['password']);
$password = mysqli_real_escape_string($con,$password);
$trn_date = date("Y-m-d H:i:s");
$query = "INSERT into `users` (username, password, email, trn_date) VALUES ('$username', '".md5($password)."', '$email', '$trn_date')";
$result = mysqli_query($con,$query);
if($result){
echo "<div class='form'><h3>You are registered successfully.</h3><br/>Click here to <a href="login.php">Login</a></div>";
}
}else{
?><div class="container">
<div class="header"><h1>Welcome to Our Site</h1></div>
<div class="form">
<h2>Registration Form</h2>
<form name="registration" action="" method="post">
<input type="text" name="username" placeholder="Username" required />
<input type="email" name="email" placeholder="Email" required />
<input type="password" name="password" placeholder="Password" required />
<input type="submit" name="submit" value="Register" />
</form>
<br /><br />
</div>
<?php } ?>
<div class="footer"><h6>@copyrights- 2017</h6></div>
</div>
</body>
</html>
index.php
<?php
session_start();
if(!isset($_SESSION["username"])){
header("Location: login.php");
exit(); }?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Welcome Home</title>
<link rel="stylesheet" href="css/style.css" />
</head>
<body>
<div class="container">
<div class="header"><h1>Welcome to Our Site</h1></div>
<div class="form"
><p>Welcome <?php echo $_SESSION['username']; ?>!</p>
<p> To enter new album data <a href="album.php" target="_blank">Click Here</a></p>
<p> To enter new artist data <a href="artist.php" target="_blank">Click Here</a></p>
<a href="logout.php">Logout</a>
</div>
<div class="footer"><h6>@copyrights- 2017</h6></div>
</div>
</body>
</html>
album.php
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Album Data</title>
<link rel="stylesheet" href="css/style.css" />
</head>
<body>
<?php
require('db.php');
if (isset($_REQUEST['title'])){
$title = stripslashes($_REQUEST['title']);
$typeofalbum = stripslashes($_REQUEST['typeofalbum']);
$material = stripslashes($_REQUEST['material']);
$price = $_REQUEST['price'];
$trn_date = date("Y-m-d H:i:s");
$query = "INSERT into `album` (title, typeofalbum, material, price,trn_date) VALUES ('$title', '$typeofalbum', '$material','$price','$trn_date')";
$result = mysqli_query($con,$query);
if($result){
echo "<div class='container'>
<div class='header'><h1>Welcome to Our Site</h1></div>
<div class='form'><h3>Your album data inserted successfully.</h3><br/>Click here to <a href="viewalbum.php">View</a></div></div>
</div>";
}
}else{
?><div class="container">
<div class="header"><h1>Welcome to Our Site</h1></div>
<div class="form">
<h2>Album Form</h2>
<form name="registration" action="" method="post">
<input type="text" name="title" placeholder="Title" required />
<input type="text" name="typeofalbum" placeholder="Type eg: pop" required />
<input type="text" name="material" placeholder="Material eg:CD,DVD..ec" required />
<input type="text" name="price" placeholder="Price" required>
<input type="submit" name="submit" value="Submit" />
</form>
<br /><br />
</div>
<?php } ?>
<div class="footer"><h6>@copyrights- 2017</h6></div>
</div>
</body>
</html>
<html>
<head>
<title>View album</title>
<link rel="stylesheet" href="css/style.css" />
</head>
<body>
<div class="container">
<div class="header"><h1>Welcome to Our Site</h1></div>
<div class="form">
<?php
require('db.php');
$query = "SELECT * FROM album";
$result = mysqli_query($con, $query);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Title: " . $row["title"]. " - Type: ".$row["typeofalbum"]." - Material: ".$row["material"]." - Price: ".$row["price"]." <br>";
}
} else {
echo "0 results";
}?>
</div>
<div class="footer"><h6>@copyrights- 2017</h6></div>
</div>
</body>
</html>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Artist Form</title>
<link rel="stylesheet" href="css/style.css" />
</head>
<body>
<?php
require('db.php');
// If form submitted, insert values into the database.
if (isset($_REQUEST['artistname'])){
$artistname = stripslashes($_REQUEST['artistname']); // removes backslashes
$artistemail = stripslashes($_REQUEST['artistemail']);
$artistphone = stripslashes($_REQUEST['artistphone']);
$date = date("Y-m-d H:i:s");
$query = "INSERT into `artist` (artistname, artistemail, artistphone, date) VALUES ('$artistname', '$artistemail', '$artistphone', '$date')";
$result = mysqli_query($con,$query);
if($result){
echo "<div class='form'><h3>Your are artist details submited successfully.</h3><br/>Click here to <a href="login.php">Login</a></div>";
}
}else{
?><div class="container">
<div class="header"><h1>Welcome to Our Site</h1></div>
<div class="form">
<h2>Artist Form</h2>
<form name="registration" action="" method="post">
<input type="text" name="artistname" placeholder="Artist Name" required />
<input type="email" name="artistemail" placeholder="Artist Email" required />
<input type="text" name="artistphone" placeholder="Artist Phone" required />
<input type="submit" name="submit" value="Submit" />
</form>
<br /><br />
</div>
<?php } ?>
<div class="footer"><h6>@copyrights- 2017</h6></div>
</div>
</body>
</html>