1

I am quite new to PHP and MySQL, but have experience of VBA and C++. In short, I am trying to count the occurrences of a value (text string), which can appear in 11 columns in my table.

I think I will need to populate a single-dimensional array from this table, but the table has 14 columns (named 'player1' to 'player14'). I want each of these 'players' to be entered into the one-dimensional array (if not NULL), before proceeding to the next row.

I know there is the SELECT DISTINCT statement in MySQL, but can I use this to count distinct occurrences across 14 columns?

For background, I am building a football results database, where player1 to player14 are the starting 11 (and 3 subs), and my PHP code will count the number of times a player has made an appearance.

Thanks for all your help!

Matt.

Improve this question
1
  • It is possible to do what you are asking but you might want to redesign your table, player1...14 doesn't sound right. Commented Mar 23, 2013 at 18:06

3 Answers 3

Reset to default
1

Rethink your database schema. Try this:

Table players:
player_id
name

Table games:
game_id

Table appearances:
appearance_id
player_id
game_id

This reduces the amount of duplicate data. Read up on normalization. It allows you to do a simple select count(*) from appearances inner join players on player_id where name='Joe Schmoe'

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks Joe, it started life as an Excel VBA project - so I now have tables titled 'Matches', 'Players', 'Teams' (containing records against other opposing teams, P,W,D,L etc..) - The 'Matches' table is the main driver, containing one row for each game played (by Hull City) - and columns included scorers(1-11) and players(1-14) - hence my need for this (to update the 'Players' table with appearance, and goalscoring totals) - whenever a new 'Match' is added. Thanks for the pointers, I will see if I can re-design the schema. Really appreciated!
0

First of all, the database schema you're using is terrible, and you just found out a reason why.

That being said, I see no other way then to first get a list of all players by distinctly selecting the names of players into an array. Before each insertion, you would have to check if the name is already in the array (if it is already in, don't add it again).

Then, when you have the list of names, you would have to run an SQL statement for each player, adding up the number of occurences, like so:

SELECT COUNT(*)
FROM <Table>
WHERE player1=? OR player2=? OR player3=? OR ... OR player14 = ?

That is all pretty complicated, and as I said, you should really change your database schema.

Improve this answer

1 Comment

Thanks Geier, really appreciate those comments - I know it makes for a poor schema, and subsequently messy queries and PHP code - and I will look to see if I can improve it.
0

This sounds like a job for fetch_assoc (http://php.net/manual/de/mysqli-result.fetch-assoc.php).

If you use mysqli, you would get each row as an associative array.

On the other hand the table design seems a bit flawed, as suggested before. If you had on table team with team name and what not and one table player with player names.

TEAM
| id | name | founded | foo |

PLAYER
| id | team_id | name | bar |

With that structure you could add 14 players, which point at the same team and by joining the two tables, extract the players that match your search.

Improve this answer

1 Comment

Yes, I think I understand what you mean - I think I need another table to reference each Player with a Match (see my response to Joe on the question above, for a background) - and maybe a column for 'goals_scored' as well.. (as it could be more than one, but very rare for Hull City) ;-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.