COUNT Problem / Multiple Columns

cristal777

Hi,

I am a newbie on the MySQL front hopefully you guy's can help:

OK I have a table that collates soccer team player bookings, in my table I have the follow columns:

ID <---- Unique ID
TEAMID <---- Team Name
HOMETEAM <---- Name of Home Team
AWAYTEAM <---- NAme of Away Team
HOMEYELLOW1 <---- Name of 1st HOME player booked in match
HOMEYELLOW2 <---- Name of 2nd HOME player booked in match
HOMEYELLOW3 <---- Name of 3rd HOME player booked in match
etc etc
HOMERED1 <---- Name of 1st AWAY player sent off in match
HOMERED2 <---- Name of 2nd AWAY player sent off in match
etc etc
AWAYYELLOW1 <---- Name of 1st AWAY player booked in match
AWAYYELLOW2 <---- Name of 2nd AWAY player booked in match
AWAYYELLOW3 <---- Name of 3rd AWAY player booked in match
etc etc
AWAYRED1 <---- Name of 1st AWAY player sent off in match
AWAYRED2 <---- Name of 2nd AWAY player sent off in match

Basically and I am unsure if this is possible at all, say for example player name 'SMITH' was booked in numerous games: i.e:

SMITH booked in match #1 (HOMEYELLOW3)
SMITH booked in match #4 (AWAYYELLOW2)
SMITH booked in match #15 (HOMEYELLOW1)
SMITH sent off in match #26 (AWAYRED2)

I am looking for a query that will search across all the HOMEYELLOW, HOMERED, AWAYYELLOW, AWAYRED columns for the string 'SMITH' and then COUNT them up,giving me a total.

I am unsure if I can do this across multiple columns etc?

Apologies if I have not made sense lol and thanks in advance for any help/assistance provides.

Thanks,

Jack_McSlay

no offense, but I believe there's no straight way to do so because the way you did this table is against the rules of proper database design.

The best practice is to make another table and store each game in one row. I see no solution for this table you've done other than apply the count for all fields and then sum up the result

Piranha

As Jack says there is a design issue with your database. You should read up on database normalization, normalize the database to at least 3NF and then it is pretty easy to do what you want. There is an article on phpbuilder.com about this.

If you need someone to check if the database is properly normalized please don't hesitate to post your suggestion and ask if it is normalized.

cristal777

Thanks for your replies Piranha and Jack McSlay it is much appreciated.

I am a real newbie to this database lark 🙂

I know its wrong but I basically have one large table:

ID
TeamID
MatchID
Referee
HomeTeam
AwayTeam
Score
HomeYellow1
HomeYellow2
etc
HomeRed1
etc
AwayYellow1
AwayYellow2
etc
AwayRed1
etc

When 2 teams play each other I have almost 2 identical rows apart from the TeamID part.

If possible and if it does not take up to much time could you suggest a better database / table(s) format. Any help is most appreciated,

Many thanks,

Piranha

I will help you out a lot with this issue. The help is to do nothing, that way I hope that you will read up on database normalization and learn about it instead of just using what others do.

Of course, if you have a specific question you need help with don't hesitate to ask.