CODEWITHSUNDEEP
phpmysql
hydravink
hydravink

Reputation: 63

Is it possible to add more text to a text from MySQL?

Okay so I am trying to make this form for teams. Something like:

Football Club Name | W-W-L-D-W (W=win, D=draw, L=lose)

The way I thought I will do this is by adding text. For example, there is this match between FC Apple - FC Banana. Banana wins 0-1. In the team's row from the teams table, the "form" column is modified, adding "-W". Another match for Banana, with Kiwi, which they lose. Again adding "-L" to the column. So now the column is "-W-L". Is this possible?

I've thought about making 5 colums, lastmatch, lastmatch2, ... lastmatch5. When a team wins, lastmatch5 is W. If lastmatch4 is null and the team loses, lastmatch4 will be L. If all lastmatch columns are filled, then make lastmatch1 = lastmatch2, 2=3, 3=4, 4=5 , 5 = null, and its filled by the result.

But this is very complicated... Thats why I thought about the first method. Any other methods? What do you think?

Upvotes: 0

Views: 137

Answers (1)

Mark Miller
Mark Miller

Reputation: 7447

First, to answer your question, you could just grab the current content, like W-W-L-D, from the column, add the recent outcome, like -W, and then update the column with the new string - W-W-L-D-W. But I don't suggest doing it this way. Storing individual records in a long string is probably not the best idea. Instead, create separate relational tables. This is just off the top of my head, but I would consider doing something more like this: 

Table: Football_club
+========================+
|  Id     |     Name     |
|---------|--------------|
|  1      |    Apple     |
|  2      |    Banana    |
|  3      |    Kiwi      |
+========================+
//This table stores each Football club's basic info


Table: Matches
+================================================+
| Id    |   Date    |   Team1_id  |   Team2_id   |   
|-------|-----------|-------------|--------------|
|  1    |2014-05-14 |     1       |     2        |
|  2    |2014-05-15 |     1       |     3        |
|  3    |2014-05-16 |     2       |     3        |
+================================================+
//This table stores basic info about each match.


Table: Match_outcomes
+==================================+
| Id    |  Match_id    | Winner_id |   
|-------|--------------|-----------|
|  1    |     1        |     2     |
|  2    |     2        |    NULL   |
|  3    |     3        |     3     |
+==================================+
//This table stores match outcomes. 
//A winner ID of NULL would mean it was a draw (you could also use 0 or -1 or some other value that is not a team id)

This way, you can always get the number of wins, losses, and draws for any team, or add matches and match outcomes pretty easily.

For example, to get number of wins for team $id:

(I haven't tested this, but I think it would work)

SELECT 
    COUNT(mo.id) AS wins
FROM match_outcomes mo
JOIN matches m ON m.id = mo.match_id
    AND (m.team_id1 = $id OR m.team_id2 = $id)
WHERE mo.winner_id = $id
GROUP BY mo.winner_id

Update - Been messing around with this... here's a sample query to get all wins, losses, and draws for each team using the above DB:

SELECT 
    fc.name,
    SUM(IF(mo.winner_id=fc.id, 1, 0)) AS wins,
    SUM(IF(mo.winner_id!=fc.id, 1, 0)) AS losses,
    SUM(IF(ISNULL(mo.winner_id), 1, 0)) AS draws
FROM match_outcomes mo
JOIN matches m ON m.id = mo.match_id
JOIN football_clubs fc ON fc.id = m.team1_id
    OR fc.id = m.team2_id
GROUP BY fc.name

See this SQL Fiddle

This is just a rough idea - hopefully it's helpful!

Upvotes: 5

Related Questions