#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    22
    Rep Power
    0

    Need help with counting in json field


    Hi,

    I have a table which holds the info of website's article categories and one table that holds info of articles. An article can be added under many categories and this is done with json encode so an article's category field could be like : ["17","18","19"].

    What i want to do is a list with the categories sorted by how many articles each category has and i dont know how to do that I dont know how to count the articles of each category since the categories of each article are stored in json format.

    PROJECT INFO TABLE
    ***************
    project_id,
    project_title,
    project_text,
    project_categories (<- JSON ENCODED)

    PROJECT CATEGORIES TABLE
    ******************
    category_id,
    category_name,
    category_parent

    Any help appreciated,

    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    Originally Posted by BlazeMike
    I dont know how to count the articles of each category since the categories of each article are stored in json format.
    it would be a total piece of cake to do the counts, if you had designed the tables properly

    your json encoding violates first normal form, so if you want to fix that, you'll have to introduce a many-to-many relationship in your design

    as it stands, all you can do is read ~all~ the articles, and do the counting in php arrays
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    22
    Rep Power
    0
    Ok would this be correct instead? :

    Delete project_categories field and create a table named "member_categories" and have the following fields :

    id,
    article_id,
    article_selected_cat

    so if an article is under 2 categories that would be

    ID | ARTICLE | CAT
    ****************
    1 | 20 | 15
    1 | 20 | 23

    and then

    SELECT COUNT(*) as count FROM member_categories GROUP by article_selected_cat ORDER BY count DESC
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    yeah, that's almost perfect

    remove the superfluous id column, and make { article_id, article_selected_cat } the (composite) primary key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    you can also "re-create" your json string by using the GROUP_CONCAT function in another query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo