Distinct values from column of delimiter split strings

[pernest]

Hi

 

I'm having some problems working out the most efficient way of solving my problem.

 

I have a table that stores multiple locations for site members. Two columns, UserId and locations. The problem is that the locations field is a string with delimiters, for example:

London|*|Paris|*|New York|*|Rome

I want to get a list of all distinct locations that are used in the table. Currently I'm pulling out the locations string for each user, exploding it on the delimiter to produce and array. Then concatenate all the arrays, and use array_unique to remove duplicate entries.

 

I can see that my approach is poor as the database grows, with both the number of users will growing, and also the number of possible locations. I would like to shift as much of the calculation from php to MySQL, but I can't see how to do it in this case.

 

Any help would, as always, be greatly appreciated.

 

Cheers

 

Paul

[trq]

I can see that my approach is poor as the database grows, with both the number of users will growing, and also the number of possible locations. I would like to shift as much of the calculation from php to MySQL, but I can't see how to do it in this case.

 

You need to store the locations in a separate table with each user having a new record for each location.

[pernest]

You need to store the locations in a separate table with each user having a new record for each location.

 

Sorry, I should have made it clear in my original post, I'm working with an existing table structure. There is so much code already build on this structure that to deviate from it would be a complete nightmare. I was hoping for some advise on any efficiencies that I make without changing the structure of the table.

[trq]

There really isn't allot you can do without changing the structure. Its never going to be efficient, you have to pull out all data and go from there.

[pernest]

The more I look at it, the more I think I've done the best I can with what I've got to work with. I'm using the table structure set up by the 'community builder' extension to Joomla!, and it seems that they haven't bothered to normalise their database.

 

What I was hoping for was to shift the processing from PHP to MySQL as it seems much faster. I don't know whether it's worth trying the MySQL string functions.

 

There really isn't allot you can do without changing the structure. Its never going to be efficient, you have to pull out all data and go from there.