what kind do I have to put in MySQL to get two values

I think the best solution would be to treat that data as varchar, and in the case you need to show or treat the numbers separately, when you pick it up again on the web, divide it with the split ()

And in the case that you do not need to deal with the numbers separately, I would simply show it as varchar and would not complicate the functionality unnecessarily.

Greetings, I hope it helps.

As we have said, I will show you an example of a solution.

In this case the correct thing would be to have two columns of type INT that store the goals for and against. If for some reason you want to represent like this: 40:20 , that can be done in the output. In BD, it is convenient to save each data as an entity of its own and with its own data type. Never make the mistake of saving a numeric or date data as VARCHAR . You will have problems to the larta to keep the data and to present them.

Suppose you are asked for the total of goals in favor separately, or the total of goals in favor of the overall, or the total of goals in 2014, or the total of goals against when you played with the X team. It will be very difficult to calculate this data if you have in a column VARCHAR two data in one. Moreover, it may even occupy more space in the BD than having two INT ...

So to solve this, you would have to:

• create two new columns of type INT . In the example I have called them a_favor and en_contra .
• make a query that obtains on the one hand the values that are before : and on the other hand those that are after the : , and assign those values to the columns cited above.

This query would be something like this:

UPDATE validar_goles SET 
     a_favor= SUBSTRING_INDEX( goals , ':', 1 ),
     en_contra= SUBSTRING_INDEX(SUBSTRING_INDEX( goals , ':', 2 ),':',-1) ;

This does not have to be applied anymore, but I put it here in case you ever need to

If you wanted to validate the data in the column goles to a data really DECIMAL , you could do creating a new column, which I called decimal_goals , join the table with itself replacing the two points of the column goals for a punto , and inserting that value in the new decimal column. What the REPLACE will do is create a parseable data to DECIMAL that will allow you to register the values correctly in your new column of type DECIMAL .

The query would look like this:

 UPDATE validar_goles AS t2
    LEFT JOIN validar_goles AS t1 ON (t1.ID = t2.ID)
    SET t1.decimal_goals = REPLACE(t2.goals,':','.');

  

NOTES:

     

These queries would update the entire table with a single execution, you would not have to execute them for each row.

  

Therefore, it is always a good idea to make a backup copy of the table before making these changes .

  

It is possible that if the current column GOALS of your table is VARCHAR some values are entered incorrectly. This is another problem added when we use data types that are not adequate. There may be values with blanks, with any characters other than numbers or colons. Anyway, the query could fail in those values and you will have to correct them. For the question of blank spaces you can clean the column with appropriate functions such as TRIM .

  

Here's a proof of concept:

VIEW DEMO IN REXTESTER

CREATE TABLE IF NOT EXISTS validar_goles 
(
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    goals VARCHAR(10),
    decimal_goals DECIMAL(10,2),
    a_favor INT,
    en_contra INT
)ENGINE=INNODB;

INSERT INTO validar_goles (goals)
    VALUES 
        ('50:14'),
        ('41:22'),
        ('30:26')
;

SELECT * FROM validar_goles;

UPDATE validar_goles SET 
     a_favor= SUBSTRING_INDEX( goals , ':', 1 ),
     en_contra= SUBSTRING_INDEX(SUBSTRING_INDEX( goals , ':', 2 ),':',-1) ;

UPDATE validar_goles AS t2
LEFT JOIN validar_goles AS t1 ON (t1.ID = t2.ID)
SET t1.decimal_goals = REPLACE(t2.goals,':','.');

SELECT * FROM validar_goles;

Result:

This is the table before the changes:

ID  goals   decimal_goals   a_favor     en_contra
1   50:14   NULL             NULL        NULL
2   41:22   NULL             NULL        NULL
3   30:26   NULL             NULL        NULL

And after the changes:

ID  goals   decimal_goals   a_favor      en_contra
1   50:14   50,14            50          14
2   41:22   41,22            41          22
3   30:26   30,26            30          26