Member Avatar for RazorRamon

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>';

?>
  • 3 Contributors
  • 6 Replies
  • 200 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by karthik_ppts

Dani AI

Generated

Quick diagnosis and why saw that PHP error: mysql_fetch_array() complaining means mysql_query() returned false because the SQL failed. In the original attempt the subquery produced id values but the outer condition compared the wrong column, so the query was invalid. 's GROUP BY trick got a result for you (as noted by ), but it has important caveats below.

GROUP BY without aggregation in MySQL can return non‑deterministic values for columns that aren’t grouped or aggregated (username/timestamp may come from any row in the group). For a deterministic “one row per topic, using the latest id” pattern, join to a derived table that picks the MAX(id) per topic:

SELECT h.username, h.timestamp, h.topic
FROM hotspots AS h
JOIN (
  SELECT topic, MAX(id) AS maxid
  FROM hotspots
  GROUP BY topic
) AS m ON h.topic = m.topic AND h.id = m.maxid
ORDER BY h.id DESC;

If you’re on MySQL 8+ you can also use window functions (clear and concise):

SELECT username, timestamp, topic
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY topic ORDER BY id DESC) AS rn
  FROM hotspots
) t
WHERE rn = 1
ORDER BY id DESC;

Practical fixes and safety notes: change timestamp from VARCHAR(20) to DATETIME/TIMESTAMP so ordering and filtering work correctly; add an index on topic for performance; always check query errors while debugging (example), and move off deprecated mysql_* functions to mysqli or PDO with prepared statements to avoid SQL injection.

$result = mysql_query($sql) or die('SQL error: '.mysql_error());

Recommended Answers

All 6 Replies

Member Avatar for karthik_ppts

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

Member Avatar for Shanti C

@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
Member Avatar for RazorRamon

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)
Member Avatar for karthik_ppts

try out with Shanti Chepru's Query

Edited by karthik_ppts because: n/a
Member Avatar for RazorRamon

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

Member Avatar for karthik_ppts

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.