Explode value from database column then count it

[cobusbo]

Hi I got a column in my database containing profile information in an array form, but I want to select a specific part the gender of the person from the column and count the males and female values

 

My database column contains the value

en,ZA,1991-01-30,Male,1

I know I can explode the values like this

$str = explode(',', $query);
$answ = $str[3];

but I don't know how to implement it for the database to count the amount of values named Male and Female

 

[Barand]

Store that data correctly in five separate columns, instead of cramming it all into a single column in that ridiculous manner, and your problem goes away.

[cobusbo]

Store that data correctly in five separate columns, instead of cramming it all into a single column in that ridiculous manner, and your problem goes away.

I know it would had been much easier to store it seperatly but the info is already stored like that for 900+ users so it will be difficult to reconfigure it like that without losing info and all the info is placed into an array by retrieving a header

$_SERVER["HTTP_X_MXIT_PROFILE"];

so its not me cramming it all together

[ginerjm]

As Barand said - you REALLY need to normalize (look it up) this data. Not a good table design at all!

 

Create a new table with the appropriate cols for your data. Then write a script to query the whole table, then loop thru the results and for each record do your explode (and grab any other fields you haven't told us about) and do an insert query into the new table to post the individual values in their respective columns. Voila - a proper designed table with all your data.

[Barand]

 

so its not me cramming it all together

 

No it isn't, but it is you that isn't splitting it before storing it

 

You can try

SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(profile_info, ',', 4), ',', -1) as gender
  , COUNT(*) as total
FROM mytable
GROUP BY gender