i have the following details in mysql as
user_id follow_user_id
1 2,3,3,3,3
5 1,2,3,3,3,3
6 1,2,3,3,3,3
i write the following code to get the unique code as follow:
SELECT LENGTH( follow_user_id ) - LENGTH( REPLACE( follow_user_id, ',', '' ) ) +1 AS no_of_follow FROM follow WHERE user_id =1;
but it provide the result :6 i need exactly unique rows: i.e:4
plz help me thanks & regards in advance
Apart from DB design questions you could use in PHP after fetching the row to $result:
count(array_unique(explode(",",$result["follow_user_id")));
This is better and faster ;)
count(array_flip(explode(",", $result["follow_user_id")));
Or doing it in SQL:-
SELECT COUNT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(follow_user_id, ',', units.i + tens.i * 10), ',', -1) AS col1)
FROM sometable
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)units
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)tens
WHERE user_id = 1
(copes with up to 100 comma separated values).
But this would be so much easier with a properly normalised database design
$query="SELECT follow_user_id FROM follow WHERE user_id ='".$_POST['user_id']."' "; $query_run=mysql_query($query); $row= mysql_fetch_assoc($query_run);
$count= count(array_unique(explode(",",$row['follow_user_id'])));
$count;