Jump to content
cobusbo

Explode value from database column then count it

Recommended Posts

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

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

 

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.