Using PHP/MySQL, how can I SELECT from table with multi-value column content

Question

This one has me stumped. I am trying to create a report from a table that has company data, including two columns both of which can have multiple values. Example: A company can be classified as falling into one or more of groups:

Sector: Gold; Precious Metals; Diamonds;... Industry: Mining; Refining; Marketing;...

I need to produce an alphabetic list of companies by Sector/Industry. My first approach was to do something like this :

$request = "SELECT sector, industry, name, uid FROM colist ORDER BY LOWER(industry),LOWER(sector) ASC"  ;

But this gives me a result ordered only by the first of the multiple elements. So a structured cascading view by Industry looks like this at the top level:

In this example, it doesn't explode the rows that have multiple values into separate entries for (Gas Utilities / Midstream - Oil and Gas) and (Gold Mining / Gold & Silver) and (Generic Pharmaceutical / Speciality Pharmaceutical).

To make it even harder, I need to be able to pick a range alphabetically, otherwise the view is unmanageable for users. Again in the example, if I choose letter M, then the entry for the company that has Gas Utilities coming ahead on the list doesn't show.

Is it possible to solve. Any ideas will be appreciated.

Answer 1

To clarify the question first ... since no-one could try to answer. My database contains information about companies' business activities, which needs to be classified in reporting. One of the elements of the data is selected from a list of possible values in a checkbox. Example, a company can be classified as working in several different areas (let's say eg Gold Mining, Gold Refining, Exploration, Metals). Next, I need to pull an alphabetic list (letter by letter) of all companies, selected by business activity.

The solution I was trying worked, but only with the first element in the list of activities. Using the example, if I looked for activities starting with letter "G", it would return the company because the column does start with the letter "G", but if I looked for letter "E", it would not find the company, because the column doesn't start with the letter "E" even though the company does classify as "Exploration.

The answer is ... A column in a row can used be as an array to store multiple values, but only the programmer's code that creates and reads it knows that. SQL doesn't understand arrays - to SQL the row/column content is just a string of characters. Using the example above, although I think of the data as a list of separate elements in an array, like this:

Gold Mining
Gold Refining
Exploration
Metals

to SQL, that simply looks like Gold Mining,Gold Refining,Exploration,Metals, so no logic can be applied directly.

But, as long as due care is taken to use the right separator (and comma is a very bad choice), then you can use some SQL code to find all elements in an array even though SQL doesn't know it.

Using the special character %, together with LIKE, you can match the pattern that you know separates the elements. Using a better example, if you create a more specialized separator, preferably one that would never be used inside your text, then things will work. Taking my old list, and adding a more specific separator, you get text looking like Gold Mining~Gold Refining~Exploration~Metals. In my own code, I can create an array from this and handle it any way I want, like this ...

$chosen = explode("~" , $row['industry']) ;foreach ($chosen as $choice){...do something};

Now to SELECT each element in the string separately, you need to tell SQL what to look for as the separator, like this ...

$request = "SELECT sector, industry, company_id, name, stat FROM companies WHERE isopen <> '' AND ( industry LIKE '". $sk . "%' OR industry LIKE '%;". $sk . "%' ORDER BY LOWER(industry),LOWER(sector) ASC"  ;}

$sk in the code is a single-letter that the user can pick from the alphabet, indicating what the starting letter of the industry is.

The necessary pieces are to tell SQL to look for the pattern containing the separator. Using the example of looking for letter "E", it can be in two forms, either E.... if it's the first element in the "array", or ...~E... if it's a subsequent element.

Finally, got it. Hope this helps someone else.