remove duplicates in mysql query

[eagled2]

I am creating a page of articles for a site i'm working on. The current page i'm creating needs a list of articles by date. I want to have it list the year the article was posted as a header then list only the entries for that year underneath. I want to have 2 columns of article titles per row. My query is pulling the proper data but I can't figure out how to get it formatted properly. I have the first title followed by the articles for that year in the same row but the second header ends up in a 3rd column in the same row. Here is my current code. You can view it at http://eagled2.com/archtest.php. Any ideas.

<?
require "config.php";
include 'opendb.php';

echo '<link rel="stylesheet" href="/httest.css" type="text/css">';

include 'hthead.htm';

// queries database 
$query8 = mysql_query("SELECT id, title, date_format(date, '%Y') as year, date_format(date, '%b %Y') as seldate FROM htarticles where archived=1 group by year desc order by date desc") 
//no query? DIE DIE DIE!  
or die("Reason :<br />".mysql_error()); 

$x = 0;                        //set counter variable to zero 

// lets begin the table 
echo '<TABLE WIDTH="90%" BORDER="0">'."\n"; 

// Loop through result set and display rows 

while($row = mysql_fetch_array($query8)) 
{ 

//set table rows with 2 table cells 
if (!($x % 2)) { 
if ($x > 0) { 
echo '</tr>'; 
} 
echo '<tr>' . "\n"; 
} 
$x++; 

echo '<td><div align="left">'."\n"; 
echo $row['year'].'</div></t 
d></tr>'."\n"; 
//Begin displaying the 3 story titles underneath the category name 
$query9 = mysql_query("SELECT DISTINCT * FROM(SELECT id, title, date_format(date, '%Y') as year, date_format(date, '%b %Y') as seldate FROM htarticles where archived=1 order by date desc)ss where year = $row[year]")
//no query? DIE DIE DIE!  
or die("Reason :<br />".mysql_error()); 
echo '<tr>'; 
while($row = mysql_fetch_array($query9)) 
{ 
echo '<td><a href="'.$self.'?='.$row['seldate'].'">'.$row['title'].'</a></td>&nbsp'; 
} 
} 
// lets end the table 
echo '</tr>'."\n"; 
echo '</TABLE>'."\n"; 
?>

[sergb]

If I understand correctly, you want a header and two columns of data under it.
It looks like generated HTML is not well formed.
Try this for your HTML generation (fill out the queries and whatever is missing).
This code aims to generate a separate table under each header.

<?php
$query8 = "...";
while ($row = mysql_fetch_array($query8))
{
	// Header
	echo '<div align="left">', $row['year'], '</div>';
	// Start the table
	echo '<table>';
	$query9 = "...";
	// Generate table rows with 2 cells.
	while (true)
	{
		$r1 = mysql_fetch_array($query9);
		$r2 = mysql_fetch_array($query9);
		if (!$r1 && !$r2) { break; }
		echo '<tr><td>';
		if ($r1)
			echo $r1['title'];
		echo '</td><td>';
		if ($r2)
			echo $r2['title'];
		echo '</td></tr>';
	}
	echo '</table>';
}
?>

Hope this helps.

[eagled2]

Thanks that's exactly what I needed. I just modified it a little bit more to add the date and seperate the sections a little. Here is the section I changed.

if (!$r1 && !$r2) { break; }
        echo '<tr>';
        if ($r1)
            echo '<td>'.$row['seldate'].' - '.$r1['title'];
        echo '</td>';
        if ($r2)
            echo '<td></td><td>'.$row['seldate'].' - '.$r2['title'];
        echo '</td></tr>';
    }
    echo '</table><br>';

[sergb]

I think, the code you posted may still generate malformed HTML. Think of a case when you have an odd number of records returned by the $query9. When you get to the last row, in the code above the $r1 variable will be set, while the $r2 will not be set.

if (!$r1 && !$r2) break;
// start row
echo '<tr>';
if ($r1) {
    // start table cell
    echo '<td>', $r1['seldate'], '-', $r1['title'];
}
// end table cell
echo '</td>';

if ($r2) {
    // this code will not run because $r2 is undefined
    echo '<td></td><td>'.$r2['seldate'].' - '.$r2['title'];
}
// ends the cell that was never started because the code in the if($r2) block
// was not executed.
echo '</td></tr>';

If you want to keep your HTML well formed all the time, you only need to print the contents of the cells inside the if blocks. See the code I posted before. Even if $r1 or $r2 is not set, the HTML table will still have all the proper opening and closing tags.

Good luck.

[eagled2]

Thanks again for the help. I think I understand what you were saying. The way I had it the tr tag would never be closed unless there was an even number of items for the row. I did add an html link to the code so I added your changes to my code and it still seems to work. Here is what I have.

if (!$r1 && !$r2) { break; }
        echo '<tr>';
        if ($r1) {
            echo '<td><a href="'.$self.'?id='.$r1['id'].'">'.$r1['seldate'].'</a> - '.$r1['title'];
}
        echo '</td>';
        if ($r2) {
            echo '<td></td><td><a href="'.$self.'?id='.$r2['id'].'">'.$r2['seldate'].'</a> - '.$r2['title'];
}
        echo '</td></tr>';
    }
    echo '</table></p>';