counting number of same strings in array

[LuckySyringe]

I'm trying to count how many times the same name shows up in a database with different tables for different times (the tables are still formatted the same way).
IE:

Array
(
    [0] => John Doe
    [1] => Jane Doe
    [2] => John Doe
    [3] => Bob Smith
    [4] => Bob Smith
    [5] => Jane Doe
    [6] => Bob Smith
    [7] => John Doe
    [8] => Bob Smith
    [9] => John Doe
    [10] => Bob Smith
)

John Doe shows up 4 times in the database, Jane Doe shows up 2 times, and Bob Smith shows up 5, so the output would likely be

$total = array("John Doe" => "4", "Jane Doe" => "2", "Bob Smith" => "5")

I've already searched google and tried some functions but to no avail. Below is what I have so far.

Code:

<?php
//MySQL Connection Settings
mysql_connect ($mysql_host, $mysql_user, $mysql_pass) or die (mysql_error());
mysql_select_db ($mysql_db) or die (mysql_error());

$query = "SHOW TABLES FROM ".$mysql_db;
$result = mysql_query($query) or die (mysql_error());
while ($row = mysql_fetch_row($result)) {
    $tableName = $row[0];
	print "Table Name: ".$tableName."\n";
    $query_2 = "SELECT * FROM ".$tableName." ORDER BY `name`";
    $result_2 = mysql_query($query_2) or die (mysql_error());
	$query_3 = "SELECT name FROM ".$tableName;
	$result_3 = mysql_query($query_3);
	$result_4 = mysql_num_rows($result_3);
	print "-- Count from ".$tableName.": ".$result_4."\n";
	$total_count = $total_count + $result_4;
    while ($row_2 = mysql_fetch_assoc($result_2)){
            $val['tableName'] = $tableName;
            $array[uniqid(rand(), true)] = $row_2;
    }
}
print "Total Count: ".$total_count."\n\n";

$i = count($array) > $total_count ? $total_count : count($array);
$cnt = 0;
foreach ($array as $key=>$val){
	if ($cnt < $i ) {
		$name[$key] = stripslashes($val['name']);
		$cnt++;}
	else{
		exit;}
}
sort($name);
print_r(array_values($name));

@mysql_free_result($result) or die (mysql_error());
@mysql_free_result($result_2) or die (mysql_error());
@mysql_free_result($result_3) or die (mysql_error());
@mysql_free_result($result_4) or die (mysql_error());
@mysql_close() or die (mysql_error());
?>

-> LuckySyringe

 

[sleipnir214]

It seems to me that you're not letting MySQL do enough of the work.

Given a table "foo" containing:

[tt]+------+--------------+
| pkID | name |
+------+--------------+
| 1 | John Doe |
| 2 | Jane Doe |
| 3 | John Doe |
| 4 | Bob Smith |
| 5 | Bob Smith |
| 6 | Jane Doe |
| 7 | Bob Smith |
| 8 | John Doe |
| 9 | Bob Smith |
| 10 | John Doe |
| 11 | Bob Smith |
| 12 | Betty Frieze |
+------+--------------+[/tt]

Then the query:

SELECT
name, COUNT(*) AS the_count
FROM
foo
GROUP BY
name
HAVING
the_count > 1
ORDER BY
name

produces:

[tt]+-----------+-----------+
| name | the_count |
+-----------+-----------+
| Bob Smith | 5 |
| Jane Doe | 2 |
| John Doe | 4 |
+-----------+-----------+[/tt]

Which shows a count of record broken down by name. If you leave out the "HAVING" clause, you'll also get all the records that appear only once.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[LuckySyringe]

I edited the code, but now it returns:

Table Name: 2003
--Count from 2003: Resource id #5
Table Name: 2004
--Count from 2004: Resource id #7
Table Name: 2005
--Count from 2005: Resource id #9
Table Name: 2006
--Count from 2006: Resource id #11
Total Count: 32

...when before it returned:

Table Name: 2003
-- Count from 2003: 21
Table Name: 2004
-- Count from 2004: 72
Table Name: 2005
-- Count from 2005: 100
Table Name: 2006
-- Count from 2006: 74
Total Count: 267

...did I do something wrong? updated PHP code:

//MySQL Connection Settings
$query = "SHOW TABLES FROM ".$mysql_db;
$result = mysql_query($query) or die (mysql_error());
while ($row = mysql_fetch_row($result)) {
    $tableName = $row[0];
	print "Table Name: ".$tableName."\n";
    $query_2 = "SELECT * FROM ".$tableName." ORDER BY `name`";
    $result_2 = mysql_query($query_2) or die (mysql_error());
	$query_3 = "SELECT name, COUNT(*) AS the_count FROM ".$tableName." GROUP BY name ORDER BY name";
	$result_3 = mysql_query($query_3);
	print "-- Count from ".$tableName.": ".$result_3."\n";
	$total_count = $total_count + $result_3;
    while ($row_2 = mysql_fetch_assoc($result_2)){
            $val['tableName'] = $tableName;
            $array[uniqid(rand(), true)] = $row_2;
    }
}
print "Total Count: ".$total_count."\n\n";

$i = count($array) > $total_count ? $total_count : count($array);
$cnt = 0;
foreach ($array as $key=>$val){
	if ($cnt < $i ) {
		$name[$key] = stripslashes($val['name']);
		$cnt++;}
	else{
		exit;}
}
sort($name);
print_r(array_values($name));
//MySQL Disconnect

-> LuckySyringe

 

[sleipnir214]

You're trying to print a database resultset handle, rather than fetching data through it using a mysql_fetch_*() function.



Want the best answers? Ask the best questions! TANSTAAFL!