php - SQL查询拉取数据建议

Question

我已经构建了一个 CMS 并使用 worpress 数据库、wp_posts 和 wp_postmeta。

我想要完成的是从 wp_posts 中提取数据，它将显示所有帖子，然后通过第二个查询我试图提取图像。

wp_posts 上的 ID 等于 wp_postmeta 上的 post_id

我的代码正在工作，但是第二个查询上的图像为所有帖子提取相同的图像，我如何从第一个查询的 $ID 中提取图像。

<小时/>

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

编辑:更改图像位置。

我认为第二个 while 循环是不必要的。如果您在第一个查询中添加用于获取图像的部分，则只需运行一次。请尝试一下(我没有数据可以100%确认)，但应该是在正确的轨道上:

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