PHP post submit values for SQL statement

Question

So I'm trying to sort an SQL query based on which submit button is clicked

My Sort options:

echo "<form method='post' action adminview.php'>"
    ."<input type='submit' name='sort' value='Sort by Attempt ID'/><br />"
    ."<input type='submit' name='sort' value='Sort by Student ID'/><br />"
    ."<input type='submit' name='sort' value='Sort by lowest Score'/><br />"
    ."<input type='submit' name='sort' value='Sort by highest Score'/><br />"
    ."</form>";

And when the page reloads here is the code to check which button was clicked

switch($_POST['sort'])
{
    case "Sort by Attempt ID":
        $sortby = "attempt_id";
        break;
    case "Sort by Student ID":
        $sortby = "student_id";
        break;
    case "Sort by lowest Score":
        $sortby = "score";
        break;
    case "Sort by highest Score":
        $sortby = "score desc";
        break;
}

My issue is that when the page reloads it says that sort is undefined.

If you were curious here is the SQL statement

$sqlstring = "select * from quizattempts order by '$sortby'";

before switch add echo '<pre>';print_r($_POST); - this will give you clue — Iłya Bursov
– Iłya Bursov, Commented Nov 3, 2013 at 5:51
$sqlstring = "select * from quizattempts order by '" . $sortby. "' "; would have probably worked. — Funk Forty Niner
– Funk Forty Niner, Commented Nov 3, 2013 at 15:44

Mureinik · Accepted Answer · 2013-11-03 05:40:02Z

2

The single quotes are preventing $sortby from being evaluated. Just remove them:

$sqlstring = "select * from quizattempts order by $sortby";

answered Nov 3, 2013 at 5:40

Mureinik

316k54 gold badges399 silver badges405 bronze badges

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

2 Comments

Brendon Rother Over a year ago

this worked even when it is more than one work, maybe it is because my query is simple, I'm not sure but thank you so much Mureinik!!

Rajesh Paul Over a year ago

@BrendonRother This actually worked because there is no multi-word attribute but you may sometimes have multi-word attributes in the database. In that case it is bound to fail. Check my update.

Krish R · Accepted Answer · 2013-11-03 06:22:37Z

Can you please try this,

<?php 
      echo "<form method='post' action='adminview.php'>"
           ."<input type='submit' name='sort' value='Sort by Attempt ID'/><br />"
           ."<input type='submit' name='sort' value='Sort by Student ID'/><br />"
           ."<input type='submit' name='sort' value='Sort by lowest Score'/><br />"
           ."<input type='submit' name='sort' value='Sort by highest Score'/><br />"
          ."</form>";

        if(isset($_POST['sort'])){

            switch($_POST['sort'])
            {
                case "Sort by Attempt ID":
                    $sortby = "attempt_id";
                    break;
                case "Sort by Student ID":
                    $sortby = "student_id";
                    break;
                case "Sort by lowest Score":
                    $sortby = "score";
                    break;
                case "Sort by highest Score":
                    $sortby = "score desc";
                    break;
            }

            $OrderBy = "order by $sortby ";
        }

         echo $sqlstring = "select * from quizattempts $OrderBy";

Rajesh Paul · Accepted Answer · 2013-11-03 13:50:10Z

0

instead of single quote(') you need to use grave accent(`).May be in this case you don't need it because it is certain that the attributes are of single word. But when you are not sure whether the attributes are of multiple words or single word you should always use the grave accent.

Syntax

select * from tab_name order by `attribute name` [ordering=asc/desc];

So use the following:

$ordering="asc";
switch($_POST['sort'])
{
    case "Sort by Attempt ID":
        $sortby = "attempt_id";
        break;
    case "Sort by Student ID":
        $sortby = "student_id";
        break;
    case "Sort by lowest Score":
        $sortby = "score";
        break;
    case "Sort by highest Score":
        $sortby = "score";
        $ordering="desc";
        break;
}
$sqlstring = "select * from quizattempts order by `$sortby` $ordering";

Here I have specified the ordering in a separate variable to keep it out of ambiguity.

edited Nov 3, 2013 at 13:50

answered Nov 3, 2013 at 6:52

Rajesh Paul

7,0376 gold badges45 silver badges58 bronze badges

2 Comments

Brendon Rother Over a year ago

you solution did work for everything except when $sortby = score desc. However the first answer given by Mureinik worked

Rajesh Paul Over a year ago

@BrendonRother Sorry for that but in case of $sortby = "score desc" score desc is not an attribute name and hence didn't work. I specified the use of grave accent(`) to show you the ideal way to use order by with any type of attribute name(multiword or single-word). But according to the particular state of your code i.e. you merged the attribute name with the ordering sequence. That's I think simple but not a smart way to do it in a global approach. Updating my answer. Check it if it solves your issue.

Collectives™ on Stack Overflow

PHP post submit values for SQL statement

3 Answers 3

2 Comments

Comments

Syntax

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Syntax

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related