baris22 Posted July 27, 2010 Share Posted July 27, 2010 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. Quote Link to comment Share on other sites More sharing options...
RussellReal Posted July 27, 2010 Share Posted July 27, 2010 try this SELECT * FROM file As f1 INNER JOIN file As f2 ON (f1.topic = f2.topic AND f1.id != f2.id) Quote Link to comment Share on other sites More sharing options...
baris22 Posted July 27, 2010 Author Share Posted July 27, 2010 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 Quote Link to comment Share on other sites More sharing options...
RussellReal Posted July 27, 2010 Share Posted July 27, 2010 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 Quote Link to comment Share on other sites More sharing options...
baris22 Posted July 27, 2010 Author Share Posted July 27, 2010 i get an error when i add this ORDER BY topic DESC LIMIT $start, $limit Quote Link to comment Share on other sites More sharing options...
RussellReal Posted July 27, 2010 Share Posted July 27, 2010 what is the error.. I am pretty sure it should work Quote Link to comment Share on other sites More sharing options...
baris22 Posted July 27, 2010 Author Share Posted July 27, 2010 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. Quote Link to comment Share on other sites More sharing options...
RussellReal Posted July 27, 2010 Share Posted July 27, 2010 oooo you should order by f1.topic.. although the problem could be with the limit if you're forgetting to set $start and $limit Quote Link to comment Share on other sites More sharing options...
baris22 Posted July 27, 2010 Author Share Posted July 27, 2010 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. Quote Link to comment Share on other sites More sharing options...
RussellReal Posted July 27, 2010 Share Posted July 27, 2010 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 Quote Link to comment Share on other sites More sharing options...
baris22 Posted July 27, 2010 Author Share Posted July 27, 2010 thank you so much, it is sorted now. Quote Link to comment Share on other sites More sharing options...
RussellReal Posted July 27, 2010 Share Posted July 27, 2010 anytime:) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.