How Do I Search An Array For Duplicates And Append Suffix?

[mevukpaul]

Hey guys, really hoping someone can help me with this I'll be extremely grateful!

 

I am redesigning a website with a news archive type page using the mysql database from the previous version. The archive section contacts mysql and returns a number of values.

 

In the archive section the previous version of the site uses a column called newsTitle to put information into the anchor url of the page which displays the full article. The problem is that there are duplicates in newsTitle. The previous website dealt with this by using php to add a suffix to the 1st duplicate of -0 , a suffix to the second duplicate of -1 and so on. I want to recreate this so I can keep the urls the same for seo purposes, otherwise I would just use the unique column newsID to seperate each article.

 

Here's a simplistic version of my code so far. If anyone can come up with a way to add the suffixes to $title I will be soooo happy! Really appreciate your time and assistance.

 

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

@mysql_select_db($db) or die ("Unable to select database!");
$querytable="SELECT * FROM tblnews WHERE YEAR(newsDate) >= $startyear AND YEAR(newsDate) <= $endyear AND newsPageID = 13 ORDER BY `tblnews`.`newsDate` DESC";
$result=mysql_query($querytable);

$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!");
mysql_close();
$i= ($currentpage - 1) * 4;
while ($list = mysql_fetch_assoc($result3)) {
$title=mysql_result($result,$i,"newsTitle");
$url = strtolower ($title);
?>
<div id="release">
<p> </p>
<h3><a href="questionsarticle.php?pagetitle=<?php echo $url; ?>"><?php echo $title; ?></a></h3>
<p> </p>
</div>
<?php
$i++;
}

[Muddy_Funster]

That code, no offence, is a total mess. have a look at this, it's not tested, but should be close to what you want :

mysql_connect($host, $user, $pass) or die ("Unable to connect!");
mysql_select_db($db) or die ("Unable to select database!");
$sql = "SELECT newsTitle FROM tblnews WHERE YEAR(newsDate) >= $startyear AND YEAR(newsDate) <= $endyear AND newsPageID = 13 ORDER BY `tblnews`.`newsDate` DESC LIMIT $offset, $rowsperpage";
$result = mysql_query($sql) or die (mysql_error());
while($row = mysql_fetch_assoc($result){
$title[] = $row['newsTitle'];
}
foreach($title as $keys => $value){
$counter = aray_keys($title, $value);
if(count($counter)) > 1){
$i=0;
foreach($counter as $index => $key){
$title[$key] = $title[$key]."-{$i}";
$i++
}
}
}
foreach($title as $link){
$url = strtolower($link);
$output = <<<OUTP
<div id="release">
<p> </p>
<h3><a href="questionsarticle.php?pagetitle=$url">$link</a></h3>
<p> </p>
</div>
OUTP;
echo $output;
}

Edited November 15, 2012 by Muddy_Funster

[mevukpaul]

Thank you so much mate. You've saved me a whole lot of agony! Anything I can do to say thanks?!

 

p.s. I thought the code was a mess as I am a real novice with php/mysql.

p.p.s For anyone using this solution, there are a few pieces of the code where there is an extra ( or a missing ; or something along those lines but once you get that sorted it works a treat.

[Barand]

Alternatively you can do it in the query (uses my test table)

SELECT n.idnews, n.news, n.news_date, n.title, x.suffix
FROM news n
INNER JOIN 
   (
   SELECT idnews, 
   @row := IF(title=@prev, @row+1, 0) as suffix,
   @prev := title as dummy
   FROM news
       JOIN (SELECT @row:=-1, @prev:='') as y
   ORDER BY title, idnews
   ) as x USING (idnews)
ORDER BY news_date;

+--------+------+------------+-------+--------+
| idnews | news | news_date  | title | suffix |
+--------+------+------------+-------+--------+
|      1 | aaa  | 2012-04-01 | aa    |      0 |
|      2 | bbb  | 2012-04-02 | aa    |      1 |
|      3 | ccc  | 2012-04-03 | aa    |      2 |
|      4 | ddd  | 2012-04-04 | bb    |      0 |
|      5 | eee  | 2012-04-05 | cc    |      0 |
|      6 | ffff | 2012-04-06 | cc    |      1 |
|      7 | ggg  | 2012-04-07 | dd    |      0 |
|      8 | hhh  | 2012-04-08 | aa    |      3 |
|      9 | iii  | 2012-04-09 | ee    |      0 |
+--------+------+------------+-------+--------+

[Barand]

If you only want a suffix number when the title is duplicated, but blank otherwise then

mysql> SELECT n.idnews, n.news, n.news_date, n.title,
   -> CASE WHEN z.title IS NULL THEN '' ELSE x.suffix END as suffix
   -> FROM news n
   -> INNER JOIN
   ->     (
   ->     SELECT idnews,
   ->     @row := IF(title=@prev, @row+1, 0) as suffix,
   ->     @prev := title as dummy
   ->     FROM news
   ->         JOIN (SELECT @row:=-1, @prev:='') as y
   ->     ORDER BY title, idnews
   ->     ) as x USING (idnews)
   -> LEFT JOIN
   ->     (
   ->     SELECT title, COUNT(*) as dupes
   ->     FROM news
   ->     GROUP BY title
   ->     HAVING dupes > 1
   ->     ) as z USING (title)
   -> ORDER BY news_date;
+--------+------+------------+-------+--------+
| idnews | news | news_date  | title | suffix |
+--------+------+------------+-------+--------+
|      1 | aaa  | 2012-04-01 | aa    | 0      |
|      2 | bbb  | 2012-04-02 | aa    | 1      |
|      3 | ccc  | 2012-04-03 | aa    | 2      |
|      4 | ddd  | 2012-04-04 | bb    |        |
|      5 | eee  | 2012-04-05 | cc    | 0      |
|      6 | ffff | 2012-04-06 | cc    | 1      |
|      7 | ggg  | 2012-04-07 | dd    |        |
|      8 | hhh  | 2012-04-08 | aa    | 3      |
|      9 | iii  | 2012-04-09 | ee    |        |
+--------+------+------------+-------+--------+