I want the query to retrieve topics but if the topic repeats I only want one result to show up, no duplicates. I also want it to return username and timestamp. Here is what i have now but I get an error. [mysql_fetch_array() expects parameter 1 to be resource]. Anybody have any ideas of how i should change this?

viewtopics.php

<?php
$sql="SELECT username, timestamp, topic FROM hotspots WHERE topic IN (Select MAX (id) FROM hotspots GROUP BY topic) ORDER BY id DESC";
$result=mysql_query($sql);
 
 
$color="1";
 
echo '<table width="472px" border="0" align="center" cellpadding="2" cellspacing="0" style="border:1px solid #0000ff;">';
while($rows=mysql_fetch_array($result)){
 
if($color==1){
 
echo "
<tr bgcolor='#6698FF' ><td style='border-top:1px solid #0000ff; color:#ffffff;font-weight: bold;'>".$rows['topic']."</td>
<td style='border-top:1px solid #0000ff; color:#ffffff;font-weight: bold;'>".$rows['username']."</td>
<td style='border-top:1px solid #0000ff; color:#ffffff;font-weight: bold;'>".$rows['timestamp']."</td></tr>";
$color="2";
 
} else {
 
echo "<tr bgcolor='#AFDCEC'>
<td style='border-top:1px solid #0000ff;color:#0000ff;font-weight: bold;'>".$rows['topic']."</td>
<td style='border-top:1px solid #0000ff;color:#0000ff;font-weight: bold;'>".$rows['username']."</td>
<td style='border-top:1px solid #0000ff;color:#0000ff;font-weight: bold;'>".$rows['timestamp']."</td></tr>";
$color="1";
}

}
echo '</table>';

?>

your SQL is not meet your requirement.. Post the structure of hotspot table here...

@karthik , yes.
and also you can write like this simply..

SELECT username, timestamp, topic FROM hotspots GROUP BY topic ORDER BY id DESC
commented: usefu; post +5

Heres the structure.. I'm testing it in wamp

id int(30)   No None AUTO_INCREMENT               
  topic varchar(250) latin1_swedish_ci  No None                
  comment varchar(1000) latin1_swedish_ci  No None                
  username varchar(30) latin1_swedish_ci  No None                
  timestamp varchar(20)

try out with Shanti Chepru's Query

Thanks Shanti Chepuru, worked like a charm!!...

Thanks Shanti Chepuru, worked like a charm!!...

Mark as solved if ur problem solved.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.