I am finding exact duplicates from a table with no primary key and contains 2 fields. The code I have for this is:
$prodtocat_count_query = "select pc.products_id, pc.categories_id, COUNT() AS NumberOccurances from testing_products_to_categories pc GROUP BY pc.products_id, pc.categories_id HAVING ( COUNT() > 1 )";
$prodtocat_count_info = mysql_query($prodtocat_count_query) or die (mysql_error());
$row = mysql_fetch_array($prodtocat_count_info);
$products_id = $row['products_id'];
$categories_id = $row['categories_id'];
which... works great. Later on, this allows me to echo the values of the product_id and categories_id of the duplicate records.
Here is where the problem is though: I also want to get other information to these records such as the product name and category name. When I attempt to access multiple tables by adding to that query, it messes up the results to not be true. I have resigned to the fact that I will need another query for that.. maybe such as:
$second_query = 'SELECT pd.products_id, pd.products_name, c.categories_id, c.parent_id, cd.categories_id, cd.categories_name FROM products_description pd, categories c, categories_description cd WHERE pd.products_id = '" . $row['products_id'] . "' AND c.categories_id = '" . $row['categories_id] . "' AND cd.categories_id = '" . $row['categories_id'] . "' GROUP BY pd.products_id";
What I would like to know is 2 things...
1) How would I put these in a loop such that if there were multiple duplicate entries in the database.... these queries would run together to get all of the information for each record.
2) How would be the best way to extract these to variables or something that I can deal with later... to perhaps do this:
echo $product_id
or how ever else I would like to manipulate each record. The way I have it now works.. but.. is there a better way?
Any ideas? Your help would be appreciated!
Iceman 🙂
