Sorting with MySQL and PHP

Question

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>

@JayBlanchard no I'm not sure. I'm kind of stuck at this point in my code — Rataiczak24
– Rataiczak24, Commented May 4, 2015 at 19:08

codegaze · Accepted Answer · 2015-05-04 19:21:57Z

2

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

edited May 4, 2015 at 19:21

answered May 4, 2015 at 19:07

codegaze

7557 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rataiczak24 Over a year ago

so you're saying that I should change it so I am searching for a POST value instead of a GET value?

danteinzu · Accepted Answer · 2015-05-04 19:41:25Z

1

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'

answered May 4, 2015 at 19:41

danteinzu

825 bronze badges

3 Comments

codegaze Over a year ago

much better answer! I didn't even notice!

Rataiczak24 Over a year ago

@DanteJavier I switched it up so the input submit is outside the select tag. I also changed it so everything is GET instead of POST. However, if i change it to 'sort' instead of 'hide' then it does not hide the player anymore when selected which is what I need. I need to get both where I can select a player and hide him when selected but also be able to choose from a dropdown box how I want to sort the table.

danteinzu Over a year ago

then change $_GET['sort'] to $_GET['hide']

Collectives™ on Stack Overflow

Sorting with MySQL and PHP

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related