0

The SQL syntax doesnot have anything wrong but it has something wrong it feels like it isn't getting inserted in SQL.

function getAllStudentsDetails($param){

    $sql = "SELECT * FROM students LIMIT ?,?";
    $data = Db::query($sql,$param);
    return Db::fetchObject($data);
}

While this code returns values if I simply typed

    $sql = "SELECT * FROM students LIMIT $param[0],$param[1];

Am I doing something wrong here? By the way Db::query($sql,$param); returns value for all other function.

Custom functions:

static function query($sql, $values=null) { 
     $stmt = self::$conn->prepare($sql); 
     $stmt->execute($values); 
     return $stmt;
} 

static function fetchObject($result) { 
     if ($result) { 
          $result = $result->fetchAll(PDO::FETCH_OBJ); 
     }else{ 
          $result = "Error in fetching Object the sql returned false"; 
     } 
     return $result; 
}

UPDATE:
I have tried to find out the problem and it seems that the values in LIMIT ?,? are being passed as a string no matter how you do either by directly execute or bind method both returns the string as it's type. The error message displays '0','10' as the values that is being placed by PDO. It seems PDO escaped my int as string via prepare method.

UPDATE 2
My guess for prepare function in above update was indeed correct when typing sql in mysql like this

SELECT * FROM `students` WHERE 1 LIMIT '10','10'

Returns error as

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

near ''10','10'' at line 1

Which is indeed the error returned by PHP. The way prepare method was made is the reason for this. It seems to quote everything in order to escape values to put in database in order to protect from SQL injection.

Community
  • 1
  • 1
Retro Ace
  • 16
  • 3
  • Are you missing a " ? – PhillipD Oct 13 '17 at 22:00
  • Sorry it's typing mistake.And it's not that I am not getting result I get empty array back – Retro Ace Oct 13 '17 at 22:00
  • You probably need to cast the values as integers, if they go as strings it will fail because `limit` requires ints. A thread on this for PDO, https://stackoverflow.com/questions/2269840/how-to-apply-bindvalue-method-in-limit-clause. – chris85 Oct 13 '17 at 22:04
  • @chris85 It doesnot work [10,10] and ['10','10'] both gives empty array as result. – Retro Ace Oct 13 '17 at 22:06
  • Don't use the `execute` iterate over the `$values` and bind each by its type. The lazy binding binds everything as a string. That also should do into the question. – chris85 Oct 13 '17 at 22:11
  • @chris85 Actually I tried another way of putting (int) on sql it doesnot work that way too. So the problem is with not binding?? – Retro Ace Oct 13 '17 at 22:13
  • @RetroAce Any luck with that? – chris85 Oct 13 '17 at 22:27
  • @chris85 I am trying to achieve pagination. I tried basically everything it seems the sql here is not receiveing values from execute method. I am still trying to debug. I think I need to patch it up. By inserting values literally – Retro Ace Oct 13 '17 at 22:35
  • I have about 142 rows and I am trying to return 10 rows after 10 it's OFFSET sql. – Retro Ace Oct 13 '17 at 22:37
  • Okay, so the first 10 work though, with limit `0, 10`? – chris85 Oct 13 '17 at 22:38
  • No empty array with passing from binding or via execute. – Retro Ace Oct 13 '17 at 22:40
  • `$smt->errorInfo();` in the `query` function is empty? – chris85 Oct 13 '17 at 22:46
  • Please, please do not do what was suggested by @NickCoons. Adding variables to a sql statement directly like "SELECT * FROM students LIMIT {$param[0]},{$param[1]};" will result in SQL injection. – Alex Barker Oct 13 '17 at 22:50
  • I'd have to point back to https://stackoverflow.com/questions/2269840/how-to-apply-bindvalue-method-in-limit-clause. Please show your usage where you bind it as an integer. – chris85 Oct 14 '17 at 00:00

0 Answers0