CODEWITHSUNDEEP
phpmysqldatabasesorting
Rataiczak24
Rataiczak24

Reputation: 1042

Sorting with MySQL and PHP

So right now I have my MySql database on phpmyadmin connected to my PHP script. It lists 50 different NFL players and their stats from last year. I would like to be able to list a dropdown box to where I can sort the players by any of the categories (i.e. Receptions, Rec Yds, TDs, etc.) but am not sure how I would do this..?? I have a switch statement in there but it doesn't seem to be doing anything right now.

<!DOCTYPE html>
<html>
<!-- Seth Rataiczak -->
<head>
        <title>PHP Project</title>
        <style>
            table,th,td {
                border:1px solid navy;
                }
            body {
            background-color:peachpuff;
            }
        </style>
</head>

<body>

<?php
// database connection
    $db_hostname='localhost';
    $db_username='root';
    $db_password='';
    $db_database='Project';

    $connection = new mysqli(   $db_hostname,
                                $db_username,
                                $db_password,
                                $db_database);

//MySQL Select Statement

    $sort = "";
    if(isset($_GET['sort'])) {
        switch ($_GET['sort'] ) {
            case 0:
                $sort = ' ORDER BY Team DESC';
                break;
            case 1:
                $sort = ' ORDER BY Pos DESC';
                break;
            case 2:
                $sort = ' ORDER BY Rec DESC';
                break;
            case 3:
                $sort = ' ORDER BY Yds DESC';
                break;
            case 4:
                $sort = ' ORDER BY Avg DESC';
                break;
            case 5:
                $sort = ' ORDER BY Yds/G DESC';
                break;
            case 6:
                $sort = ' ORDER BY TD DESC';
                break;
        }
    }    

        $sql = "SELECT * FROM NFL_2014_Receiving WHERE Field=1" . $sort;
        $result = $connection->query($sql);
        if (!$result) die ($connection->error);
        $n = $result->num_rows;

        $nfl = array();

// echos the table headers
        echo "<table>
            <tr><th>ID</th><th>Player</th><th>Team</th>
            <th>Position</th><th>Receptions</th>
            <th>Receiving Yards</th><th>Avg Yds/Catch</th>
            <th>Avg Yds/Game</th><th>Touchdowns</th></tr>";

// echos the table data
        while ($row = $result->fetch_array(MYSQLI_ASSOC)){
            $nfl[$row['iD']] = $row['Player'];
            if(!isset($_POST['hide']) || $_POST['hide'] != $row['iD']){
                echo "<tr><td width=20>" . $row['iD'] . "</td><td width=150>" . $row['Player'] . "</td><td width=40>" .
                        $row['Team'] . "</td><td width=30>" . $row['Pos'] . "</td><td width=30>" .
                        $row['Rec'] . "</td><td width=40>" . $row['Yds'] . "</td><td width=30>" .
                        $row['Avg'] . "</td><td width=40>" . $row['Yds/G'] . "</td><td width=20>" .
                        $row['TD'] . "</td></tr>";
            }
        }
        echo "</table><br>";

//dropdown box
        echo "<form method='post' action='index.php'><select name='hide'>";
        foreach($nfl as $key=>$value){
            echo "<option value='".$key."'>".$value."</option>";
        }
// submit button
        echo "<input type='submit' value='Submit'>";
        echo "</select></form>";

?>

</body>
</html>

Upvotes: 1

Views: 82

Answers (2)

danteinzu
danteinzu

Reputation: 82

  • the input submit is inside of select tag. must be after select, not inside.
  • the method of form must be equal to the parameter received, in this case must be GET.
  • the name of the select must be 'sort' instead of 'hide'

Upvotes: 1

codegaze
codegaze

Reputation: 755

You are setting a POST method action and you are searching GET for value.

To be clear: You can change your form method to get <form method='get' action='index.php'> or change your php value $_GET['sort'] to $_POST['sort']

I m quite sure this is your problem but you can echo your $sql variable to see what your query does ;)

Upvotes: 2

Related Questions