MYSQL Count problem

TheCase

Hi,

I have uploaded an image of my database structure. I'm trying to count the following fields if they equal to 0 presenter1status, presenter2statusandengineerstatus`

The following query
SELECT count(*) FROM showrota WHERE presenter1status = '0' AND presenter2status = '0' AND engineerstatus = '0'

Only counts the first field if there is a match not all of the feilds, any ideas

Thanks

leatherback

not sure what you mean there. Should they all be 0, or just one of the three? In case of the latter you should use OR in stead of AND in your query.

TheCase

I was trying to get all of the fields to equal to 0

leatherback

Mysql counts the number of rows, not field. So the query you have there will give you the number of rows where all 3 fields are 3. If you want all the rows that have one of the field with 0, you would have to perform a query with or, instead of and. And subsequentyl you could look through the resulting dataset to count the 0 values. This would be sub-optimal though. It looks like you have a database normilisation issue here. Probably you would do well creating a table 'status'

id | status | person | rotation

That would make such a query much easier.

TheCase

Thanks for that about the count, I didn't realise it was only rows. The problem is with the structure you showed is I want status 1,2 and 3 in the same row on a HTML table and I had problems with that structure as first tried out see topic http://phpbuilder.com/board/showthread.php?t=10380566 should explain a bit better

The table structure is in that post, could you suggest a better one?

Thanks

leatherback

How you want to display should not really affect how your store the data. Those are different parts of the process.

I would probably create
a table
SHOWS: ID | Information | WhateverElse

A table
CONTRIBUTORS
ID | ShowID | ContributorType | Status | Comments

then you can for a specific showID count the number of status == 0.
By using a join on shows.id = contributors.showid you can retrieve info on the show & on the contributors simultanuously. You might just have to loop through the data to create the HTML output you want to show. But that should not be a big issue.

TheCase

Thats a good idea to bring the table into 3NF. Althought normalisation should always be done but for a small table with less than 200 records at one time will the more complex queries such as JOIN and the more hardware it uses I guess jumping between table still be worth normalisation which really just reduces duplicate data so the size of the database.

I guess this sounds like a real newbie question so forgive me 🙂