php - 我正在尝试将一个准备好的语句包含在另一个准备好的语句中

Question

在这个给定的准备好的声明中，它获得了文章标题。当它获取文章标题时，它还应该将该变量发送到另一个准备好的语句中，然后该语句找到给定文章的浏览量。

<table class="table table-colored table-centered table-inverse m-0">
<thead>
<tr>

<th>Title</th>
<th>Category</th>
<th>Writer</th>
<th>Action</th>
<th>Views</th>
</tr>
</thead>
<tbody>


<?php
$stmt = $con -> prepare('select tblposts.id as postid,tblposts.PostTitle as title,tblposts.PostUrl as postname, tblcategory.CategoryName as category,tblwritter.Writter as writter from tblposts left join tblcategory on tblcategory.id=tblposts.CategoryId left join tblwritter on tblwritter.WritterId=tblposts.WritterId where tblposts.Is_Active=?');
$cnt=1;
$stmt -> bind_param('i', $cnt);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($postid,$title,$postname,$category,$writter);
?>

 <tr>
<?php while ($stmt->fetch()){?>
<td><b><?php echo $postname;?></b></td>
<td><?php echo $category?></td>
<td><?php echo $writter?></td>


<td>
<a href="edit-post.php?pid=<?php echo $postid;?>"><i class="fa fa-pencil" style="color: #29b6f6;"></i></a>
    &nbsp;<a href="manage-posts.php?pid=<?php echo$postid;?>&&action=del" onclick="return confirm('Do you reaaly want to delete ?')"> <i class="fa fa-trash-o" style="color: #f05050"></i></a>
</td>

<?php
$stmt = $con -> prepare('select COUNT(ip) FROM tblviews WHERE postname = ?');
$stmt -> bind_param('s', $postname);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($ip);
?>
<?php while ($stmt->fetch()){?>
<td><?php echo $ip ?></td>
<?php } ?>

 </tr>


<?php }?>
                                            </tbody>
                                        </table>

此代码的预期结果是将帖子名称发送到准备好的语句中，然后调用任何给定文章中的浏览量。

Answer 1

简短回答:您正在循环内覆盖 $stmt。

长答案，有提示:通过首先编写所有 php，然后最后发布 html(仅使用 php 进行迭代和变量替换)，可以避免(或至少更容易检测到)这种情况。

更好的方法是将数据库访问和业务逻辑放入单独的类(即模型)中，并让 View 向模型(或 Controller ，具体取决于您对 MVC 的解释)询问所需的数据。

我无法测试它，但您应该能够使用单个查询:

select 
    tblposts.id as postid,
    tblposts.PostTitle as title,
    tblposts.PostUrl as postname,
    tblcategory.CategoryName as category,
    tblwritter.Writter as writter,
    COUNT(tblwritter.ip) as views
from tblposts
    left join tblcategory on tblcategory.id=tblposts.CategoryId 
    left join tblwritter on tblwritter.WritterId=tblposts.WritterId 
    left join tblviews on tblviews.postname = tblposts.PostUrl'
where tblposts.Is_Active=?
group by tblposts.PostUrl, tblposts.id, tblposts.PostTitle, tblCategory.CategoryName, tblwritter.Writter

(注意，MySQL 可能会让您只在分组中命名 tblposts.PostUrl)

这样，您的页面就可以简化为如下所示:

<?php
    // do your query here
?>
<table class="table table-colored table-centered table-inverse m-0">
  <thead>
    <tr>

      <th>Title</th>
      <th>Category</th>
      <th>Writer</th>
      <th>Action</th>
      <th>Views</th>
    </tr>
  </thead>
  <tbody>
    <?php while ($stmt->fetch()): ?>
    <tr>
      <td><b><?= $postname ?></b></td>
      <td><?= $category ?></td>
      <td><?= $writter?></td>
      <td>
        <a href="edit-post.php?pid=<?= $postid?>"><i class="fa fa-pencil" style="color: #29b6f6;"></i></a>
&nbsp;<a href="manage-posts.php?pid=<?=$postid?>&&action=del" onclick="return confirm('Do you reaaly want to delete ?')"> <i class="fa fa-trash-o" style="color: #f05050"></i></a>
      </td>
      <td><?= $views ?></td>
    </tr>
    <?php endwhile; ?>
  </tbody>
</table>