Count from 2 fields

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
nebbe
Forum Newbie
Posts: 3
Joined: Sun Oct 17, 2010 10:18 am

Count from 2 fields

Post by nebbe »

Hi from an beginner, Hope you can help me.

I have a database with a table with name " Owner"

From owner i pic from 2 fields " mc " and " model ".

When I count from "mc" and the works perfect with this code

Question 1

Code: Select all

<?php 
 ## database connection!
 include ('mysqlcondb.php');  
// Make a MySQL Connection
$query = "SELECT *, COUNT(model) FROM owner GROUP BY model"; 
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo "There are ". $row['COUNT(model)'] ." ". $row['model'] ."";
echo "<br />";
}
?> 
Here I get :

There are 1 alabama
There are 34 Mexico
There are 65 New orleans
There are 34 wales
And so on.....

Question 2

Code: Select all

<?php 
## database connection!
 include ('mysqlcondb.php');  
// Make a MySQL Connection
$query = "SELECT *, COUNT(mc) FROM owner GROUP BY mc"; 
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo "There are ". $row['COUNT(mc)'] ." ". $row['mc'] ."";
echo "<br />";
}
?> 
Question 2 works also realy great.
Here I get :

There are 17 USA
There are 110 UK
There are 4 Canada
There are 3 France
And so on.......

Now I want to combine those to and get:

2 in Florida USA
3 in Califonia USA
7 in Wales UK
and so on....

Any who can help a beginner?

Nebbe
jankidudel
Forum Commoner
Posts: 91
Joined: Sat Oct 16, 2010 4:30 pm
Location: Lithuania, Vilnius

Re: Count from 2 fields

Post by jankidudel »

Please write more clearly, and if you want send me private message( because it's crazy job trying to guess what is in your mind), i'll help you.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Count from 2 fields

Post by Eran »

Please keep the discussion on the forums so everyone can benefit.
nebbe
Forum Newbie
Posts: 3
Joined: Sun Oct 17, 2010 10:18 am

Re: Count from 2 fields

Post by nebbe »

Hi . Sorry but English is not my first Language.

In a database I have lots of members from all ower the world.

In table " owner" I have 2 fields with country ( "mc" ) and states ("models"). I know bad naming :oops:

I want to count how many members there are in:

different states in every country.

Like:

2 in Califonia, USA
3 in Florida, USA
......
and so on.

Nebbe
jankidudel
Forum Commoner
Posts: 91
Joined: Sat Oct 16, 2010 4:30 pm
Location: Lithuania, Vilnius

Re: Count from 2 fields

Post by jankidudel »

create table owner (
id int not null auto_increment primary key,
country char(30) not null,
states char(30) not null
);

query = "SELECT COUNT(states), states, country FROM owner GROUP by states;

Results from my experimentary table :

_______________________________________
count(states) | states | country
------------------------------------------------------
1 | Cansas | USA
2 | Chicago | USA
4 | Florida | USA
5 | Liverpool | UK
3 | London | UK
1 | Mexico | USA
__________________________________________


<?php

$conn = mysql_connect('localhost', 'root', '');
mysql_select_db('your_db_name');

$res_arr = mysql_query("SELECT COUNT(states), states, country
FROM owner GROUP by states", $con);

while($result = mysql_fetch_assoc($res_arr)) {
echo $result['COUNT(states)'].' member[s] in '.$result['states'].' '.$result['country']."<br />";
}
?>



Is it what you want ?
nebbe
Forum Newbie
Posts: 3
Joined: Sun Oct 17, 2010 10:18 am

Re: Count from 2 fields

Post by nebbe »

Thanks . Looks just what I want. I will try that. Have a nice weekend.

Rickard
Post Reply