SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    This is all Mumbo Jumbo Amit's Avatar
    Join Date
    Oct 2001
    Location
    New Delhi, India
    Posts
    277
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT Query ti get a SET field

    Hi,

    I need help ...

    I have a field in one of my table of set .... as
    fld set('a','b','c','d','e' ....... 'z') NOT NULL

    And in the data I have something like ....
    Code:
    rows  data
    1     'a'
    2     'a','c'
    3     'c','e','x','z'
    and so on ....
    Now what I want to do is get count of how many elements are there e.g

    Code:
    count  data
    39       'a'
    35       'b'
    25       'c'
    and so on ....
    using a single select query ...

    The queries I used without success ...
    SELECT COUNT(*) AS count, fld FROM table GROUP BY fld ORDER BY count DESC

    I even used the following functions in various combination find_in_set(), elt(), bit_count(), <<
    As you can see, I even tried using as bit comparison and also treating the fld set as number doing bit computing ... but (

    Any idea / suggestions ?

  2. #2
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm pretty sure your problem is that you're Counting all the fields with COUNT(*) and then you go on and also select "fld" which you are already counting, so its being selected twice so to speak. Just run COUNT() one one field, like your primary key field.



    or an easy way to count the number of rows is

    $numresults=mysql_num_rows($ResultSet);

    Which would be done by PHP, not your SQL.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  3. #3
    This is all Mumbo Jumbo Amit's Avatar
    Join Date
    Oct 2001
    Location
    New Delhi, India
    Posts
    277
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for help BUT .. tried that and it didn't work

  4. #4
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this
    PHP Code:
    $sql "SELECT * from yourtable";
    $result mysql_query($sql);

    while(
    $row mysql_fetch_array($result))
    {
      
    str_replace(" a""a"$row[fld]); // we do this because if $row[fld] begins with a, it won't be counted.
      
    $occurances substr_count("row[fld]""a");
      if(
    occurances 1)
      {
        
    $times "times";
      }
      else
      {
        
    $times "time";
      }
      echo 
    "'a' occured $occurances $times.";

    Last edited by Defender1; Nov 4, 2001 at 20:40.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  5. #5
    This is all Mumbo Jumbo Amit's Avatar
    Join Date
    Oct 2001
    Location
    New Delhi, India
    Posts
    277
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Defender ... I want to get the count for all the elements of the set i.e for 'a' and for 'b' and for 'c' and so on ...

    Also the rows in my table are large ... > 1000 and I would run this query / data atleast once a day so your solution may have a performance drag ...

    Thanks for helping though ...

    Any more ideas ... PLEASE

  6. #6
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yea, that would have a bit of a performance drag.
    but you could do that for as many sets as you want by just adding another str_replace() and substr_count() in the while loop.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •