user3062910
Reputation: 13
Trying to sort table of data from mysql table in php
Im not sure as to why my code wont sort, im pretty new to this and any help would be appreciated.
This is the file that reads in the content and then is suppose to sort it, but when i execute it on the browser the link to sort the data have no affect.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Movie Reviews Display</title>
</head>
<body>
<?php
$host="XXXXXXX";
$username="XXXXXX"; // Mysql username
$password="XXXXXXX"; // Mysql password
$db_name="XXXXXXXX"; // Database name
$tbl_name="movies"; // Table name
$con=mysqli_connect("$host", "$username", "$password")or die("cannot connect");
mysqli_select_db($con,"XXXXXX") or die ("no database");
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT * FROM movies");
?>
<h1>Movie Reviews Database</h1>
<hr />
<p />
<?php
echo "<table border='1'>
<tr>
<th>Review Date</th>
<th>Title</th>
<th>Rating</th>
<th>Genre</th>
<th>Release Date</th>
<th>Reviewer Name</th>
<th>Review Rateing</th>
<th>Review Text</th>
</tr>";
while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['reviewDate'] . "</td>";
echo "<td>" . $row['title'] . "</td>";
echo "<td>" . $row['rating'] . "</td>";
echo "<td>" . $row['genre'] . "</td>";
echo "<td>" . $row['releaseDate'] . "</td>";
echo "<td>" . $row['ReviewerName'] . "</td>";
echo "<td>" . $row['reviewRating'] . "</td>";
echo "<td>" . $row['reviewText'] . "</td>";
echo "</tr>";
}
echo "</table>";
if(isset($_GET['sort'])){
switch ($_GET['sort'] ){
case 0:
$query = "SELECT * FROM movies ORDER BY reviewDate DESC";
mysqli_query($con,$query);
break;
case 1:
$query = 'SELECT * FROM movies ORDER BY title DESC';
mysqli_query($con,$query);
break;
case 2:
$query = 'SELECT * FROM movies ORDER BY rating DESC';
mysqli_query($con,$query);
break;
case 3:
$query = 'SELECT * FROM movies ORDER BY genre DESC';
mysqli_query($con,$query);
break;
case 4:
$query = 'SELECT * FROM movies ORDER BY releaseDate DESC';
mysqli_query($con,$query);
break;
case 5:
$query = 'SELECT * FROM movies ORDER BY ReviewerName DESC';
mysqli_query($con,$query);
break;
case 6:
$query = 'SELECT * FROM movies ORDER BY reviewRating DESC';
mysqli_query($con,$query);
break;
}
}
mysqli_close($con);
?>
<p />
Sort by...
<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=0";?>">Review Date</a>
<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=1";?>">Title</a>
<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=2";?>">Rating</a>
<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=3";?>">Genre</a>
<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=4";?>">Release Date</a>
<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=5";?>">Reviewer</a>
<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=6";?>">Reviewer Rating</a>
</body>
</html>
Upvotes: 0
Views: 7585
Answers (2)
transilvlad
Reputation: 14532
I would have made it a lot more complex, but I kept your general idea and improved upon.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Movie Reviews Display</title>
</head>
<body>
<?php
$host = "XXXXXXX";
$username = "XXXXXX";
$password = "XXXXXXX";
$db_name = "XXXXXXXX";
$tbl_name = "movies";
$con = mysqli_connect($host, $username, $password);
if(mysqli_connect_errno())
$con = false;
else
$cdb = mysqli_select_db($con, $db_name);
if(!$con) {
echo "Failed to connect to database server!";
else if(!$cdb) {
echo "Failed to select database!";
else {
echo " <h1>Movie Reviews Database</h1>
<hr />
<p />
<table border='1'>
<tr>
<th>Review Date</th>
<th>Title</th>
<th>Rating</th>
<th>Genre</th>
<th>Release Date</th>
<th>Reviewer Name</th>
<th>Review Rateing</th>
<th>Review Text</th>
</tr>\r\n";
$sort = "";
if(isset($_GET['sort'])) {
switch ($_GET['sort'] ) {
case 0:
$sort = " ORDER BY reviewDate DESC";
break;
case 1:
$sort = ' ORDER BY title DESC';
break;
case 2:
$sort = ' ORDER BY rating DESC';
break;
case 3:
$sort = ' ORDER BY genre DESC';
break;
case 4:
$sort = ' ORDER BY releaseDate DESC';
break;
case 5:
$sort = ' ORDER BY ReviewerName DESC';
break;
case 6:
$sort = ' ORDER BY reviewRating DESC';
break;
}
}
$result = mysqli_query($con, "SELECT * FROM `movies`" . $sort);
while($row = mysqli_fetch_array($result)) {
echo " <tr>\r\n";
echo " <td>" . $row['reviewDate'] . "</td>\r\n";
echo " <td>" . $row['title'] . "</td>\r\n";
echo " <td>" . $row['rating'] . "</td>\r\n";
echo " <td>" . $row['genre'] . "</td>\r\n";
echo " <td>" . $row['releaseDate'] . "</td>\r\n";
echo " <td>" . $row['ReviewerName'] . "</td>\r\n";
echo " <td>" . $row['reviewRating'] . "</td>\r\n";
echo " <td>" . $row['reviewText'] . "</td>\r\n";
echo " </tr>\r\n";
}
echo " </table>\r\n";
}
mysqli_close($con);
?>
<p />Sort by...
<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=0";?>">Review Date</a>
<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=1";?>">Title</a>
<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=2";?>">Rating</a>
<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=3";?>">Genre</a>
<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=4";?>">Release Date</a>
<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=5";?>">Reviewer</a>
<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=6";?>">Reviewer Rating</a>
</body>
</html>
Upvotes: 1
Dave
Reputation: 3658
Arrays are your friend...
$sortable = array(0 => 'reviewDate',
1 => 'title',
2 => 'rating',
3 => 'genre',
4 => 'releaseDate',
5 => 'ReviewerName',
6 => 'reviewRating');
$sortBy = $sortable[1];
if ( isset($_GET['sort']) && isset($sortable[$_GET['sort']]) ){
$sortBy = $sortable[$_GET['sort']];
}
$result = mysqli_query($con,"SELECT * FROM movies ORDER BY ".$sortBy);
Upvotes: 0
Related Questions
- sorting table row data with php
- How to sort rows of HTML table that are called from MySQL
- How to sort <table> data with PHP
- PHP loop to sort table
- Sorting a HTML table with PHP after input from database
- Sorting data from MySQL and inserting it into a HTML Table
- Having difficulties sorting data from mysql query using php
- sort rows of html table - mysql
- How to best sort data from MySQL table in PHP script?
- Sorting MySQL with PHP