Making multiple different dynamic queries in MySQL

Question

this is my first question... I am making a dynamic blogging template and I want to show the next 4 blog posts from a MySQL database, here's what I am doing now:

$stmt = $conn->prepare("SELECT id, title, filename, date, content FROM blogs WHERE id=:id");

// bind in execute with an array. 

$values = [
    'id' => $id+1
];

$stmt->execute($values);

while ($row = $stmt->fetch())
{

$filename1 = $row['filename'];
$title1 = $row['title'];
$date1 = date_format(date_create($row['date']),"F j, Y");
$content1 = substr($row['content'], 0, 85);


$photo1 = preg_replace("/\..+/", "-thumb.jpg", $row['filename']);
}

$stmt = $conn->prepare("SELECT id, title, filename, date, content FROM blogs WHERE id=:id");

// bind in execute with an array. 

$values = [
    'id' => $id+2
];

$stmt->execute($values);

while ($row = $stmt->fetch())
{

$filename2 = $row['filename'];
$title2 = $row['title'];
$date2 = date_format(date_create($row['date']),"F j, Y");
$content2 = substr($row['content'], 0, 85);


$photo2 = preg_replace("/\..+/", "-thumb.jpg", $row['filename']);
}

(and the same for 3, 4)

While this for sure works, I feel it is a rather inefficient and expensive way of accomplishing this. What would the correct way of completing this task be?

BTW, you don't need a while loop if you're only fetching one row. — Barmar
– Barmar, Commented Jan 17, 2020 at 22:51

Barmar · Accepted Answer · 2020-01-17 22:55:37Z

1

Use ORDER BY and LIMIT to specify how many rows to fetch.

$stmt = $conn->prepare("
    SELECT id, title, filename, date, content 
    FROM blogs 
    WHERE id > :id
    ORDER BY id
    LIMIT 4");
$stmt->execute([':id' => $id]);
$posts = $stmt->fetchAll(PDO::FETCH_ASSOC);

$posts will then be a 2-dimensional array with all the data for the next 4 posts.

This won't have a problem if the id values aren't sequential, as your code does. That could happen if posts are deleted.

answered Jan 17, 2020 at 22:55

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

Collectives™ on Stack Overflow

Making multiple different dynamic queries in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related