MySQL, PHP, extracting and formatting multiple values from on database field

Question

I am a beginner and got stuck. Any help will be very much appreciated.

I have a text field in the database, the values in the field are as follow, i.e.: "John: 20; Dan: 30; Greg: 80; Sam: 66; Coco: 97;" this all in one field.

The result that I would like to get is as follows: John is 20 years old Dan is 30 years old Greg is 80 years old

Thank you

Please provide example DB structure. Also please clarify records, are semi-colons separating rows in your example or is that all one row? — chris85
– chris85, Commented Sep 30, 2017 at 16:08
@chris85 he wrote "this all in one field", so I assume, it's all in one field ;o). Vladimir: want the solution to be all mysql, php or does it matter anyway? — Jakumi
– Jakumi, Commented Sep 30, 2017 at 16:12
@Jakumi It'd be better if reproducible code was provided. As is we are guessing here. — chris85
– chris85, Commented Sep 30, 2017 at 16:15
Store your data in two columns (name, age). That would make your live easier. Your next question could be "How do I sort the data by age?" - Which would be easy to answer with two columns. — Paul Spiegel
– Paul Spiegel, Commented Sep 30, 2017 at 16:40

Dr. X · Accepted Answer · 2017-09-30 16:09:43Z

2

Please run below code

UPDATE your_table
SET your_field = REPLACE(your_field, ':', ' is ')

and this replaces ':' character with ' is'

UPDATE your_table
SET your_field = REPLACE(your_field, ';', 'years old')

and this replaces ';', 'years old'

At the end of this query

John: 20; Dan: 30; Greg: 80; Sam: 66; Coco: 97;

above string will be below string

John is 20 years old Dan is 30 years old Greg is 80 years old  Sam is 66 years old Coco is 97 years old

answered Sep 30, 2017 at 16:09

Dr. X

2,9402 gold badges18 silver badges37 bronze badges

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

1 Comment

A l w a y s S u n n y Over a year ago

REPLACE() also works with SELECT, So without updating the column value it is possible to SELECT from the text field as per the output required.

U Gold · Accepted Answer · 2017-09-30 16:04:12Z

0

SELECT name + ' is ' + age + ' years old' 
FROM your_table_name

Here name is the column name of people(John, Dan, Grek) and age is the column name of ages (20, 30, 80)

answered Sep 30, 2017 at 16:04

U Gold

1111 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

MySQL, PHP, extracting and formatting multiple values from on database field

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related