Query to show duplicated entries

Databases
1

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.

2

Describe in your own words, in plain english, what you want to achieve. Maybe with some data example, if that makes things clearer.
When we understand what you want, then we can look at the code.

3

4

Untested:


SELECT c.*,COUNT(topic) AS totalTopic FROM `file` AS c GROUP BY topic HAVING totalTopic>1 ORDER BY topic DESC;
5 

SELECT COUNT(*)
FROM file  AS f1 
INNER JOIN file AS f2 
ON f1.topic = f2.topic 
AND f1.id != f2.id
6

ok, i sorted the secon part. i can get the right data:



$sql = "SELECT * FROM file  As f1 INNER JOIN file As f2 ON (f1.topic = f2.topic AND f1.id != f2.id) ORDER BY f1.topic DESC LIMIT $start, $limit";

what can i do to get the right count on here?


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

thank you very much for your help. it is working perfect now.

8

Can you give an example with some data? The data in the database table, and the data shown on the page.

9

this is for pagination,



// 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 have got another 10-15 lines before the next query*/

this is for the data



// i use this to get the data 

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

no luck, it did not work. i tried many different queries but i can not get the count for pagination and data

11

ok, sorry for misunderstanding.

At the moment i am using this codes to get the data from database. I use the first query to count the rows and second query to get the data.

I want to change this code to show dublicated entries. I don`t want to show all the data, I only want to show dublicates on topic field.

I want to count the rows where topic field has got the same entries and i want to show the rows where topic field has got the same value.

I hope i explained what i would like to do.



	$query = "SELECT COUNT(*) as num FROM file";
	$total_pages = mysql_fetch_array(mysql_query($query));
	$total_pages = $total_pages[num];
	
	/* Setup vars for query. */
	$targetpage = $cat_name.'.php'; 	//your file name  (the name of this file)
	$limit = 20; 								//how many items to show per page
	$page = $_GET['page'];
	if($page) 
		$start = ($page - 1) * $limit; 			//first item to display on this page
	else
		$start = 0;								//if no page var is given, set start to 0
	
	/* Get data. */
	$sql = "SELECT * FROM file ORDER BY topic DESC LIMIT $start, $limit";

	$result = mysql_query($sql);
12

What do you mean? You supposed to retrieve the duplicated records as per the topic column. Now you saying ‘pagination and data’. What is the purpose?