SQL query pulling data advice

Question

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

Answer 1

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