query to show duplicated entries.

baris22 · 07-26-2010, 10:40 PM

hello all,

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

PHP Code:

   
// 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);

????: NamePros.com http://www.namepros.com/programming/668976-query-to-show-duplicated-entries.html
My question is what do i need to change here to show only duplicated entries according to topic field.

Thanks all.

defaultuser · 07-26-2010, 10:47 PM

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

baris22 · 07-26-2010, 10:50 PM

Originally Posted by defaultuser

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

Thanks for replie.
????: NamePros.com http://www.namepros.com/showthread.php?t=668976
What about the total count for the first query?

thanks

nirvdrum · 07-29-2010, 11:02 AM

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:

Code:

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

If you want to get all duplicated topics, it'd be:
????: NamePros.com http://www.namepros.com/showthread.php?t=668976

Code:

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.

07-26-2010, 10:40 PM	THREAD STARTER #1 (permalink)
baris22 NamePros Member Join Date: Jan 2007 Posts: 98	query to show duplicated entries. hello all, this is what i use to show the the data from mysql. PHP Code: `// 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);` ????: NamePros.com http://www.namepros.com/programming/668976-query-to-show-duplicated-entries.html My question is what do i need to change here to show only duplicated entries according to topic field. Thanks all. __________________ Rapidme (rapidshare application)

07-26-2010, 10:47 PM	#2 (permalink)
defaultuser Senior Member Join Date: May 2009 Location: In My Head Posts: 4,219	SELECT {fields} FROM file ORDER BY topic DESC LIMIT $start, $limit GROUP BY {fields} HAVING COUNT() > 1 __________________ internet@ctivist.com* Defaultuser R.I.P.

07-29-2010, 11:02 AM	#4 (permalink)
nirvdrum New Member Join Date: Jul 2010 Location: Boston, MA Posts: 5 Follow @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: Code: SELECT COUNT() FROM file WHERE topic = '<my_topic>'; If you want to get all duplicated topics, it'd be: ????: NamePros.com http://www.namepros.com/showthread.php?t=668976 Code: 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.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)