View Single Post
Old 09-27-2010, 02:15 AM   #2 (permalink)
Village Idiot
Wizard
Top Contributor 
 
Village Idiot's Avatar
 
Join Date: Sep 2007
Posts: 1,299
Thanks: 17
Village Idiot is on a distinguished road
Default

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:

Users
user_id | user_name
1 | Jyoti
2 | Preeti
3 | Nyasa
4 |Nini
5 | Pinku

user_cities
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
Code:
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.
__________________

Village Idiot is offline  
Reply With Quote