Reputation: 127
I have built a CMS and using worpress databases, wp_posts & wp_postmeta.
what i am trying to accomplish is pull the data from wp_posts which will display all posts, then with the second query i am trying to pull out the image.
on wp_posts the ID is equal to the post_id on wp_postmeta
my code is working however the image on the second query is pulling the same image for all posts, how do i go about pulling the image from $ID from the first query.
<?php
$query = "SELECT * FROM wp_posts
ORDER BY post_date DESC LIMIT 0, 25";
$result = mysqli_query($connection, $query) or die(mysqli_error($connection));
while($row = mysqli_fetch_array($result)) {
$ID = $row['ID'];
$post_title = $row['post_title'];
$post_date = $row['post_date'];
$post_excerpt = $row['post_excerpt'];
$post_content = $row['post_content'];
$querystrs = "
SELECT post_id,
MAX(CASE WHEN meta_key = '_wp_attached_file' THEN meta_value END) '_wp_attached_file'
FROM wp_postmeta
WHERE meta_key IN('_wp_attached_file') AND post_id = '' ";
$select_postmetaimage = mysqli_query($connection, $querystrs) or die(mysqli_error($connection));
while($row = mysqli_fetch_assoc($select_postmetaimage)) {
$image = $row['key_value'];
?>
<!-- First Blog Post -->
<div class="panel panel-primary">
<div class="panel-heading">
<h3><a href="post.php?p_id=<?php echo $ID; ?>"><font color="white"><?php echo $post_title; ?></font></a></h3>
<p><span class="glyphicon glyphicon-time"></span> <?php echo humanTiming( strtotime($post_date) ); ?>
</p>
</div>
<div class="panel-body">
<img class='col-md-4' align='left' width='148' src='images/<?php echo $image; ?>' alt=''>
<p><?php echo $post_content; ?></p>
</div>
<p align="right"><a class="btn btn-primary" href="post.php?p_id=<?php echo $ID; ?>">Read More <span class="glyphicon glyphicon-chevron-right"> </span></a></p>
</div>
<?php } } ?>
Upvotes: 0
Views: 96
Reputation: 121
Edit: Changed image location.
I don't think the second while loop is necessary. If you add the part to fetch the image in the first query you only need to run through it once. Please try (I don't have data to confirm 100%), but it should be on the right track:
<?php
$query = "SELECT
wp_posts.*,
(SELECT meta_value FROM wp_postmeta WHERE wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = '_wp_attached_file' ORDER BY meta_id DESC LIMIT 1) as image
FROM wp_posts
ORDER BY post_date DESC
LIMIT 0, 25";
$result = mysqli_query($connection, $query) or die(mysqli_error($connection));
while ($row = mysqli_fetch_array($result)) {
$ID = $row['ID'];
$post_title = $row['post_title'];
$post_date = $row['post_date'];
$post_excerpt = $row['post_excerpt'];
$post_content = $row['post_content'];
$image = $row['image'];
?>
<!-- First Blog Post -->
<div class="panel panel-primary">
<div class="panel-heading">
<h3><a href="post.php?p_id=<?php echo $ID; ?>"><font color="white"><?php echo $post_title; ?></font></a></h3>
<p><span class="glyphicon glyphicon-time"></span> <?php echo humanTiming(strtotime($post_date)); ?></p>
</div>
<div class="panel-body">
<img class='col-md-4' align='left' width='148' src='/wp-content/uploads/<?php echo $image; ?>' alt=''>
<p><?php echo $post_content; ?></p>
</div>
<p align="right"><a class="btn btn-primary" href="post.php?p_id=<?php echo $ID; ?>">Read More <span class="glyphicon glyphicon-chevron-right"> </span></a></p>
</div>
<?php } ?>
Upvotes: 1