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.



  `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.

Are you needing to do this in MySQL or in PHP? I’m assuming MySQL would be preferred?

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.

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

:smiley: :smiley:

Can you suggest a way to redo this table? I think I can see it but just want a quick visual. Appreciate the help!

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 )
);

:slight_smile: