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.
Please help me...
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:
user_id | user_name
1 | Jyoti
2 | Preeti
3 | Nyasa
5 | Pinku
city_id | city_name | city_user_id
1 | Mumbai | 1
2 |Delhi | 1
3 | Delhi | 2
4 | Kolkata | 2
5 | Mumbai | 3
6 | Boston | 3
7 | NewYork | 3
8 | Delhi | 4
9 | Chennai | 4
10 | Pune | 5
11 | Mumbai | 5
Now to find the users from Delhi
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.
|All times are GMT. The time now is 08:12 PM.|
Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0