Duplicates in database table

[ohioman]

Hello... I have quite an interesting problem here and hope someone can help me finally put this project to bed.    I am putting code together to find exact duplicate records in a table with no primary key.  I would like you to consider the following code:

 

function dup_prodtocat() {

 

echo "<table cellpadding=0 border=1 valign=top>";

echo "<tr>";

      echo  "<td width='100%'><table border='1' width='100%' cellspacing='0' cellpadding='0'>";

        echo "<tr>";

          echo "<td class='pageHeading' width='100%'>"; echo HEADING_ACTION_PRODUCTS_TO_CATEGORIES; "</td>";

          echo "</tr>";

          echo "</table>";

 

echo "<table cellpadding='0' border='1' valign='top'>";

 

echo "<tr>";

 

$prodtocat_count_query = "select pc.products_id, pc.categories_id, pd.products_id, pd.products_name, c.categories_id, c.parent_id, cd.categories_id, cd.categories_name, COUNT(*) AS NumberOccurances from testing_products_to_categories pc, testing_products_description pd, testing_categories c, testing_categories_description cd WHERE pc.products_id = pd.products_id AND pc.categories_id = c.categories_id AND pc.categories_id = cd.categories_id GROUP BY pc.products_id, pc.categories_id HAVING ( COUNT(*) > 1 )";

$prodtocat_count_info = mysql_query($prodtocat_count_query) or die (mysql_error());

 

$num = mysql_num_rows($prodtocat_count_info) +1;

if ($num < 2) {

echo "<td width='100%' align='center'>"; echo PRODUCTS_TO_CATEGORIES; "</td>";

}else{

//  We have matches!  We list them all.

 

//  Begin Column Headings.

echo "<tr>";

echo "<td align='center' class='main'>", "Product ID", "</td>";

echo "<td align='center' class='main'>", "Category ID", "</td>";

echo "<td align='center' class='main'>", "Product Name", "</td>";

echo "<td align='center' class='main'>", "Category Name", "</td>";

echo "<td align='center' class='main'>", "Delete", "</td>";

echo "</tr>";

//  End column headings.

for ($i=1; $i < $num; $i++) {

$row = mysql_fetch_array($prodtocat_count_info);

$products_id = $row['products_id'];

$categories_id = $row['categories_id'];

$cPath = $row['parent_id'];

$products_name = $row['products_name'];

$categories_name = $row['categories_name'];

echo "<tr>";

echo "<td class='smallText'>", "<a href='/categories.php?action=new_product&pID=$products_id' target='blank'>", $row['products_id'], "</a>","</td>";

echo "<td class='smallText'>", "<a href='/categories.php?cPath=$cPath&cID=$categories_id' target='blank'>", $row['categories_id'], "</a>","</td>";

echo "<td class='smallText'>", $row['products_name'], "</td>";

echo "<td class='smallText'>", $row['categories_name'], "</td>";

echo '<td><a href="database_check.php?action=delete_prodtocat&ref=7&products_id='.$products_id.'&categories_id='.$categories_id.'" onclick="return confirmDelete();"><img src="includes/languages/english/images/buttons/button_delete.gif" border="0" alt="Click to delete category"></a></td>';

echo "</tr>";

}

}

 

The query up top does not display anything.  Interestingly enough, I have this shorter one that displays only half of what I want:

 

$prodtocat_count_query = "select products_id, categories_id, COUNT(*) AS NumberOccurances from testing_products_to_categories GROUP BY products_id, categories_id HAVING ( COUNT(*) > 1 )";

 

This one brings up exact duplicates and with the rest of the code displays the products_id number and categories_id number... row after row if it exists in the database.  That is exactly how I want it to do.. but..  Now comes the part I'm really lost with.  How in the world do I incorporate the cPath(parent_id), products_name and categories_name into either the same query without messing up the results.. or a totally separate query perhaps?  Where products_id = '" . $products_id . "' and such?  I tried something like that but couldn't tie the results from that and keep it straight with the first query.  If I need a second query.. how do I pull the information out such that I can display it as I have the first?

 

Any advice you have would be appreciated.

 

Thanks,

 

Iceman