SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot clarnp49's Avatar
    Join Date
    Sep 2004
    Location
    Bowie MD
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Count Filled Columns Per Id

    This is probably pretty easy but I'm pulling a blank.
    I have a database (structure below) that I want to see how many items are associated with each ID

    Example of what I am looking for

    Select file1 thru file10 and count how many of these fields have a value in them.

    Code:
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `title` varchar(100) NOT NULL,
      `cat_id` int(11) NOT NULL,
      `file1` varchar(100) NOT NULL,
      `file2` varchar(100) NOT NULL,
      `file3` varchar(100) NOT NULL,
      `file4` varchar(100) NOT NULL,
      `file5` varchar(100) NOT NULL,
      `file6` varchar(100) NOT NULL,
      `file7` varchar(100) NOT NULL,
      `file8` varchar(100) NOT NULL,
      `file9` varchar(100) NOT NULL,
      `file10` varchar(100) NOT NULL,
    PRIMARY KEY (`id`)
    I can count how many records I have in the database but for each record I want to know how many files I have.
    If it works don't fix it. If it's broke fix it.
    He who knows all is the smartest.
    He who doesn't know anything isn't dumbest.

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Are you needing to do this in MySQL or in PHP? I'm assuming MySQL would be preferred?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Zealot clarnp49's Avatar
    Join Date
    Sep 2004
    Location
    Bowie MD
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I would like to use PHP to pull the information from my MYSQL database. If this belongs in the MYSQL forum, my apologies just thought it was PHP code.
    If it works don't fix it. If it's broke fix it.
    He who knows all is the smartest.
    He who doesn't know anything isn't dumbest.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by clarnp49 View Post
    I can count how many records I have in the database but for each record I want to know how many files I have.
    you need to redesign this table

    so instead of one row with up to 10 values in the row, you would have one column, and up to 10 rows

    then the count becomes trivial -- you just count the rows that are there and don't bother with the ones that aren't

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot clarnp49's Avatar
    Join Date
    Sep 2004
    Location
    Bowie MD
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Can you suggest a way to redo this table? I think I can see it but just want a quick visual. Appreciate the help!
    If it works don't fix it. If it's broke fix it.
    He who knows all is the smartest.
    He who doesn't know anything isn't dumbest.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    CREATE TABLE widgets
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , title VARCHAR(100) NOT NULL
    , cat_id INTEGER NOT NULL
    );
    
    CREATE TABLE widgetfiles
    ( id INTEGER NOT NULL REFERENCES widgets (id )
    , file VARCHAR(100) NOT NULL
    , PRIMARY KEY ( id,file )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •