Counting results in table

[MitchEvans]

Hi,

I have a table that looks like this:

Id Field
1 ab
2 ab
3 dt
4 df
5 cv
6 cv
7 cv
8 bn
9 bn
10 wr

I want to be able to count how many ab's I have, how many cv's I have etc.
I will not know what "field" will be so counting where field = ab will not work for me, maybe where field = same or something.

I would like to be a able to echo the results like so:

ab has 2 results
dt has 1 result
df has 1 result
cv has 3 results
bn has 2 results
wr has 1 result

Hope you can help guys!

[darkstrider]

If I had to do this, I would have 2 arrays, one for the names and one for the counts. Cycle through the records and for each record cycle through the name array. If found, increment the equivalent counter. If not, add to the names list and start the counter. At the end, just cycle through the arrays printing the results.
There are probably better ways but this has worked for me. Cheers.

[MitchEvans]

I have this so far so how could I get this working?

The values I want to count are $postcode1

$directory = "SELECT DISTINCT postcode FROM searchpost ORDER BY postcode asc";
$directoryResults = mysql_query($directory);
while ($directoryRow = mysql_fetch_array ($directoryResults)){

$postcode = strtoupper($directoryRow["postcode"]);
$postcode1=substr("$postcode",0,2);

$count++;
echo"$postcode1<br>";
$array=array($postcode1);

}

The values of $postcode1 look like so:

AB
AB
AB
AB
AB
AL
AL
AL
AL
AL
AO
AU

etc etc etc.

[darkstrider]

I'd probably do something like this :

$pc = array();
$pcname = array();
$pccount = array();
for ($a = 0; $a <= 50; $a++) {
  $pcname[$a] = "";
  $pccount[$a] = 0;
}
$count = 0;
$directory = "SELECT DISTINCT postcode FROM searchpost ORDER BY postcode asc"; 
$directoryResults = mysql_query($directory); 
while ($directoryRow = mysql_fetch_array ($directoryResults)){ 
    // Read results and put in array (pc)
    $postcode = strtoupper($directoryRow["postcode"]); 
    $pc[$count] = substr("$postcode",0,2); 
    $count++; 
} 
$count = count($pc);
$x = 0;
// Read through array
for ($i = 0; $i < $count; $i++) {
  // Find name in name array
  $found = 0;
  for ($j = 0; $j <= count($pcname); $j++) {
    if (isset($pc[$i]) && $pc[$i] == $pcname[$j]) {
      $pccount[$j] = $pccount[$j] + 1;
		  $found = 1;
		  $j = count($pcname) + 10;
		}
	}
  if ($found == 0) {
    $pcname[$x] = $pc[$i];
    $pccount[$x] = $pccount[$x] + 1;
    $x++;
	}
}
// Print results
for ($k = 0; $k <= $x; $k++) {  

  if ($pccount[$k] > 0 )  

  echo "Postcode $pcname[$k] = $pccount[$k]<br>";
}

[MitchEvans]

That's great, that seems to be working.

Do you have any idea how to order those results? So the one with the mosty is at the top?

Thanks

[darkstrider]

Just substitute this for the last bit :

for ($k = 0; $k <= $x; $k++) {  

  if ($pccount[$k] > 0 )  

  $post[$k] =  $pcname[$k] . " = " . $pccount[$k];
}
sort($post);
for ($m = 0; $m < count($post); $m++) {  

   echo "Postcode : " . $post[$m] . "<br>";
}

Hope that helps. Cheers.

[MitchEvans]

Hi,

Sorry for late reply that doesn't seem to put them in order, unless its ordering by postcode?!?!

Any ideas how to order them?

[theslinky]

Why don't you just use SQL to get the count?

SELECT field, COUNT(*) AS count FROM table GROUP BY field ORDER BY field ASC

that should return something like this:

field | count

ab | 2
bn | 2
cv | 3
...

-sridhar

[Weedpacket]

Agreed; use the database if you're using a database. But if you have to do it in PHP there is a built-in function for counting array values.

[MitchEvans]

The reason I was using PHP was because I only need to group by the first two letters of each postcode and count these.

The below doesn't work but should hopefully make sense as to what I want to do!

$directory = "SELECT DISTINCT substr(postcode0,2) AS postie, COUNT(*) AS count FROM searchpost GROUP BY postie ORDER BY count desc";

I need the first two letters of unique postcodes in the database and then to count them.

So FG1 4RT appeared twice it would be counted as once, but if FG4 5GH appeared that would be a seperate counted value as they both begin with FG but are different.

[Weedpacket]

MySQL has a substring function....

[MitchEvans]

So how do I get this to work then, it isn't working as it is?

$directory = "SELECT DISTINCT substring(postcode,0,2) AS postie, COUNT(*) AS count FROM searchpost GROUP BY postie ORDER BY count desc"; 

My database is like this:

postcode
dg1 4gh
dg4 5th
wa1 5dj
wa2 4dj
rt1 5gh
wa3 8th

I want to echo the results for the above like so:

dg - 2
wa - 3
rt - 1

Hope you can advise/help.

Thanks.

[BlueKai]

What about using a search in the query itself?

select whatever from atable where columnname like '%dg%' or anothercolumn like '%dg%'

[Weedpacket]

MitchEvans wrote:

Hope you can advise/help.

It's because you're still grouping by the entire postie, not the substring.

[MitchEvans]

Thanks weedpacket that's what i thought it was:

I've tried this:

$directory = "SELECT DISTINCT postcode, COUNT(*) AS count FROM searchpost GROUP BY substring(postcode,0,2) ORDER BY count desc"; 
$directoryResults = mysql_query($directory); 
while ($directoryRow = mysql_fetch_array ($directoryResults)){ 

$postcode = strtoupper($directoryRow["postcode"]); 
$counts = $directoryRow["count"];
echo "$postcode,$counts<br>";


} 

Unfortunately it counts all the postcodes in the table and only echo's one postcode.

[BlueKai]

ah.. substring starts at 1 not 0. also, don't need the distinct if you're already grouping by postcode.

$directory = "select postcode, count(*) as count
	from searchpost group by substring(postcode,1,3)
	order by count desc";

[Weedpacket]

You probably also want to substring() the column you're retrieving, otherwise (in MySQL) you'll end up with random postcodes that just happen to all have different prefixes. Other (more sensible) dbms's will probably choke on your having a nonaggregated column in an aggregated query.

select substring(postcode,1,3) as pcode, count(*) as count
	from searchpost group by pcode
	order by count desc

[jib0]

The following query should work:

SELECT SUBSTRING(postcode, 1, 2) AS postie, COUNT(*) AS count FROM (SELECT DISTINCT postcode FROM searchpost) AS t GROUP BY postie ORDER BY count DESC;