当前位置: 动力学知识库 > 问答 > 编程问答 >

php - Search mysql column for duplicates and add suffix to variable in while loop

问题描述:

Right, I really hope someone can help on this as I am stumped. Basically, I'm redesigning a website for a company using a mysql database and php to provide a news archive type section. It all works fine except one thing. I am pulling the information from a database to provide the snippet view and using the newsTitle column from the mysql database to put information into the respective anchor's link text which can be retrieved from the corresponding page using the $_GET function. The issue is that although predominantly unique, there are a number of newsTitle entries which are duplicates, yet the content for each is different. The old website dealt with this by adding the suffix -0 then -1 and so on to the end of each duplicate (on page) but I cannot figure out how to do this.

The variable I need to check is that which is contained in $title/$url against all other versions of $title/$url that are generated. Please Note: I have to use the suffixes and not the id to make each page unique as this is what the old version of the website did and I don't want to break links from Google or lose 'link juice'.

Here's my code (I am a n00b so excuse the awfulness):

mysql_connect($host, $user, $pass) or die ("Unable to connect!");

@mysql_select_db($db) or die ("Unable to select database!");

$sql = "SELECT * FROM tblnews WHERE YEAR(newsDate) >= $startyear AND YEAR(newsDate) <= $endyear AND newsPageID = 13 ORDER BY `tblnews`.`newsDate` DESC LIMIT $offset, $rowsperpage";

$result3 = mysql_query($sql, mysql_connect($host, $user, $pass)) or die ("Unable to connect!");

$querytable="SELECT * FROM tblnews WHERE YEAR(newsDate) >= $startyear AND YEAR(newsDate) <= $endyear AND newsPageID = 13 ORDER BY `tblnews`.`newsDate` DESC";

$result=mysql_query($querytable);

mysql_close();

$i= ($currentpage - 1) * 4;

while ($list = mysql_fetch_assoc($result3)) {

$title=mysql_result($result,$i,"newsTitle");

$titlelower = strtolower ($title);

$changedwords = array(' is ', ' with ', ' on ', ' to ', ' for ', ' the ', ' at ', ' a ', ' in ', ' of ', '"', "'", ':', '(', ')', ';', '?', '!', '.', ',', '&', '£', '/');

$replacements = array('-', '-', '-', '-', '-', '-', '-', '-', '-', '-', '', '', '', '', '', '', '', '', '', '', '', '', '-');

$url = str_replace ($changedwords, $replacements, $titlelower);

$url = str_replace (" ", "-", $url);

$url = str_replace ("---", "-", $url);

$url = str_replace ("--", "-", $url);

$date=mysql_result($result,$i,"newsDate");

$shortdescription=mysql_result($result,$i,"newsTeaser");

$id=mysql_result($result,$i,"newsID");

?>

<div id="release" <?php if($odd = $i%2 ) { echo 'style="background-color:#d2d2d2;"';

} else { echo 'style="background-color:#dbdbdb;"'; } ?> >

<p>&nbsp;</p>

<h3><a href="questionsarticle.php?pagetitle=<?php echo $url; ?>&id=<?php echo $id; ?>"><?php echo $title; ?></a></h3>

<p style="font-size:12px;color:#666;font-style:italic;">- <?php echo date('l, jS F Y',strtotime($date)); ?></p>

<p>&nbsp;</p>

<p><?php echo $shortdescription; ?></p>

<p>&nbsp;</p>

<p>&nbsp;</p>

</div>

<?php

$i++;

}

?>

网友答案:

It is not clear from the question which newsarticle should have which URL suffix, but assuming that it is based on age as determined by the newsID, you will have to count the number of older news for each duplicate title.

This can be done by joining tblnews to itself, and counting the rows that have the same title but a lower newsID than a given article.

Your modified query, including a column with the number of older articles for each article, will then be:

SELECT DISTINCT news.*, COUNT(older.newsID) AS older
FROM tblnews news
LEFT JOIN tblnews older
ON older.newsTitle = news.newsTitle
AND older.newsID < news.newsID
WHERE year(news.newsDate) >= $startYear
AND year(news.newsDate) <= $endYear
AND news.newsPageID = 13
GROUP BY news.newsID
ORDER BY news.newsDate DESC;
分享给朋友:
您可能感兴趣的文章:
随机阅读: