[SOLVED] Counting how many dupliate records

[CoreyR]

Ok, I have a field name "cname" and there are multiple records some are the same, some are not.

 

Example:

1. ebay

2. google

3. ebay

4. yahoo

5. ebay

 

How would I go about printing a list like this?

Ebay = 3

Google = 1

Yahoo =1

 

Iv'e tried the foreach function, but for some reason it is not valid. I know you can only use a foreach with an array, but I'm just learning arrays.

 

Thanks

[Lumio]

SELECT COUNT(`cname`) FROM `table` WHERE `cname` = 'ebay';

[CoreyR]

Sorry, maybe I should have stated, what if I do not know whats in the field?

[Kasuke_Akira]

$result = mysql_db_query(SELECT * FROM table ORDER BY field_name);
$temp = "";

while ($data = mysql_fetch_array($result)) {
  if ($temp != $data[field_name]) {
     $temp = $data[field_name];
     $num_rows = mysql_num_rows(SELECT * FROM table WHERE field_name = '$temp');
     echo "$temp = $num_rows<br />";
  }
}

 

That should work..if not tell me errors and I'll figure it out. I'm at work and have no way to look at my scripts to see if it's right.

[Hell Toupee]

$result = mysql_db_query(SELECT * FROM table ORDER BY field_name);
$temp = "";

while ($data = mysql_fetch_array($result)) {
   if ($temp != $data[field_name]) {
      $temp = $data[field_name];
      $num_rows = mysql_num_rows(SELECT * FROM table WHERE field_name = '$temp');
      echo "$temp = $num_rows<br />";
   }
}

 

That should work..if not tell me errors and I'll figure it out.  I'm at work and have no way to look at my scripts to see if it's right.

 

Besides syntax errors, that wouldn't work if the arrays weren't ordered by field_name, as the same field_name would still be displayed if it came more than 1 row after the last one of the same name.

 

I'd add all the records to an array as they come, checking each time to see if the item already exists in the array to avoid duplication.

 

This should work:

 

// DECLARE THE ARRAY
$field_array=array();

// SELECT ALL THE FIELDS FROM THE DATABASE
$result=mysql_query("SELECT field FROM table") or die(mysql_error());

// ITERATE THROUGH THE RESULTS OF THE ABOVE  QUERY
while ($row=mysql_fetch_array($result,MYSQL_BOTH)){

// ADDS EACH FIELD TO TO AN ARRAY ONLY ONCE
if (!in_array($row[field],$field_array)){
$field_array[]=$row[field];

// FINDS OUT HOW MANY RESULTS FOR EACH FIELD THEY ARE
$num_fields=mysql_num_rows(mysql_query("SELECT * FROM tableWHERE field LIKE '$row[field]'"));
echo("$row[field] - $num_fields </br>");
}
}

[CoreyR]

Damn, thanks guys. I learn a bunch also. Thank you very much for the help!!!!!!!!!

[Kasuke_Akira]

if you order by fieldname..it rearranges the table by field name, grouping all the same together before it is processed. then it would cycle through each enrty, it only grabs the num rows in the DB WHERE $field_name = $temp, after it decides if that $temp != $data[fieldname]. when it does come across one that doesnt equal it, it assigns $temp the new value, searches the table for all the rows with field_name = $temp, counts them up, displays and continues to run through each row, till it finds a nother that isnt equal

 

it should work cuz i use the same concept to group names based on a certain type in another website i have. if you order by, it orders the whole table in that order grouping ebay, yahoo, etc together...

 

Besides, I don't see syntax errors. and it IS ordered by field_name (you jsut replace 'field_name' with the appropriate fieldname.............the same with any instance of 'table'

 

the only reason it wouldnt work is if i typed something wrong. but the concept is right as long as everything is typed right.

[Hell Toupee]

Ah right I didn't see the order by in the first query, my mistake oh and the syntax errors were the bad typing yeah, both methods should work fine.

 

Also for speed's sake, in either method use "GROUP BY field", instead of "ORDER BY field" asc as it limits the amount of results to the query to how many unique fields there are.

[Kasuke_Akira]

Ahh..ok..didnt know about GROUP BY..i need to read a mySQL book...lol

[CoreyR]

Woked perfect!