0

I am hoping someone can help. I am trying to build something and I would like PHP/MySQL to group results. Here is an example of my table:

id   service service_info service_crew
num  varchar longtext     varchar

In service_crew, the format will be FirstName.LastName but sometimes there can be multiple, such as: John.Doe,Sam.Smith,Alex.Jones or Jordan.Smith

What I would like to do is group results per crew member so an email can be sent with the relevant info for each of the services they will work that day, but due to the amount of crew members and a quick changing environment, it is not possible to have a list of all crew members stored, so it will need to work purely based on the information in this database table.

I hope this makes sense?

Thanks!

Simon
  • 1
  • provide your expected output – Fahmi Nov 23 '18 at 06:25
  • @fa06 I am would like each crew members workings to be available in an array so that the PHP script will automatically be able to send an email to each of them containing all of the services they are due to work. I can handle the email part I just need a way of running maybe a "foreach" for each crew member and then each of their workings? – Simon Nov 23 '18 at 06:31
  • `service_crew` is hopefully not a CSV? Please provide sample data. – user3783243 Nov 23 '18 at 06:33
  • @Simon Better you store the email_Id's of employees also in DB. It will be a feasible solution – lifeisbeautiful Nov 23 '18 at 06:33
  • @user3783243 no, this will be manually inputted. So for example if there is only one crew member working, service_crew would be: John.Doe or if there is more than one it would be John.Doe,James.Smith – Simon Nov 23 '18 at 06:34
  • Don't do that. That is a CSV format. Make it `service_crew_id` and have a `service_crew` table that lists every member of a crew individually. You then can easily do a `join` to get this data. See https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – user3783243 Nov 23 '18 at 06:36
  • @AravindBhatK Yes I understand that would be much more easier of a solution however I need to try and figure a way of not doing that because the environment is changing so much it is unable to keep a database for this system I am building with all crew members. Email ID's don't matter as from John.Smith it will just add (at)domain.com on to the end. – Simon Nov 23 '18 at 06:37
  • @user3783243 See my above comment, I understand that but it's not really feasible for this purpose. I am trying to figure out some other way if it's possible otherwise I could make it so when the service_crew are inputted, it will create a record for them on the DB if there's not already one there – Simon Nov 23 '18 at 06:40
  • 1
    Yes, please go the the other route. If you must you could use a CSV parser, but this really is a bad design and will bite you further down the road. – user3783243 Nov 23 '18 at 06:50
  • Essentially, if you find yourself using the csv route, then there's really no point continuing with sql – Strawberry Nov 23 '18 at 08:31

0 Answers0