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.

[RussellReal]

try this

 

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

[baris22]

 

 

Thank you very much, it worked kind of.

 

How can i add this to the code

 

 

ORDER BY topic DESC LIMIT $start, $limit

 

 

and How can i get the right count for pagination on here?

 

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

 

 

Thanks alot

 

 

[RussellReal]

for question part 1.. just add it to the code

 

and for question part 2.. you could insert all the rows to a temporary table...

 

and then count the results from the temporary table.. then output them

that way you don't need to do the same query twice

[baris22]

 

 

i get an error when i add this

 

ORDER BY topic DESC LIMIT $start, $limit

[RussellReal]

what is the error.. I am pretty sure it should work

[baris22]

if i do this

 

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

 

i get thie error

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in index.php on line 191

 

that line is

 

while($row = mysql_fetch_array($result))
	{

 

if i remove ORDER BY topic, it works.

[RussellReal]

oooo you should order by f1.topic.. although the problem could be with the limit if you're forgetting to set $start and $limit

[baris22]

 

 

thank you for your help, it is working perfect now.

 

$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";

 

is there any way i can get the right count on here for the pagination by changing the query?

 

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

 

thanks again.

 

 

[RussellReal]

temporary tables..

 

CREATE TEMPORARY TABLE temp SELECT * FROM file  As f1 INNER JOIN file As f2 ON (f1.topic = f2.topic AND f1.id != f2.id) ORDER BY f1.topic

 

^^ that will create a temporary table called 'temp' which will hold all of your duplicate rows..

 

then you get the total number like this:

 

SELECT COUNT(*) As num FROM temp

 

then you do your pagination like this:

 

SELECT * FROM temp LIMIT $start, $limit

[baris22]

 

 

 

thank you so much,

 

it is sorted now.

 

 

 

[RussellReal]

anytime:)