Fahad Kazmi Fahad Kazmi - 4 months ago 29
PHP Question

Unable to run named placeholder for order by ASC in php pdo

I am new to PHP PDO and trying to use named placeholder at the place of ORDER BY ASC. Sometime in simple query page this run very successfully but unable to run in the following query:

PHP CODE IS:

$price_sort = "ASC";
$keyword = "samsung glaxy";
$limit = 0;
$query = $db->prepare("SELECT *, MATCH(title) against (:keyword) as 'relevence'
FROM view_store_items_grid
WHERE MATCH(title) against(:keyword)
ORDER BY relevence DESC, price :order
LIMIT :limit,25");
$query->bindValue(":keyword",$keyword);
$query->bindValue(":order",$price_sort);
$query->bindValue(":limit", $limit, PDO::PARAM_INT);
$query->execute();


When I remove placeholder
:order
with
ASC
this run and gives result but when I use this placeholder, I get empty result.

For Fetching data or to show fetched result I am using

while ($row = $query->fetch(PDO::FETCH_ASSOC)) :
extract($row);
echo "$name";
endwhile;


What I am doing wrong and How I can use many named placeholder in query at different places?

Answer

You should not bind ASC/DESC in prepared statement. Parameters are automatically quoted, and ASC/DESC shouldn't be quoted. this is the same reason that table and column names can't be parameters.

Instead you can do like that

$sql_query = "SELECT *, MATCH(title) against (:keyword) as 'relevence'
                FROM view_store_items_grid 
                WHERE MATCH(title) against(:keyword) 
                ORDER BY relevence DESC, price "
if($price_order == 'ASC'){
    $sql_query .= " ASC "
}else{
    $sql_query .= " DESC  "
}
$sql_query .= " LIMIT :limit,25  "
$query = $db->prepare($sql_query);

Refer How bindValue in LIMIT

Refer pdo binding asc/desc order dynamically

Also do not try to use the same named parameter twice in a single SQL statement, for example

<?php 
$sql = 'SELECT * FROM some_table WHERE  some_value > :value OR some_value < :value'; 
$stmt = $dbh->prepare($sql); 
$stmt->execute( array( ':value' => 3 ) ); 
?> 

...this will return no rows and no error -- you must use each parameter once and only once. Apparently this is expected behavior (according to this bug report: http://bugs.php.net/bug.php?id=33886) because of portability issues.

Comments