1

I'm working on a forum. To pull the actual forum threads I use

            $query = <<<SQL
            SELECT *
            FROM forum_forums
            WHERE category = :category
            ORDER BY sticky DESC, lastAnswer DESC 
            LIMIT :limit 
            OFFSET :offset
SQL;
            $resource = $db->db->prepare( $query );
            $resource->execute(array (
                ":category" => $_GET['ident3'],
                ":limit"    => $limit,
                ":offset"   => $offset,
            ));

The limit and offset are defined variables, but if I remove them from the query it "works" and fetches results, but ruins the pagination portion, was wondering if there's something wrong with the way I'm preparing this query because it works when I have the query directly inside an execute function and use the $stmt->bindParam(':limit', $limit, PDO::PARAM_INT); I just really don't care to run my queries this way.

Above the query we also have.

        try {
            $query = <<<SQL
            SELECT COUNT(*)
            FROM forum_forums
            WHERE category = :category
SQL;

            $resource = $db->db->prepare( $query );
            $resource->execute( array (
                ":category" => $_GET['ident3'],
            ));
            $total = $resource->fetchColumn();
            $limit = 1;
            $pages = ceil($total / $limit);
            $page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
                'options' => array(
                    'default'   => 1,
                    'min_range' => 1,
                ),
            )));
            $offset = ($page - 1)  * $limit;
Morgan Green
  • 1,012
  • 2
  • 10
  • 22
  • What are the values of $limit and $offset? where do you set them up? – olibiaz Sep 10 '16 at 18:57
  • 2
    [A problem with LIMIT clause](https://phpdelusions.net/pdo#limit) – Your Common Sense Sep 10 '16 at 19:06
  • @olibiaz I added the beginning of the function before the query on top to show where the limit and offset comes from. – Morgan Green Sep 10 '16 at 19:08
  • 1
    You should use `bindParam` or `bindValue` with PDO::PARAM_INT since `execute` treats params as strings and adds quotes in query which violates sql syntax. Also in your test enviroment use `$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` – Alexey Chuhrov Sep 10 '16 at 19:17
  • See my answer to http://stackoverflow.com/questions/18005593/parametrized-pdo-query-and-limit-clause-not-working/18006026#18006026 – Bill Karwin Sep 10 '16 at 20:13

0 Answers0