I need help on a query.

hello all,

i need son help on a query.

	$query = "SELECT COUNT(*) as num FROM file ";
	$total_pages = mysql_fetch_array(mysql_query($query));
	$total_pages = $total_pages[num];
	
/* 
my codes continues.................
*/

	/* Get data. */
	$sql = "SELECT * FROM file ORDER BY topic DESC LIMIT $start, $limit";
	$result = mysql_query($sql);

PHP:

I get the count for rows for pagination then get the informations in the rows. My question is:

I am trying to show dublicated rows. (I want to get only the dublicated rows according to topic field)

How can i do this?

Thanks alot.

 

Soemthing like this (will need adjusting slightly as i use custom wrappers ect):

Global $db;

$q=$db->query("SELECT * FROM file ORDER BY topic DESC LIMIT $start, $limit");
   while($r=$db->fetch_row($q))
{
//We are now looping every match as it comes in
$checkstring =  $r['topic']; //checkstring = The current fields topic
$checkid = $r['topicid']; //checkid = The topics current id (assuming topicid is a primary auto increacment key) You can do something with it later if needed
if (checkexist($thetopic)==1)
{
//The topic already exist show a error
print "Error that topic already exist";
//Kill page? if you want , or leave it so it dosent mess with ur footer
}
else
{
print "Success topic does not exist :)";
//Do something here
}
}

function checkexist($thetopic)
{
//Checks if a topic already exist , returns 1 if so and 0 if not

Global $db;

$result=$db->query("SELECT * FROM file WHERE topic='{$thetopic}'");
$num_rows = mysql_num_rows($result);
if ($num_rows> 0)
{
return 1;
}
else
{
return 0;
}
}

Code (markup):

 

To select UNIQUE rows only:

SELECT DISTINCT field FROM table;

To select DUPLICATE rows only:
SELECT field FROM table GROUP BY field HAVING ( COUNT(field) = 2 )

To select DUPLICATE, TRIPLICATE or more rows only:
SELECT field FROM table GROUP BY field HAVING ( COUNT(field) > 1 )

Source: http://www.adamsinfo.com/mysql-find-duplicates-only/

 

i could not get it working. Query for Pagination is the problem.

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

PHP:

i can get the result on phpmyadmin by doing:

SELECT topic, COUNT( * ) AS usercount
FROM `file` 
GROUP BY topic
HAVING usercount >1

PHP: