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! Quote Link to comment 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 Quote Link to comment 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. Quote Link to comment Share on other sites More sharing options...
sintax63 Posted August 25, 2008 Author Share Posted August 25, 2008 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. Quote Link to comment 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' Quote Link to comment 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 Quote Link to comment 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. Quote Link to comment Share on other sites More sharing options...
sintax63 Posted August 25, 2008 Author Share Posted August 25, 2008 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); Quote Link to comment 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... Quote Link to comment 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. :-\ Quote Link to comment Share on other sites More sharing options...
fenway Posted August 26, 2008 Share Posted August 26, 2008 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(). Quote Link to comment 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted August 26, 2008 Share Posted August 26, 2008 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. 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.