Returning multiple rows from a function -- MySQL Database -- PDO

Question

I'm working on a blog site, and over the past few days I've been cleaning it up so it uses a lot more included functions rather than inline php. So far it's been really helpful.

Right now I'm trying to use a function to get all the blogposts in the database, which are stored like this:

Previously I was doing this right in my main blog.php file, which includes all the html. That looked like this:

$stmt = $db->prepare("SELECT * FROM blog_posts ORDER BY id DESC");
$stmt->execute();
foreach($stmt as $blogPost)
{
    $dateTime = date("j F, Y g:i a", (strtotime($blogPost['date_posted'])));

    echo "<div class='blog-main-preview'>";
    echo "<p class='blog-preview-title'>" . $blogPost['title'] . "</p>";
    echo "<p id='blog-preview-date'>" . $dateTime . "</p><br>";
    $post = $blogPost['post'];
    $post = strip_tags($post);
    $post = substr($post, 0, 200);
    echo "<p id='blog-preview-post'>" . $post . "...</p><br>";
    echo "<a href='blog-post.php?postid=" . $blogPost['id'] . "' id='read-more'><p>Read More</p></a><br>";
    echo "</div>";

}

This worked great but I was looking to move it to a function too. I realize I could just move the whole thing to a function, I was just hoping there was a solution where I didn't have to have any of the html in the function, and it could return something with which I could run the foreach loop in the main blog.php file. Can I do this?

Edit: So to clarify, what I'm looking for is to have my blog.php page look like:

$blogPosts = getBlogPosts($db);
foreach($blogPosts as $blogPost)
{
    $dateTime = date("j F, Y g:i a", (strtotime($blogPost['date_posted'])));

    echo "<div class='blog-main-preview'>";
    echo "<p class='blog-preview-title'>" . $blogPost['title'] . "</p>";
    echo "<p id='blog-preview-date'>" . $dateTime . "</p><br>";
    $post = $blogPost['post'];
    $post = strip_tags($post);
    $post = substr($post, 0, 200);
    echo "<p id='blog-preview-post'>" . $post . "...</p><br>";
    echo "<a href='blog-post.php?postid=" . $blogPost['id'] . "' id='read-more'><p>Read More</p></a><br>";
    echo "</div>";

}

And the functions file will contain the function which retrieves and returns the blogPosts.

I need a function that will retrieve the blogposts and return it in such a way that I can iterate through them with a foreach loop. — Chris
– Chris, Commented Oct 8, 2015 at 0:32
As I said in the question, I want the FUNCTION, to RETURN the results that can be iterated through from a DIFFERENT FILE, I don't want the function to iterate through them itself. — Chris
– Chris, Commented Oct 8, 2015 at 0:36

bd808 · Accepted Answer · 2015-10-12 19:45:00Z

2

<?php
function getBlogPosts($db) {
    $stmt = $db->prepare("SELECT * FROM blog_posts ORDER BY id DESC");
    $stmt->execute();

    // Get all results from the query as an array of associative arrays
    $rows = $sth->fetchAll();

    // Post-process results
    // Use `&` to get a reference to each row to prevent the default 
    // "copy on write" behavior of PHP's arrays from detaching our 
    // changes from the $rows array. Without this, the updated $row[X]
    // values would only be visible inside the foreach loop.
    foreach ($rows as &$row) {
      $row['date_posted'] = date("j F, Y g:i a", strtotime($row['date_posted']));
      $row['post'] = strip_tags($row['post']);
      $row['post'] = substr($row['post'], 0, 200);
    }

    return $rows;
}

$blogPosts = getBlogPosts($db);
foreach($blogPosts as $blogPost)
{ ?>

<div class="blog-main-preview">
  <p class="blog-preview-title"><?= htmlspecialchars($blogPost['title']) ?></p>
  <p id="blog-preview-date"><?= htmlspecialchars($blogPost['date_posted']) ?></p><br>
  <p id="blog-preview-post"><?= htmlspecialchars($blogPost['post']) ?>...</p><br>
  <a href="blog-post.php?postid=<?= urlencode($blogPost['id']) ?>" id="read-more"><p>Read More</p></a><br>
</div>

<?php } ?>

edited Oct 12, 2015 at 19:45

answered Oct 8, 2015 at 5:18

bd808

1,8432 gold badges24 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chris Over a year ago

That did it! Turns out it won't work if "$blogPosts = getBlogPosts($db);" and the proceeding foreach loop are in different chunks of php, as long as they're in the same set of <?php ?> tags, it works. Odd, but that was my problem from the start. Thanks! Do you think there's any way to get the: $dateTime = date("j F, Y g:i a", strtotime($blogPost['date_posted'])); $post = strip_tags($blogPost['post']); $post = substr($post, 0, 200); into the function as well?

user557846 Over a year ago

@Chris the 1st and 3rd you can just do in the querry, the 2nd you will have to loop the data in the query.

Chris Over a year ago

It works! That's super cool and really makes the file a lot cleaner. I really appreciate al your help, but is there any chance you could explain how exactly the "&$row", works?

Barmar · Accepted Answer · 2015-10-08 02:00:58Z

1

You can simply return $stmt from the function.

function getBlogPosts($db) {
    $stmt = $db->prepare("SELECT * FROM blog_posts ORDER BY id DESC");
    $stmt->execute();
    return $stmt;
}

answered Oct 8, 2015 at 2:00

Barmar

789k57 gold badges554 silver badges669 bronze badges

Comments

user557846 · Accepted Answer · 2015-10-08 00:44:37Z

0

function getBlogPosts($db){
$stmt = $db->prepare("SELECT * FROM blog_posts ORDER BY id DESC");
$stmt->execute();
return $stmt->fetchAll();
}

answered Oct 8, 2015 at 0:44

user557846

2 Comments

Chris Over a year ago

Doesn't work. :/ The Page still renders but no blog posts, so it seems like the foreach loop isn't retrieving results it needs.

user557846 Over a year ago

seems like come on you can test that, then debug line by line with echo,var_dump and print_r

Collectives™ on Stack Overflow

Returning multiple rows from a function -- MySQL Database -- PDO

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related