deleting rows with same value in a single column

[Smudly]

I have a mysql table that looks like this:

 

id int(11) autoincrement

artist varchar(50)

title varchar(50)

 

I need to check for any rows that have the same titles.

So for example, if I had the following rows:

 

1 - Bob - Hello World

2 - Charlie Brown - Theme Song

3 - Joe - Hello World

4 - Joe - Is Cool

5 - Bob - Magic Dude

 

The query would display Row 1, and Row 3 as duplicates. Any idea how I can do something like this?

 

[Hypnos]

Depends on how you want to display the duplicates. The easiest way would be a COUNT() query to show the amount of rows that share a title.

 

http://www.tizag.com/mysqlTutorial/mysqlcount.php

[Hypnos]

I just noticed that the thread title says "deleting rows with same value in a single column".

 

What you would want to loop through the rows, and build up an array of titles, each time you step through an array checking if the title is in your array. If it is, run a delete query.

 

Little bit of sample code:

$result = mysql_query("SELECT * FROM songs");
$titlecache = array();
while($row = mysql_fetch_assoc($result)) {
    if(in_array($row['title'], $titlecache)) {
        //Add code to delete the row of $row['id']
    }
    $titlecache[] = $row['title']; //Add the title of this row to our array.
    
}

 

EDIT: Also you would probably want to do a strtoupper() on both in_array arguments to make it case insensitive.

[Smudly]

Alright, I tried this:

 

$result = mysql_query("SELECT title, COUNT(title) AS n FROM sheets GROUP BY title HAVING n > 1"); 

 

I have a while loop later on in my code that looks like this:

 

while($row = mysql_fetch_array($result))[

}

This is a page that I've been using for weeks and the while loop works great. There must be a problem with this query.

 

I am not getting any errors, nor anything displaying to the screen.

 

[Hypnos]

Your query looks right.

[fortnox007]

$result = mysql_query("SELECT title, COUNT(title) AS n FROM sheets GROUP BY title HAVING n > 1");

 

what happends if you remove the comma after title?

if that doesnt work is this maybe something?

$result = mysql_query("SELECT COUNT(title) AS n FROM sheets GROUP BY title HAVING n >1");

[Pikachu2000]

I wrote this function a while back to check for and list duplicates in a db field. You can use it, or modify it to suit your needs if you'd like. Maybe you could embed the output in a form and use it to trigger a DELETE query . . .

 

<?php
function mysqli_check_duplicates($db_conn, $table, $field, $flag = 1) {
$field = $field;
$table = $table;
require_once("$db_conn");
$output = '';
$query = "SELECT `{$field}`, COUNT(`{$field}`) AS c FROM `{$table}` GROUP BY `{$field}` ORDER BY c DESC";
$result = mysqli_query( $dbc, $query );
$output = "<table>";

while( $array = mysqli_fetch_assoc($result) ) {
if( $array['c'] > $flag ) {
$bg != "CCCCCC" ? $bg = "CCCCCC" : $bg = "FFFFFF";
$count = $array['c'];
$output .=  "<tr bgcolor=\"$bg\"><td>Value:</td><td>{$array[$field]}</td><td>Entries:</td><td>{$count}</td></tr>";
}
}
$output .= "</table>";
return $output;
}

/*  USAGE:
mysqli_check_duplicates('db_conn_with_path', 'table_name', 'field_name', $flag); // 0 = show all rows, 1 = (default) only show duplicates
*/
?>