код ниже является частью моего файла sitemap.xml
. Моя цель – написать последнюю измененную дату моих утвержденных статей. Для этих данных есть 2 возможности.
lastmod
является датой утверждения статьи. lastmod
является датой утверждения последнего одобренного комментария. Ошибки в моем выходе:
$newsql
во втором цикле while печатает «newstmt prepare error» на экране, поэтому if ($newstmt = $connection->prepare($newsql))
часть не работает Вы можете меня исправить?
Спасибо, всего хорошего
<?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('Ymd\TH:i:sP'); echo "\t".'<lastmod>'.$biggerdate.'</lastmod>'."\r\n"; echo '</url>'."\r\n"; } /* close statement */ $stmt->close(); } ?>
.<?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('Ymd\TH:i:sP'); echo "\t".'<lastmod>'.$biggerdate.'</lastmod>'."\r\n"; echo '</url>'."\r\n"; } /* close statement */ $stmt->close(); } ?>
Как это должно быть
Итак, вот вы:
$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('Ymd\TH:i:sP'); echo "\t<lastmod>$biggerdate</lastmod>\r\n"; echo "</url>\r\n"; }
он, конечно, не протестирован и, по-видимому, содержит много ошибок, но просто для того, чтобы показать вам.
Прежде всего, вы должны сделать работу с запросами.
затем сделайте чтение связанной вики-метки PDO-тега и установите соединение.
Затем исправить все ошибки и опечатки, если они есть