Trying to sort table of data from mysql table in php

Question

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>

perhaps if you actually ran the query with the order by clause BEFORE you spit out the results... — Marc B
– Marc B, Commented Dec 3, 2013 at 19:27

transilvlad · Accepted Answer · 2013-12-03 19:41:52Z

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>

Dave · Accepted Answer · 2013-12-03 19:34:19Z

0

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);

answered Dec 3, 2013 at 19:34

Dave

3,6581 gold badge18 silver badges9 bronze badges

1 Comment

user3062910 Over a year ago

Im still running into the problem that when I go to try it in my browser no sorting happens...

Collectives™ on Stack Overflow

Trying to sort table of data from mysql table in php

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related