cobusbo Posted December 31, 2015 Share Posted December 31, 2015 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted December 31, 2015 Share Posted December 31, 2015 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. Quote Link to comment Share on other sites More sharing options...
cobusbo Posted December 31, 2015 Author Share Posted December 31, 2015 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 Quote Link to comment Share on other sites More sharing options...
ginerjm Posted December 31, 2015 Share Posted December 31, 2015 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted December 31, 2015 Share Posted December 31, 2015 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.