Explore our sites

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Possible Duplicate:
Find duplicate strings in database

im querying a mysql database with php.

I need to know what names are repeated over 375 in the table (it has 375 rows). The table contains 3 columns: id, name, category.

Here is my php code, however it counts with php, and i want it to be made with mysql

$query = "SELECT * FROM user_likes"; 
//echo $query;
$result = mysql_query($query) or die(mysql_error());

// craete arrays
while($row = mysql_fetch_array($result)){
    set_time_limit(0);
    $like[$row['name']]=$like[$row['name']]+1;
    if($like[$row['name']]==375) $likes375 ++; 

}

If someone could help me ill appreciate it. Thanks!

share|improve this question
 
Sorry if my answer isn't helpful -- I don't clearly understand what you are trying to do. –  logan Sep 30 '12 at 3:11
add comment

marked as duplicate by OMG Ponies, AVD, Jonathan Leffler, hjpotter92, pad Sep 30 '12 at 7:10

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3 Answers

up vote 1 down vote

Try this:

SELECT name, count(*) likes 
FROM user_likes 
GROUP BY name
HAVING COUNT(*) > 375
share|improve this answer
 
when using this appreach i receive: Lost connection to MySQL server during quer.... the db has over 20 million rows, so probably it takes too long and the connection is closed –  Dan Stern Sep 30 '12 at 4:31
 
Try making a view on mysql from that select statement and querying the view from the php code. –  Mt. Schneiders Sep 30 '12 at 12:40
add comment
up vote 0 down vote

Here's some SQL to count the number of likes per name.

SELECT name, count(*) likes FROM user_likes GROUP BY name;

Here is some SQL to filter for names that have more than 375 likes

SELECT i.name, i.likes
FROM ( SELECT name, count(*) likes FROM user_likes GROUP BY name) i
WHERE i.likes > 375
share|improve this answer
add comment
up vote 0 down vote

If you mean to find duplicated names:

$query = "SELECT name FROM user_likes GROUP BY name HAVING COUNT(*) = 375"; 

$result = mysql_query($query) or die(mysql_error());

$names = array();    
while($row = mysql_fetch_array($result)){
  $names[] = $row['name'];
}

print_r($names);
share|improve this answer
 
thats not actually what im trying to achieve... i want to display only the names that are repeated 375 times –  Dan Stern Sep 30 '12 at 4:32
 
@DanStern That's the same, check my updated answer, it is even easier. –  xdazz Sep 30 '12 at 4:35
 
@DanStern If you want 375 times and more, then change = to >= –  xdazz Sep 30 '12 at 4:36
 
thats true! howver im having another problem related to the same. when using this appreach i receive: Lost connection to MySQL server during query.... the db has over 20 million rows, so probably it takes too long and the connection is closed –  Dan Stern Sep 30 '12 at 4:43
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.