Page 1 of 1

Count from 2 fields

Posted: Sun Oct 17, 2010 11:20 am
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

Re: Count from 2 fields

Posted: Sun Oct 17, 2010 1:32 pm
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.

Re: Count from 2 fields

Posted: Sun Oct 17, 2010 1:39 pm
by Eran
Please keep the discussion on the forums so everyone can benefit.

Re: Count from 2 fields

Posted: Mon Oct 18, 2010 4:02 am
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

Re: Count from 2 fields

Posted: Wed Oct 20, 2010 12:27 pm
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 ?

Re: Count from 2 fields

Posted: Fri Oct 29, 2010 12:12 pm
by nebbe
Thanks . Looks just what I want. I will try that. Have a nice weekend.

Rickard