Optional LIMIT for function grabbing all post with PDO

Discussion in 'PHP' started by BenSeagrave, Nov 6, 2012.

  1. #1
    I'm trying to create a function that will grab all posts within a table. I also wanted to add a optional LIMIT parameter. Here is an example:

    function get_all_posts($conn, $limit = 0) {
    	if ($limit > 0) {
    		$stmt = $conn->prepare("SELECT * FROM posts LIMIT :limit");
    		$stmt->execute(array(
    			':limit' => $limit
    		));
    		$results = $stmt->fetchAll();
    		return $results ? $results : false ;
    	} else {
    		$stmt = $conn->prepare("SELECT * FROM posts");
    		$stmt->execute();
    		$results = $stmt->fetchAll();
    		return $results ? $results : false ;
    	}
    }
    
    PHP:
    If I call the function without using the limit parameter it works and displays all the posts. But if I call the function like this: get_all_posts($conn, "1"); Then I get this error:

    Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation:
     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 ''1'' at line 1' in /Applications/MAMP/htdocs/sandbox/blog2/functions.php:19 Stack trace: #0
     /Applications/MAMP/htdocs/sandbox/blog2/functions.php(19): PDOStatement->execute(Array) #1
     /Applications/MAMP/htdocs/sandbox/blog2/index.php(12): get_all_posts(Object(PDO), '1') #2 {main} thrown in 
    /Applications/MAMP/htdocs/sandbox/blog2/functions.php on line 19
    Code (markup):
    Can anyone show me where I've gone wrong?

    Thanks!
     
    BenSeagrave, Nov 6, 2012 IP
  2. EricBruggema

    EricBruggema Well-Known Member

    Messages:
    1,740
    Likes Received:
    28
    Best Answers:
    13
    Trophy Points:
    175
    #2
    example found by google

    
    $statement = $conn->prepare("SELECT id,username FROM public2 WHERE username = :name LIMIT :limit OFFSET :offset");
    $name = "Lei Lei";
    $statement->bindValue(':name', $name);
    $statement->bindValue(':limit', (int) $start, PDO::PARAM_INT);
    $statement->bindValue(':offset', (int) $per_page, PDO::PARAM_INT);
    $statement->execute();
    
    Code (markup):
     
    EricBruggema, Nov 6, 2012 IP