PHP在准备好的语句中的while循环不成功 [英] php unsuccessful while loop within prepared statements

问题描述

代码是我的sitemap.xml文件的一部分.我的目的是写我批准的文章的最后修改日期.此数据有两种可能性.

code below is a part of my sitemap.xml file. My aim is to write the last modified date of my approved articles. There are 2 possibilities for this data.

1. 如果文章没有批准的评论，则lastmod为批准 文章的日期.
2. 如果文章至少有1条批准的评论，则lastmod是 最后批准的评论的批准日期.

1. If article has no approved comment, then lastmod is the approval date of the article.
2. If article has at least 1 approved comment, then lastmod is the approval date of the last approved comment.

我的输出中的错误是:

1. 我的数据库中有一些文章没有评论，但是这些无评论的文章也获得了其他一些文章的最后评论的批准日期.结果，由于我有一些评论今天已经得到批准，因此所有这些无评论的文章的最后发表日期都是今天.但是这些文章很老.
2. $newsql在我的第二个while循环中在"newstmt prepare error" 上打印 屏幕上，所以if ($newstmt = $connection->prepare($newsql))部分不起作用

1. There are some articles in my db that has no comments but also these commentless articles get the approval date of last comment of some other articles. As a result since I have some comments which are approved today, all those commentless articles' lastmode is date of today. but these articles are quite old.
2. $newsql in my 2nd while loop prints "newstmt prepare error" on screen, so if ($newstmt = $connection->prepare($newsql)) part doesn't work

你能纠正我吗?

谢谢你，问候

<?php       
//if spesific article is approved then its last modification date = approval date of article
//if spesific article has approved comment(s), then modification date = approval date of its last comment
$sql = "SELECT col_author, col_title, col_approvaldate FROM articles WHERE col_status = ? ORDER by col_approvaldate DESC";

if ($stmt = $connection->prepare($sql)) 
{
    /* bind parameters */
    $stmt -> bind_param("s", $bindparam1);
    
    /* assign value */
    $bindparam1 = 'approved';
    
    /* execute statement */
    $stmt->execute();

    /* bind result variables */
    $stmt->bind_result($author, $title, $articledate);

    /* fetch values */
    while ($stmt->fetch()) 
    {
        //if exist, get approved newest comment approval date
        $newsql = "SELECT col_approvaldate FROM comments WHERE col_status = ? AND col_for_author = ? AND col_for_title = ? ORDER by col_approvaldate DESC LIMIT 1";
        if ($newstmt = $connection->prepare($newsql))
        {
            /* bind parameters */
            $newstmt -> bind_param("sss", $ybindparam1, $ybindparam2, $ybindparam3);
            
            /* give values */
            $ybindparam1 = 'approved';
            $ybindparam2 = $author;
            $ybindparam3 = $title;
            
            /* execute statement */
            $newstmt->execute();

            /* bind result variables */
            $newstmt->bind_result($commentdate);
            
            /* fetch values */
            $biggerdate = '';
            
            while ($newstmt->fetch())
            {                   
                // is there any approved comment for this article?
                if (!is_null($commentdate))
                {$biggerdate = $commentdate;}
            }
            /* close statement */
            $newstmt->close();                  
        }
        else {echo 'newstmt prepare error';}
        
        //print the result
        echo '<url>'."\r\n";
        echo "\t".'<loc>'.root_folder.urlencode('articles').'/'.urlencode(space_to_dash($author)).'/'.urlencode(space_to_dash(no_punctuation($title))).'</loc>'."\r\n";             
        //if there is no approved comment for this article
        if ($biggerdate == '') 
        {
            $biggerdate = $articledate;
        }
        $datetime = new DateTime($biggerdate);
        $biggerdate = $datetime->format('Y-m-d\TH:i:sP');
        echo "\t".'<lastmod>'.$biggerdate.'</lastmod>'."\r\n";
        echo '</url>'."\r\n";

    }
    /* close statement */
    $stmt->close();
    
}
?>

推荐答案

必须如何

1. 您必须使用单个查询来获取数据.
2. 评论必须使用文章ID链接
3. PDO 而不是mysqli应该使用

1. You have to get your data using single query.
2. Comments have to be linked using article id
3. PDO instead of mysqli ought to be used

所以，你去:

$sql = "SELECT author, title, a.approvaldate, max(c.approvdate) biggerdate
        FROM articles a
        LEFT JOIN comments c ON c.article_id = a.id AND c.status='approved'
        WHERE a.status = 'approved' 
        GROUP BY a.id
        ORDER BY a.approvaldate DESC";
$stmt = $con->prepare($sql);
$stmt->execute();
$data = $stmt->fetchall();
foreach ($data as $row) {
    extract($row);
    echo "<url>\r\n";
    echo "\t<loc>".root_folder.'articles';
    echo '/'.urlencode(space_to_dash($author));
    echo '/'.urlencode(space_to_dash(no_punctuation($title)))."</loc>\r\n";
    //if there is no approved comment for this article
    if (!$biggerdate) 
    {
        $biggerdate = $approvaldate;
    }
    $datetime = new DateTime($biggerdate);
    $biggerdate = $datetime->format('Y-m-d\TH:i:sP');
    echo "\t<lastmod>$biggerdate</lastmod>\r\n";
    echo "</url>\r\n";
}

它当然没有经过测试，显然包含许多错误，只是为了向您展示一个想法.

it is of course not tested and apparently contains many errors but just to show you an ides.

首先，您必须使查询生效.
然后读取链接的PDO标签Wiki并建立连接.
然后修复所有错误和错字(如果有的话)

First of all you have to make query work.
then make read linked PDO tag wiki and establish a connection.
Then fix all the errors and typos, if any

这篇关于PHP在准备好的语句中的while循环不成功的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！