PHP ORDER BY with a variable

Question

I am a beginner in php and am having some trouble changing the ORDER BY with a variable. I have tried to research and get it figured out, but with no luck. I am wanting the form name "filter" to pass the option name into the php variable "filter" then order by the "filter" variable in the mysql select query. What am I missing here?

Here is the code:

per recommendations I have edited the code and posted the edits.

    <center><h2> Saved Weapons List</h2>

<form name="filter" action="" method="post">
    <select name="filter">
        <option value="weaponType"> Weapon Type</option>
        <option value="weaponCategory"> Weapon Category</option>
    </select>
    <input type="submit">
</form>
</center>

<?php
$con=mysqli_connect("localhost","username","pass","db_name");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$filter = $_POST['filter'];

$result = mysqli_query($con,"SELECT * FROM weapons ORDER BY '{$filter}' desc");



while($row = mysqli_fetch_array($result))
  {
$row['masterwork'] = ( intval( $row['masterwork']) == 1) ? "Yes" : "No";
  echo "<center>";
  echo "<table border='1' class='display'>";
  echo "<tr>";
  echo "<td>Weapon Name: </td>";
  echo "<td>" . $row['weaponName'] . "</td>";
  echo "</tr>";
  echo "<tr>";
  echo "<td>Creator: </td>";
  echo "<td>" . $row['creator'] . "</td>";
  echo "</tr>";
  echo "<tr>";
  echo "<td>Weapon Category: </td>";
  echo "<td>" . $row['weaponCategory'] . "</td>";
  echo "</tr>";
  echo "<tr>";
  echo "<td>Weapon Sub-Category: </td>";
  echo "<td>" . $row['weaponSubCategory'] . "</td>";
  echo "</tr>";
  echo "<tr>";
  echo "<td>Cost: </td>";
  echo "<td>" . $row['costAmount'] . " " . $row['costType'] . "</td>";
  echo "</tr>";
  echo "<tr>";
  echo "<td>Damage(S): </td>";
  echo "<td>" . $row['damageS'] . "</td>";
  echo "</tr>";
  echo "<tr>";
  echo "<td>Damage(M): </td>";
  echo "<td>" . $row['damageM'] . "</td>";
  echo "</tr>";
  echo "<tr>";
  echo "<td>Critical: </td>";
  echo "<td>" . $row['critical'] . "</td>";
  echo "</tr>";
  echo "<tr>";
  echo "<td>Range Increment: </td>";
  echo "<td>" . $row['rangeIncrement'] . "</td>";
  echo "</tr>";
  echo "<tr>";
  echo "<td>Weight: </td>";
  echo "<td>" . $row['weight'] . "</td>";
  echo "</tr>";
  echo "<tr>";
  echo "<td>Weapon Type: </td>";
  echo "<td>" . $row['weaponType'] . "</td>";
  echo "</tr>";
  echo "<tr>";
  echo "<td>Masterwork: </td>";
  echo "<td>" . $row['masterwork'] . "</td>";
  echo "</tr>";
  echo "<tr>";
  echo "<td>Attributes: </td>";
  echo "<td>" . $row['attributes'] . "</td>";
  echo "</tr>";
  echo "<tr>";
  echo "<td>Special Abilities: </td>";
  echo "<td>" . $row['specialAbilities'] . "</td>";
  echo "</tr>";
  echo "<tr>";
  echo "<td>Additional Info: </td>";
  echo "<td>" . $row['additionalInfo'] . "</td>";
  echo "</tr>";
  }
echo "</table>";
echo "</center>";

mysqli_close($con);
?>

Do you have the constant filter defined, or did you actually mean $_POST['filter'] (note the quotes)? — Wrikken
– Wrikken, Commented Feb 12, 2014 at 21:00
Well the filter will change based on the selected option in the html form on the top. — doriansm
– doriansm, Commented Feb 12, 2014 at 21:01

cyadvert · Accepted Answer · 2014-02-13 02:02:31Z

2

Multiple updates (mainly for security reasons. as @Wrikken wrote in the comments - your code is ASKING for injections).

First. Change option values to something (digits may be), and then check it in PHP.

    <option value="filter1"> Weapon Type</option>
    <option value="filter2"> Weapon Category</option>

And then filter it in PHP

$filter = 'weaponType';

switch($_POST["filter"]) {
case 'filter2': $filter = 'weaponCategory'; break;
}

Second. if $filter set - run the query...

if (isset($filter)) {
    $result = mysqli_query($con,"SELECT * FROM weapons ORDER BY " . $filter . " desc");
    while($row = mysqli_fetch_array($result))
    {
    /* output */
    }
}

edited Feb 13, 2014 at 2:02

answered Feb 12, 2014 at 21:25

cyadvert

8757 silver badges19 bronze badges

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

2 Comments

doriansm Over a year ago

Thank you this did it! One more thing if you are up for it. Now when I initially load the page, it displays nothing. Once I select a filter and press submit it does display properly. How could I get the page to load a sort of default query when you first go to it?

cyadvert Over a year ago

in that case you can just set $filter to a default value ( let's say weaponType). $filter = 'filter1'; And so all requests will be ordered either by default value or by passed patamether.

Harry Dobrev · Accepted Answer · 2014-02-12 20:57:24Z

1

You need to filter by the name of the column, not by the value of the variable.

Try:

$filter = $_POST['filter'];

$result = mysqli_query($con,"SELECT * FROM weapons ORDER BY `{$filter}` desc");

answered Feb 12, 2014 at 20:57

Harry Dobrev

7,7172 gold badges50 silver badges67 bronze badges

11 Comments

Sergiu Paraschiv Over a year ago

For a clear disambiguation of MySQL quotes read stackoverflow.com/questions/11321491/…

doriansm Over a year ago

After changing to this the results went blank after the form.

Wrikken Over a year ago

Make damn sure you whitelist that $filter against a known good list. otherwise it's begging for sql injection.

Sergiu Paraschiv Over a year ago

Also, the correct usage is <option value="...">...</option> and NOT <option name="...">...</option>.

Harry Dobrev Over a year ago

@Aldentec there is some kind of a PHP error. I've changed to $_POST['filter']. You should turn on error reporting and debug more. On a separate note it is very useful to use prepared statements, to use ORM and generally to always escape every outside value which reaches the database.

|

Collectives™ on Stack Overflow

PHP ORDER BY with a variable

Here is the code:

per recommendations I have edited the code and posted the edits.

2 Answers 2

2 Comments

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Here is the code:

per recommendations I have edited the code and posted the edits.

2 Answers 2

2 Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related