CODEWITHSUNDEEP
mysqlsql
Busilinks
Busilinks

Reputation: 87

Eliminate Duplicate Date MySQL

I am creating a products database and trying to do it the right way. Here is how I have my database setup so far.

My product tables is just a productid and marketing description. The sku table relates a skuid to productid. One product has many skus. Skus are unique to each product. The three attributes tables associate an attribute value (i.e. size, weight, color, etc.) to a sku id.

Here is the result.

http://174.121.67.116/~aware/awarebk/product_sku_view.php?code=789

Product 789 has skus 17, 24, 25, 29, 30 related to it. Row 25, 29, and 30 is my problem. You see that duplicates are allowed. This table doesn't exist in the database. It's created using the code below. I want the combination of attributes to be unique for each sku id. Do I do this through front-end validation? Is there a way to do this on the backend? Here is mysql code I use to generate the sku rows.

$sidresult = mysql_query("SELECT sku.product_idproduct,
                                 sku.idsku, 
                                 amaterial.material, 
                                 amcolor.color, 
                                 atype.type
                            FROM sku sku
                 LEFT OUTER JOIN amaterial ON sku.idsku = amaterial.sku_idsku
                 LEFT OUTER JOIN amcolor ON sku.idsku = amcolor.sku_idsku
                 LEFT OUTER JOIN atype ON sku.idsku = atype.sku_idsku
                           WHERE sku.product_idproduct =$pid");

  if (mysql_num_rows($sidresult) > 0) {
      echo '
  <table border="1" cellspacing="0" cellpadding="5px">
    <tr>  
     <th>SKU ID</th>
    <th>Material</th>
    <th>Material Color</th>
    <th>Type</th>

    </tr>';
      while ($sidrow = mysql_fetch_array($sidresult)) {
          echo '<tr>';
          echo '<td>' . $sidrow['idsku'] . '</td>';
          echo '<td>' . $sidrow['material'] . '</td>';
          echo '<td>' . $sidrow['color'] . '</td>';
          echo '<td>' . $sidrow['type'] . '</td>';
          echo "</tr>";
      }
      // close table>
      echo "</table>";

Again, my goal is to make sure that the row of attributes generated using the skuid is unique and that duplicates can't be added. So how do I validate against duplicates across multiple tables?

Upvotes: 2

Views: 152

Answers (1)

Datajam
Datajam

Reputation: 4231

Your back-end validation code should check the new SKU values entered by the user against the product ID - it looks like you're using PHP, so this routine should sit where you process the SKU input form (add_product_sku_view.php).

Take the user's input, run an SQL query to see if it returns any existing SKUs, and notify the user if the combination already exists. Only once they have entered unique SKU details should you allow the record to be inserted into your SKU table.

Upvotes: 1

Related Questions