转换[关闭]时我做错了什么

Question

i need to convert this mysql into pdo i tried the following but i guess its wrong as i get no results showing. yes it may be easy for one of you but pdo is news to me so appreciate your help :)

actual code

$rowperpage = 3;

            // counting total number of posts
            $allcount_query = "SELECT count(*) as allcount FROM posts";
            $allcount_result = mysql_query($allcount_query);
            $allcount_fetch = mysql_fetch_array($allcount_result);
            $allcount = $allcount_fetch['allcount'];

            // select first 3 posts
            $query = "select * from posts order by id asc limit 0,$rowperpage ";
            $result = mysql_query($query);

            while($row = mysql_fetch_array($result)){

                $id = $row['id'];
                $title = $row['title'];
                $content = $row['content'];
                $shortcontent = substr($content, 0, 160)."...";
                $link = $row['link'];

            ?>
                <!-- Post -->
                <div class="post" id="post_<?php echo $id; ?>">
                    <h1><?php echo $title; ?></h1>
                    <p>
                        <?php echo $shortcontent; ?>
                    </p>
                    <a href="<?php echo $link; ?>" class="more" target="_blank">More</a>
                </div>

            <?php
            }
            ?>

what i tried

 $rowperpage = 3;

        // counting total number of posts
        //$allcount_query = "SELECT count(*) as allcount FROM posts";
        //$allcount_result = mysql_query($allcount_query);

        $query = "SELECT count(*) FROM posts";
        $stmt = $db->prepare($query);       



        $allcount_fetch = $stmt->fetch(PDO::FETCH_ASSOC);
        $allcount = $stmt->fetchColumn();

        // select first 3 posts
        //$query = "select * from posts order by id asc limit 0,$rowperpage ";
        //$result = mysql_query($query);

        $qry = "select * from posts order by id asc limit 0,$rowperpage ";
        $stm = $db->prepare($qry);  

        while($row = $stm->fetch(PDO::FETCH_ASSOC)){

            $id = $row['id'];
            $title = $row['title'];
            $content = $row['content'];
            $shortcontent = substr($content, 0, 160)."...";
            $link = $row['link'];

can someone show the right way to do it?

Answer 1

prepare() goes with execute()

Prepared statements basically work like this:

1. Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled "?"). Example:

   INSERT INTO mtTable VALUES(?, ?, ?)

2. The database parses, compiles, and performs query optimization on the SQL statement template, and stores the result without executing it

3. Execute: At a later time, the application binds the values to the parameters, and the database executes the statement. The application may execute the statement as many times as it wants with different values

try with below code

<?php

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$rowperpage = 3;
$offset     = 0;

// counting total number of posts
$query = "SELECT count(id) AS allcount  FROM posts";
$stmt  = $db->query($query)->fetchColumn();

/******** The ABOVE QUERY LOOKS POINTLESS TO ME AS YOU NOT USING THE RESULTS FROM THAT QUERY*/

// select first 3 posts

$qry = "SELECT * FROM posts ORDER BY id ASC LIMIT ?,? ";
$stm = $db->prepare($qry);
$stm->execute(array($offset,$rowperpage));
$results = $stm->fetchall(PDO::FETCH_ASSOC);

if (count($results) > 0) {

    foreach ($results as $row) {

        $id           = $row['id'];
        $title        = $row['title'];
        $content      = $row['content'];
        $shortcontent = substr($content, 0, 160) . "...";
        $link         = $row['link'];

    }
} else {

    echo "No records found";
}
?>