1

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);
?>
Community
  • 1
  • 1
doriansm
  • 247
  • 1
  • 5
  • 31

2 Answers2

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 */
    }
}
cyadvert
  • 855
  • 7
  • 19
  • 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? – doriansm Feb 12 '14 at 21:31
  • 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. – cyadvert Feb 12 '14 at 23:23
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");
Haralan Dobrev
  • 7,617
  • 2
  • 48
  • 66
  • For a clear disambiguation of MySQL quotes read http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks – Sergiu Paraschiv Feb 12 '14 at 20:58
  • After changing to this the results went blank after the form. – doriansm Feb 12 '14 at 20:58
  • Make damn sure you whitelist that `$filter` against a known good list. otherwise it's begging for sql injection. – Wrikken Feb 12 '14 at 20:59
  • 2
    Also, the correct usage is `` and NOT ``. – Sergiu Paraschiv Feb 12 '14 at 21:00
  • @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. – Haralan Dobrev Feb 12 '14 at 21:02
  • I changed the options to value="..." still no luck – doriansm Feb 12 '14 at 21:07
  • @Aldentec Try with `error_reporting(E_ALL);` in the beginning. This way you could see the error. – Haralan Dobrev Feb 12 '14 at 21:11
  • @HaralanDobrev Please excuse my ignorance but what am I supposed to do with that? – doriansm Feb 12 '14 at 21:16
  • @Aldentec As I mentioned above, the blank page is most probably caused by a PHP error which is not reported. By turning on the error reporting you would actually see where it the error during the execution and could fix it yourself. This has nothing to do with the SQL query though. – Haralan Dobrev Feb 12 '14 at 21:17
  • Oh the page was not completely blank. Basically just the results of the mysql query were not showing up. Sorry I should have been more clear. – doriansm Feb 12 '14 at 21:20
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/47359/discussion-between-haralan-dobrev-and-aldentec) – Haralan Dobrev Feb 12 '14 at 21:20