How do I count the number of rows with certain data
I have a database with the following fields
id (primary id)
name (name of member)
city (cities where he has lived, comma separated values)
I have this sample database
id | Name | City
1 | Jyoti | Mumbai, Delhi
2 | Preeti | Delhi, Kolkata
3 | Nyasa | Mumbai, Boston, NewYork
4 |Nini | Delhi, Chennai
5 | Pinku | Pune, Mumbai
Now I wish to know how many members are from "Mumbai"
$result = mysql_query("SELECT * FROM members");
$row = mysql_fetch_array($result);
$city = $row['city'];
//now i explode the city to find values in array
$array = explode(",",$city);
Then see individual results. Then I used
This gives me the names
How Do I count the numbers of these, I mean how do I reach at a result of '3'. Anyone please help me.
N.B. I tried to use count() for the query that failed me. I do not know what other method is there since I am new to Php MySQL.
You're not using a good database design, it is bad practice to require significant data processing from the results of your database. SQL can easily handle this, here is a quick table structure for it:
SELECT COUNT((SELECT city_user_id FROM user_citiesWHERE city_name='Delhi' GROUP BY city_user_id)) AS cnt
Basically this query runs a count on a subquery. The subquery selects all the rows that are in that city, but eliminates any duplicate user IDs in case one listed multiple times.
I would advise you to become more experienced with SQL as a separate subject from PHP. Knowing good database practice will do you good when working with data driven applications. Advanced SQL is also pretty cool, data manipulation is fun.