0

I am stuck with a MySQL table created by another developer. Its a holiday cruise table with fields like destinations, ships and sailing dates (among other fields). 

Destinations   Ships      Sailing Dates

Antarctica     MS QE2     2 Dec 2018, 1 Jan 2019

Antarctica     MS TBS     2 Feb 2019, 3 Mar 2019

Antarctica     MS JRB     9 Nov 2018, 5 Dec 2018

As indicated, each ship can have multiple sailing dates, entered as comma separated text. But I am interested only in the first sailing date of each row.

For example, "2 Dec 2018" is the first date on row one. Likewise, "2 Feb 2019" is the first date on row two, and so on.

I need a PHP SELECT statement to sort this entire table on the first dates of each row. To make things simpler, I thought of adding another date field to the table, and copy the first dates of each row onto that new date field, and then sort the table based on that field. But that is a round-about solution, which I don't prefer.

Is there a simple PHP/SQL statement which can achieve this? I thought of the PHP Explode() function. But I am not sure how I can incorporate that into a SELECT statement. Any help would be highly appreciated.

Update: Thanks for your suggestions. I totally agree, this table is a mess. Let's say I move the dates to a new table, with multiple rows for each row in the old table, like the following. 

Destinations   Ships      Voyage ID

Antarctica     MS QE2     1

Antarctica     MS TBS     2

Antarctica     MS JRB     3

Dates Table

--Voyage ID------Sailing Dates

-------1------------2 Dec 2018

-------1------------1 Jan 2019

-------2------------2 Feb 2019

-------2------------3 Mar 2019

-------3------------9 Nov 2018

-------3------------5 Dec 2018

Now, what SQL command should I use to sort the old table by the first dates of each Voyage ID in the new DATES table.

Jess
  • 1
  • 2
  • maybe have a look at: http://psoug.org/snippet/mySQL-Sort-comma-separated-string_500.htm – Abhishek Keshri Sep 19 '18 at 09:17
  • 3
    Since this seems to be software that is now worked on, and probably will be worked on by someone else in the future, I suggest you do the only right thing, and that is to normalize the database. You probably know this, since you blaim someone else for it, but do you really want to kick this can down the road for someone else to clean it up? – KIKO Software Sep 19 '18 at 09:18
  • 2
    The best practice is to remove the field 'Sailing Dates' from the table and create a new table with a one to many relation having 'foreign_key','departure_date'. This way you will be able to do any MYSQL operation by adding a Join with the new table without any trouble. Always good to refactor the code base than making things more complex. – Gaurav Mehra Sep 19 '18 at 09:21
  • Possible duplicate of [SQL split values to multiple rows](https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows) – Tobias F. Sep 19 '18 at 09:27
  • Thank you, everyone, for your very valuable suggestions. I'll check each one against my given constraints and see which one I can implement successfully. – Jess Sep 19 '18 at 14:15

1 Answers1

2

You are suffering from a horrible database design. Instead of having one table for the ships, one for destinations, and one for actual trips, you have just one table. This is not what a relational database is supposed to be.

Moreover dates are not stored as dates but as strings and there are even multiple dates in one string. It can hardly be worse. So the best advice is to re-write the whole database.

If you don't want to re-design the database or if you are not allowed to, then my advice is: Do not try to tackle this in a SQL query. Simply read all records from the database instead and then do the string manipulation and sorting in PHP.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • May you add a paragraph about a possible *fixme* Skript? It is possible to do this in SQL, but it's not a very clean solution [see here](https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows) . You'll be better of with a small PHP script. – Tobias F. Sep 19 '18 at 09:21
  • @Tobias F.: Do you mean a script to fix the database design? – Thorsten Kettner Sep 19 '18 at 09:23
  • at least to get rid of those multi-values, probably almost the same as creating a new table for the dates as mentioned by @Gaurav Mehra in the comments of the question – Tobias F. Sep 19 '18 at 09:25
  • 1
    @TobiasF. You already posted a link to a script that can be used to order the data based on the un-normalized design. You probably could set this question as a duplicate instead. – feeela Sep 19 '18 at 09:25
  • @Thorsten. I think I will read the data into a PHP array and then sort the array, as you've suggested (if I am not allowed to edit the database). – Jess Sep 19 '18 at 14:20