Query to show duplicated entries.

[baris22]

hello all,

this is what i use to show the the data from mysql.

// i use this to get the total count for pagination.

$query = "SELECT COUNT(*) as num FROM file ";    
$total_pages = mysql_fetch_array(mysql_query($query));    
$total_pages = $total_pages[num];    

/* my codes continues.................*/   


// i use this to get the data
$sql = "SELECT * FROM file ORDER BY topic DESC LIMIT $start, $limit";   
$result = mysql_query($sql);

My question is what do i need to change here to show only duplicated entries according to topic field.

Thanks all.

 

[DU]

SELECT {fields} FROM file ORDER BY topic DESC LIMIT $start, $limit GROUP BY {fields} HAVING COUNT(*) > 1

 

[baris22]

SELECT {fields} FROM file ORDER BY topic DESC LIMIT $start, $limit GROUP BY {fields} HAVING COUNT(*) > 1

Thanks for replie.
What about the total count for the first query?

thanks

 

[nirvdrum]

I'm not sure I follow what you're looking to do in the first query. You're selecting a single row, i.e., the count of the entire table. If you want the count for just a particular topic, it'd be:

SELECT COUNT(*) FROM file WHERE topic = '<my_topic>';

If you want to get all duplicated topics, it'd be:

SELECT topic, COUNT(*) FROM file GROUP BY topic HAVING COUNT(*) > 1;

Note that this will not return a row of tuples consisting of the topic name and its count. Selecting just the bare count in this case wouldn't make much sense because you wouldn't have any way to tie it back to the topic.