CODEWITHSUNDEEP
phpmysqlpdo
tony
tony

Reputation: 3

Making multiple different dynamic queries in MySQL

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?

Upvotes: 0

Views: 99

Answers (1)

Barmar
Barmar

Reputation: 782508

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.

Upvotes: 1

Related Questions