mysql - PHP PDO: SQL query not returning expected result -

i have function (see bottom) in php, queries mysql database. when use following values:

• $map => 1,
• $limit => 10,
• $from => 0,
• $to => current_timestamp

with sql statement:

select user,        scoreval score,        unix_timestamp(timestamp) timestamp    score   timestamp >= :from     , timestamp <= :to     , map = :map   order scoreval desc, timestamp asc   limit :limit 

in phpmyadmin, following result:

however php pdo gets returned empty array.

my attempts debug far:

• i have replaced prepared sql query static values instead of placeholders - returns correctly
• trying each placeholder separately, replacing rest tested hard-coded values - returns nothing
• instead of passing variables placeholders pass fixed constants in execute(array()) part. - returns nothing.
• i have furthermore discovered after turning on mysql query logs, php client connects, quits without sending queries.

from this, believe problem place holders within function, have been unable find reason why failing. occurring on php side, no errors being thrown mysql error file.

this function using, variables being passed in:

• $map => 1,
• $limit => 10,
• $from => 0,
• $to => 0

function:

/**  * gets highscore list map in timespan  * @param  integer $map   id of map fetch highscore  * @param  integer $limit maximum no. of records fetch  * @param  integer $from  timestamp when find rank  * @param  integer $to    timestamp when find rank  * @return array   array of highscores arranged rank map in format [{"user"=>$user,"score"=>score,"timestamp" => timestamp}]  */ function get_highscore_list($map,$limit,$from,$to){     $sql = "select user,scoreval score,unix_timestamp(timestamp) timestamp score timestamp >= :from , timestamp <= :to , map = :map order scoreval desc, timestamp asc limit :limit";     if ($to==intval(0)){         $max =1;         $sql = str_replace(":to","now()",$sql,$max);     }     try{     $conn = request_connection();     $stmt = $conn->prepare($sql);     $stmt->execute(array(':map'=>$map,':from'=>$from,':limit'=>$limit));     $result = $stmt->fetchall(pdo::fetch_assoc);     }catch(pdoexception $e){         $_post["exception"]=$e;         continue;     }     return $result; } 

edits

---

format of mysql table:

---

i have tried outputting $conn->errorinfo();, no error being thrown, returned array of values: [00000,null,null]

---

the request_connection function returns result of function, , working of other statements.

/**  * creates new pdo connection database specified in configuration file  * @author ignacy debicki  * @return pdo new open pdo connection database  */ function create_connection(){     try {         $config = parse_ini_file('cadb.ini');         $conn = new pdo('mysql' . ':host=' . $config['dbhost'] . ';dbname=' . $config['db'],$config['dbphpuser'], $config['dbphppass']);         date_default_timezone_set($config['dbtimezone']);         return $conn;     } catch(pdoexception $e){         throw new exception("failed initiate connection",102,$e);     }    } 

thanks

after many hours of trying, have found solution.

two important statements had missed out creating connection are:

$conn->setattribute(pdo::attr_emulate_prepares, false); $conn->setattribute(pdo::attr_errmode, pdo::errmode_exception); 

which turn on error reporting (see https://stackoverflow.com/a/8776392/2891273).

once turned on, trivial catch problem, due overwriting of :to parameter if $to 0, number of parameters passed in $conn->execute() statement mismatched number of parameters in sql query.

my solution use $conn->bindvalue() each parameter instead, using if statement check if bind :to parameter. below solution:

function get_highscore_list($map,$limit,$from,$to){     $sql='select user, scoreval score, unix_timestamp(timestamp) timestamp score map = :map , timestamp >= :from , timestamp <= :to order scoreval desc, timestamp asc limit :limit';     if ($to==0){         $sql = str_replace(":to",'current_timestamp()',$sql);     }     $conn = request_connection();     $stmt = $conn->prepare($sql);     $stmt->bindvalue(':map',$map,pdo::param_int);     $stmt->bindvalue(':from',$from,pdo::param_int);     if ($to!=0){         $stmt->bindvalue(':to',$to,pdo::param_int);     }     $stmt->bindvalue(':limit',$limit,pdo::param_int);     $stmt->execute();     $result = $stmt->fetchall(pdo::fetch_assoc);     return $result; }