trying to convert mysql to pdo: need guidance

[lovephp]

i got the following mysql functions which i need to convert into pdo but yes still not expert in pdo so need guidance what i am doing wrong in my conversion

 

the original

 

<?php

$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 am trying

 

$query = "SELECT count(*) FROM posts";
            $stmt = $db->prepare($query);        
            
            
            
            $allcount_fetch = $stmt->fetch(PDO::FETCH_ASSOC);
            $allcount = $stmt->fetchColumn();

          
            
            $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'];

            ?>
                <!-- 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
            }
            ?>

but i get a blank page instead

Edited August 10, 2017 by lovephp

[Sepodati]

I think you're missing the execute() call to actually run the query. Also, if you want to submit variables into the query, you need to use bindParam(). Inserting the variable directly into the query defeats the purpose of using PDO.

 

I'm not an expert in this area (learning myself), so hopefully some others can chime in.

 

Look at this example from the manual:

 

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
?>

Edited August 10, 2017 by Sepodati

[lovephp]

I think you're missing the execute() call to actually run the query. Also, if you want to submit variables into the query, you need to use bindParam(). Inserting the variable directly into the query defeats the purpose of using PDO.

 

I'm not an expert in this area (learning myself), so hopefully some others can chime in.

 

Look at this example from the manual:

 

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
?>

you r right mate i need to bind but first i need to know what is wrong in my conversion im sure its definitely wrong thats why no records display

[Jacques1]

Before you start writing code, you need to actually understand how PDO and prepared statements work. There are excellent tutorials which explain this in great detail, so going by trial-and-error is complete nonsense.

 

First off, the purpose of a prepared statement is to pass values to a query or speed up multiple query executions (though the benefit of this is debatable). When you neither have external values nor want to execute the query more than once, then a prepared statement doesn't make sense. Just use a plain old query.

$total_posts = $db->query('SELECT COUNT(*) FROM posts')->fetchColumn();

Secondly, prepared statements have to be executed. The prepare() method merely creates a statement, it doesn't run any queries. That's what execute() is for. There are three necessary steps:

1. Create the prepared statements.
2. Bind values to it (this can usually be combined with the execution).
3. Execute the statement.

Third, a blank page means you don't understand how error reporting works, or the configuration is messed up. This is your local development PC, right? Then turn the error reporting all the way up, and enable display_errors. In production, you log the errors instead. PHP doesn't just show blank pages. There's always an error message.

 

Once again: Learn PDO (and PHP).

Edited August 10, 2017 by Jacques1

[ginerjm]

Plus - you should read the manual on the different pdo functions to see what they do.  You are running both a fetch and a fetch_column.  You should read up on what each of them does because as it is you're going to being pulling out your hair soon.

 

Manuals Are Good.  Manuals Are Your Friend.   Read 'em!!!

 

Begin here:  http://php.net/manual/en/class.pdo.php

 

Then progress to here:  http://php.net/manual/en/class.pdostatement.php

[lovephp]

Before you start writing code, you need to actually understand how PDO and prepared statements work. There are excellent tutorials which explain this in great detail, so going by trial-and-error is complete nonsense.

 

First off, the purpose of a prepared statement is to pass values to a query or speed up multiple query executions (though the benefit of this is debatable). When you neither have external values nor want to execute the query more than once, then a prepared statement doesn't make sense. Just use a plain old query.

$total_posts = $db->query('SELECT COUNT(*) FROM posts')->fetchColumn();

Secondly, prepared statements have to be executed. The prepare() method merely creates a statement, it doesn't run any queries. That's what execute() is for. There are three necessary steps:

1. Create the prepared statements.
2. Bind values to it (this can usually be combined with the execution).
3. Execute the statement.

Third, a blank page means you don't understand how error reporting works, or the configuration is messed up. This is your local development PC, right? Then turn the error reporting all the way up, and enable display_errors. In production, you log the errors instead. PHP doesn't just show blank pages. There's always an error message.

 

Once again: Learn PDO (and PHP).

thanks learning more with practice but still a long way to go i guess

[lovephp]

Plus - you should read the manual on the different pdo functions to see what they do.  You are running both a fetch and a fetch_column.  You should read up on what each of them does because as it is you're going to being pulling out your hair soon.

 

Manuals Are Good.  Manuals Are Your Friend.   Read 'em!!!

 

Begin here:  http://php.net/manual/en/class.pdo.php

 

Then progress to here:  http://php.net/manual/en/class.pdostatement.php

the manuals are soo long and words used are so hard for me to understand but yes at times i do read

[ginerjm]

IF you EVER want to be a "programmer", you will HAVE to learn how to read manuals.    Your English seems to be in working order.  Now it's time to get your brain in gear.

[ginerjm]

ok - I quit this topic.  Apparently it's been hijacked by some A....