PHP PDO: SQL query does not return the expected result

Question

I have a function (see below) in PHP that queries a MySQL database. When I use the following values:

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

Using the SQL statement:

SELECT user, scoreVal AS score, UNIX_TIMESTAMP(timestamp) AS timestamp FROM Score WHERE timestamp >= :from AND timestamp <= :to AND map = :map ORDER BY scoreVal DESC, timestamp ASC LIMIT :limit 

In phpMyAdmin, I get the following result:

However, PHP PDO gets an empty array.

My debugging attempts so far are:

• I replaced his prepared SQL query with static values ​​instead of placeholders. Returns correctly.
• Attempting each placeholder separately, replacing the rest with verified hard-coded values, returns nothing
• Instead of passing variables to placeholders, I pass fixed constants in the execute (Array ()) part. - returns nothing.
• I also found, after turning on mySQL query logs, that the PHP Connects client, but then shuts down without sending any queries.

From this, I believe that this is a problem with the place owners in the function, however, I could not find the reason why they fail. Most likely, this happens on the PHP side, since MySQL does not throw an error into the error file.

This is the function I use with the variables passed in:

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

Functions:

 /** * Gets the highscore list for the map in that timespan * @param integer $map Id of map for which to fetch the highscore * @param integer $limit Maximum no. of records to fetch * @param integer $from Timestamp from when to find rank * @param integer $to Timestamp up to when to find rank * @return array Array of highscores arranged by rank for the map in the format [{"user"=>$user,"score"=>score,"timestamp" => timestamp}] */ function get_highscore_list($map,$limit,$from,$to){ $sql = "SELECT user,scoreVal AS score,UNIX_TIMESTAMP(timestamp) AS timestamp FROM Score WHERE timestamp >= :from AND timestamp <= :to AND map = :map ORDER BY 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

---

MySQL table format:

---

I tried to print $ conn-> errorInfo (); however, since the error does not occur, I return an array of values: [00000, NULL, NULL]

---

The request_connection function returns the result of this function, and it works for all my other operators.

 /** * Creates a new PDO connection to the database specified in the configuration file * @author Ignacy Debicki * @return PDO A new open PDO connection to the 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 to initiate connection",102,$e); } } 

thanks

Answer 1

After many hours of trying, I finally found my solution.

The two important statements that I skipped from creating my connection are as follows:

 $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 

which include error reporting (see fooobar.com/questions/109952 / ... ).

As soon as I turned it on, it was trivial to catch my problem, which was caused by rewriting the parameter: to, if the value of $ to is 0, the number of parameters passed in the $conn->execute() instruction was incompatible with the number of parameters in the sql query.

My solution was to use $conn->bindValue() for each parameter instead, using an if statement to check if it was bound to the: to parameter. Below is my solution:

 function get_highscore_list($map,$limit,$from,$to){ $sql='SELECT user, scoreVal AS score, UNIX_TIMESTAMP(timestamp) AS timestamp FROM Score WHERE map = :map AND timestamp >= :from AND timestamp <= :to ORDER BY 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; }