CODEWITHSUNDEEP
phpsortingpdo
Claude Grecea
Claude Grecea

Reputation: 543

PDO Column Sort Not Sorting

When I click on the Username link below it is not sort and I am not sure why.

 if(isset($_GET['field'])) {

    $orderby = $_GET['field'];
$orderby2 = $_GET['sort'];

}else{
$orderby = "id";
$orderby2 = "ASC";
} if($_GET['sort'] == "ASC") {
    $sortby = "DESC";
}else{
    $sortby = "ASC";

}

Link to sort:

<th style="text-align: center;padding:10px;white-space: nowrap;" width="auto" class="rounded-company" scope="col"><a href="dash.php?field=user_name&sort=<?php echo $sortby;?>">Username</a></th>

if(isset($_REQUEST['txtKey'])) {

   $con = "%".$_REQUEST['txtKey']."%";
}

    $result = $db->dbh->prepare("SELECT * FROM ruj_users WHERE user_name like :textKey ORDER BY :order :order2");
    $result->bindValue(":textKey", isset($con) ? $con : null, PDO::PARAM_STR);
    $result->bindParam(":order", $orderby, PDO::PARAM_STR);
    $result->bindParam(":order2", $orderby2, PDO::PARAM_STR);
    $result->execute();
    $result = $result->fetchAll(PDO::FETCH_ASSOC);

    $result2 = $db->dbh->prepare("SELECT * FROM ruj_users WHERE user_name like :textKey ORDER BY :order :order2");
    $result->bindValue(":textKey", isset($con) ? $con : null, PDO::PARAM_STR);
    $result->bindParam(":order", $orderby, PDO::PARAM_STR);
    $result->bindParam(":order2", $orderby2, PDO::PARAM_STR);
    $result2->execute();
    $resultCount = $result2->rowCount();

    if(isset($_REQUEST['txtKey']))$str='&field='.$_GET['field'].'&sort='.$_GET['sort']."&txtKey=".$_REQUEST['txtKey'];

Please let me know what I am doing or if I am missing something.

Upvotes: 1

Views: 724

Answers (1)

Barmar
Barmar

Reputation: 781078

You can't use bindParam() to substitute column names, only expression values. So you have to use string interpolation:

$result = $db->dbh->prepare("SELECT * FROM ruj_users WHERE user_name like :textKey ORDER BY $orderby $orderby2);

Unfortunately, this opens you up to SQL injection, so you need to validate the inputs before doing this. E.g.

if (preg_match('/[^a-z0-1_]/i', $orderby)) {
    // report invalid sort field
}
if (!preg_match('/^(asc|desc)$/i', $orderby2)) {
    // report invalid sort direction
}

Upvotes: 2

Related Questions