sintax63 Posted August 25, 2008 Share Posted August 25, 2008 I want to display something like this on my site: There are currently 63 lodges in 12 states listed The "63 lodges" part is easy as I am just counting the rows. The number of states is giving me issues however. I have a column in my table called "states" which obviously contains duplicate entries. For example, I have IL in the table 8 times but only want it to count "IL" as 1 to add to the total value. Is something like that possible? Thanks in advance! Link to comment https://forums.phpfreaks.com/topic/121250-counting-entry-values-excluding-duplicates/ Share on other sites More sharing options...
webent Posted August 25, 2008 Share Posted August 25, 2008 Try this... http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html Link to comment https://forums.phpfreaks.com/topic/121250-counting-entry-values-excluding-duplicates/#findComment-625107 Share on other sites More sharing options...
fenway Posted August 25, 2008 Share Posted August 25, 2008 Let's see the query... DISTINCT is not an expression. Link to comment https://forums.phpfreaks.com/topic/121250-counting-entry-values-excluding-duplicates/#findComment-625288 Share on other sites More sharing options...
sintax63 Posted August 25, 2008 Author Share Posted August 25, 2008 Quote Let's see the query... DISTINCT is not an expression. If you are talking to me, Fenway - this is all I have... $lodges = mysql_result(mysql_query("SELECT COUNT(*) FROM lodges WHERE status='1'"),0); Which I am using to just count the rows. I don't have anything for the states yet because I'm not sure how to go about doing it. Link to comment https://forums.phpfreaks.com/topic/121250-counting-entry-values-excluding-duplicates/#findComment-625299 Share on other sites More sharing options...
fenway Posted August 25, 2008 Share Posted August 25, 2008 Sorry, didn't realize you didn't get *any* output yet... ;-) Try this: SELECT COUNT(*), COUNT( DISTINCT states ) FROM lodges WHERE status='1' Link to comment https://forums.phpfreaks.com/topic/121250-counting-entry-values-excluding-duplicates/#findComment-625310 Share on other sites More sharing options...
sintax63 Posted August 25, 2008 Author Share Posted August 25, 2008 $states = mysql_result(mysql_query("SELECT COUNT(*), COUNT( DISTINCT states ) FROM lodges WHERE status='1'"),0); Produced: Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home/public_html/index.php on line 8 Link to comment https://forums.phpfreaks.com/topic/121250-counting-entry-values-excluding-duplicates/#findComment-625314 Share on other sites More sharing options...
fenway Posted August 25, 2008 Share Posted August 25, 2008 You can't use mysql_result() around that query. Link to comment https://forums.phpfreaks.com/topic/121250-counting-entry-values-excluding-duplicates/#findComment-625321 Share on other sites More sharing options...
sintax63 Posted August 25, 2008 Author Share Posted August 25, 2008 Quote You can't use mysql_result() around that query. can I use a mysql_query() then? $states = "SELECT COUNT(*), COUNT( DISTINCT states ) FROM lodges WHERE status='1'"; $showStates = mysql_query($states); Link to comment https://forums.phpfreaks.com/topic/121250-counting-entry-values-excluding-duplicates/#findComment-625341 Share on other sites More sharing options...
sintax63 Posted August 25, 2008 Author Share Posted August 25, 2008 Nope! That doesn't do the trick either... Link to comment https://forums.phpfreaks.com/topic/121250-counting-entry-values-excluding-duplicates/#findComment-625485 Share on other sites More sharing options...
sintax63 Posted August 26, 2008 Author Share Posted August 26, 2008 Any other suggestions? I've tried several things and still got nothing. :-\ Link to comment https://forums.phpfreaks.com/topic/121250-counting-entry-values-excluding-duplicates/#findComment-625969 Share on other sites More sharing options...
fenway Posted August 26, 2008 Share Posted August 26, 2008 Quote Nope! That doesn't do the trick either... How does it "not do the trick"? The query executes correctly, right? You need to use fetch the appropriate columns, though -- you should add column aliases and use mysql_fetch_assoc(). Link to comment https://forums.phpfreaks.com/topic/121250-counting-entry-values-excluding-duplicates/#findComment-625997 Share on other sites More sharing options...
sintax63 Posted August 26, 2008 Author Share Posted August 26, 2008 By that I just meant that I was not getting any output from that code. Link to comment https://forums.phpfreaks.com/topic/121250-counting-entry-values-excluding-duplicates/#findComment-626007 Share on other sites More sharing options...
fenway Posted August 26, 2008 Share Posted August 26, 2008 Quote By that I just meant that I was not getting any output from that code. Output how? Post the code you're using the display the result of this query.. all I see above is the query() call. Link to comment https://forums.phpfreaks.com/topic/121250-counting-entry-values-excluding-duplicates/#findComment-626064 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.