Kurt Leadley Kurt Leadley - 15 days ago 5
PHP Question

SELECT Statement PDO adding a variable to a ORDER BY clause

I am trying to add the option to choose what to order a SELECT statement by using a drop down list. My previously working SELECT statement would select the data from my table and just order by points. Now I want to be able to order by points, or goals, or assists etc etc.

Here is my previously working statement:

$query = $db->prepare("SELECT player_stats.*, teams.*
FROM player_stats
LEFT JOIN teams ON player_stats.tid = teams.teamid
WHERE season = :season
ORDER BY points DESC
LIMIT 20");
$query->bindParam(':season', $season);
$query->execute();
$result = $query->fetchAll();


Previously working Form:

<form action= "<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method="get" id="search">
<select name='statsYear' id='statsYear' class='dropDown' onchange='this.form.submit()'>
<option <?php if (($_GET['statsYear'] == '20162017') || !isset($_GET['statsYear'])) { ?>selected="true" <?php }; ?>value="20162017">2016-2017</option>
<option <?php if ($_GET['statsYear'] == '20152016') { ?>selected="true" <?php }; ?>value="20152016">2015-2016</option>
<option <?php if ($_GET['statsYear'] == '20142015') { ?>selected="true" <?php }; ?>value="20142015">2014-2015</option>
<option <?php if ($_GET['statsYear'] == '20132014') { ?>selected="true" <?php }; ?>value="20132014">2013-2014</option>
<option <?php if ($_GET['statsYear'] == '20122013') { ?>selected="true" <?php }; ?>value="20122013">2012-2013</option>
</select>
</form>


Here is my new SELECT stament that is giving the incorrect output. I will share a link to the incorrect output later on:

$query = $db->prepare("SELECT player_stats.*, teams.*
FROM player_stats
LEFT JOIN teams ON player_stats.tid = teams.teamid
WHERE season = :season
ORDER BY :statstype DESC
LIMIT 20");
$query->bindParam(':season', $season);
$query->bindParam(':statstype', $statsType);
$query->execute();
$result = $query->fetchAll();
echoTable($result);


The query above does not recognize the new
$statsType
variable. This is where I need help. Here is my new form and variable declaration for reference:


if (isset($_GET['statsType'])) {
$statsType = $_GET['statsType'];
} else {
$statsType = 'points' ;
}

<form action= "<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method="get" id="search">
<select name='statsYear' id='statsYear' class='dropDown' onchange='this.form.submit()'>
<option <?php if (($_GET['statsYear'] == '20162017') || !isset($_GET['statsYear'])) { ?>selected="true" <?php }; ?>value="20162017">2016-2017</option>
<option <?php if ($_GET['statsYear'] == '20152016') { ?>selected="true" <?php }; ?>value="20152016">2015-2016</option>
<option <?php if ($_GET['statsYear'] == '20142015') { ?>selected="true" <?php }; ?>value="20142015">2014-2015</option>
<option <?php if ($_GET['statsYear'] == '20132014') { ?>selected="true" <?php }; ?>value="20132014">2013-2014</option>
<option <?php if ($_GET['statsYear'] == '20122013') { ?>selected="true" <?php }; ?>value="20122013">2012-2013</option>
</select>
<select name="statsType" id="statsType" class="dropDown" onchange='this.form.submit()'>
<option <?php if ($_GET['statsType'] == 'points' || !isset($_GET['statsType'])) { ?>selected="true" <?php }; ?> value="points">Points</option>
<option <?php if ($_GET['statsType'] == 'goals') { ?>selected="true" <?php }; ?> value="goals">Goals</option>
<option <?php if ($_GET['statsType'] == 'assists') { ?>selected="true" <?php }; ?>value="assists">Assists</option>
<option <?php if (($_GET['statsType'] == 'pim') ) { ?>selected="true" <?php }; ?>value="pim">PIM</option>
<option <?php if ($_GET['statsType'] == 'pm') { ?>selected="true" <?php }; ?>value="pm">+/-</option>
<option <?php if (($_GET['statsType'] == 'toi') ) { ?>selected="true" <?php }; ?>value="toi">TOI</option>
</select>
</form>


This is what the query should look like if I select assists in the form:

correct output
This is the actual output:

http://sjsharktank.com/leaders.php?statsYear=20162017&statsType=assists

Answer

You can't use param for column .. you should use (eventually) string concatenationand build sql command dinamically

but be careful for sql injection

$query = $db->prepare("SELECT player_stats.*, teams.*
                           FROM player_stats
                           LEFT JOIN teams ON player_stats.tid = teams.teamid
                           WHERE season = :season
                           ORDER BY  " . $statstype  . " DESC
                           LIMIT 20");

if you can use double quote for nestings quote limit use alway the same

$query = $db->prepare('SELECT player_stats.*, teams.*
                           FROM player_stats
                           LEFT JOIN teams ON player_stats.tid = teams.teamid
                           WHERE season = :season
                           ORDER BY  ' . $statstype  . ' DESC
                           LIMIT 20');

you can also work with double quote and php magic behavior for vars

$query = $db->prepare("SELECT player_stats.*, teams.*
                           FROM player_stats
                           LEFT JOIN teams ON player_stats.tid = teams.teamid
                           WHERE season = :season
                           ORDER BY  $statstype   DESC
                           LIMIT 20");