So, I am trying to sort a database by clickable links. I have a function that is supposed to order them, but for some reason it isn't working. Also, once I add something to the database and submit, if I click on the links to order, it sends me to my actual function holding file in the url, which is weird.
Forgive table formatting issues. I'm still debugging that.
<!DOCTYPE html>
<html>
<head>
<meta charset='utf-8' />
<title>Project 1</title>
</head>
<body>
<h1 style='text-align:center;'>Movie Collection Database</h1>
<form method='post' action="<?php echo $_SERVER['PHP_SELF']; ?>">
<table align='center' border='1' cellpadding='5'>
<tr>
<td>
<input type="text" size='40' name="movieTitle" value="Movie Title">
</td>
<td>
<input type="text" name="studioName" value="Studio Name">
</td>
<td>
<select name="movieRating">
<option value="G">G</option>
<option value="PG">PG</option>
<option value="PG-13">PG-13</option>
<option value="R">R</option>
<option value="NC-17">NC-17</option>
<option value="Not Rated">Not Rated</option>
</select>
</td>
<td>
<input type="text" name="publicationYear" value="2015">
</td>
<td>
<input type="number" name="imdbRating" value="10.0">
</td>
<td>
<input type="number" name="runTime" value="0">
</td>
<td>
<input type="checkbox" name="add">Add
</td>
</tr>
</table>
<?php
$db = new PDO("mysql:host=localhost;dbname=berkleyr", "berkleyr", "12345");
?>
<?php include_once("Project1DB.php"); ?>
<?php if ($_SERVER['REQUEST_METHOD'] === "POST"):
delete($db);
if(isset($_POST['add']))
{
try
{
Insert($db, $_POST['movieTitle'], $_POST['studioName'], $_POST['movieRating'], $_POST['publicationYear'], $_POST['imdbRating'], $_POST['runTime']);
}
catch (PDOException $error)
{
$db->rollback();
echo "Bad things Happened";
$db = null;
die("Connection failed: " . $error->getMessage());
}
}
?>
<table align='center' border='1'>
<tr>
<th><a href="Project1DB.php?orderBy=title">Title</a></th>
<th><a href="Project1DB.php?orderBy=studio">Studio Name</a></th>
<th><a href="Project1DB.php?orderBy=rating">Rating</a></th>
<th><a href="Project1DB.php?orderBy=pub_year">Pub Year</a></th>
<th><a href="Project1DB.php?orderBy=imdb_rating">IMDB Rating</a></th>
<th><a href="Project1DB.php?orderBy=run_time">Run Time</a></th>
<th>Delete</th>
</tr>
<?php foreach (Select($db) as $row): ?>
<tr>
<td><?php echo $row['title']; ?></td>
<td><?php echo $row['studio']; ?></td>
<td><?php echo $row['rating']; ?></td>
<td><?php echo $row['pub_year']; ?></td>
<td><?php echo $row['imdb_rating']; ?></td>
<td><?php echo $row['run_time']; ?></td>
<?php echo "<td><input type='checkbox' name='". $row['id'] . "' value='" . $row['id'] . "'></td>";?>
</tr>
<?php endforeach ?>
<?php
if (isset($db))
{
$db = null; // make sure an exception didn't already close the connection. If not, close it now.
}
?>
<?php else: ?>
<table align='center' border="1">
<tr>
<th><a href="?orderBy=title">Title</a></th>
<th><a href="?orderBy=studio">Studio Name</a></th>
<th><a href="?orderBy=rating">Rating</a></th>
<th><a href="?orderBy=pub_year">Pub Year</a></th>
<th><a href="?orderBy=imdb_rating">IMDB Rating</a></th>
<th><a href="?orderBy=run_time">Run Time</a></th>
<th>Delete</th>
</tr>
<?php foreach (Select($db) as $row): ?>
<tr>
<td><?php echo $row['title']; ?></td>
<td><?php echo $row['studio']; ?></td>
<td><?php echo $row['rating']; ?></td>
<td><?php echo $row['pub_year']; ?></td>
<td><?php echo $row['imdb_rating']; ?></td>
<td><?php echo $row['run_time']; ?></td>
<?php echo "<td><input type='checkbox' name='". $row['id'] ."' value='" . $row['id'] . "'></td>";?>
</tr>
<?php endforeach ?>
<?php endif ?>
<td colspan="7" align="center">
<input type='submit' name='submit' value='Update Database' />
</td>
</table>
</form>
</body>
</html>
Here is my function for sorting:
function orderBy($order)
{
switch($order)
{
case "title":
$sorted = $this->db->prepare("SELECT * FROM movie ORDER BY movie.title ASC");
break;
case "studio":
$sorted = $this->db->prepare("SELECT * FROM movie ORDER BY movie.studio ASC");
break;
case "rating":
$sorted = $this->db->prepare("SELECT * FROM movie ORDER BY movie.rating ASC");
break;
case "pub_year":
$sorted = $this->db->prepare("SELECT * FROM movie ORDER BY movie.pub_year ASC");
break;
case "imdb_rating":
$sorted = $this->db->prepare("SELECT * FROM movie ORDER BY movie.imdb_rating ASC");
break;
case "run_time":
$sorted = $this->db->prepare("SELECT * FROM movie ORDER BY movie.run_time ASC");
break;
}
$sorted->execute();
}
