Sku mod question...

Submitted by daem0n on Sat, 2009-12-05 08:44 in Price Tapestry

Hi David,

Hopefully quick question for you. The typical sku mod (node 775), you say to change the code in products.php to:

    $sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' LIMIT 1";

    database_querySelect($sql,$rows);

    if ($rows[0]["sku"])

    {

      $sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($rows[0]["name"])."' OR sku = '".database_safe($rows[0]["sku"])."' ORDER BY price LIMIT ".$config_resultsPerPage;

    }

    else

    {

      $sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($rows[0]["name"])."' ORDER BY price LIMIT ".$config_resultsPerPage;

    }

    $numRows = database_querySelect($sql,$rows);

My question is - from my logic, this query pulls ONE record from the database to base it's decisions on...is that correct? So, if that first returned record has a sku it will match to the others with a sku OR if the name matches, it will also group those products. But what if that first record returned doesn't have a sku, only one of the other records that it matches by name has a sku - will it still try to match that sku to OTHER products?

My question is because I have some products where if I do the query (as I think it should be composed) in a MySQL query browser, I return a couple more matched products than through my website.

The query that I use is:

SELECT *, MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS matches FROM `products` WHERE name = ('product name') OR (sku = ('012345') AND sku <>(''))

GROUP BY name && sku

What do you think? Let me know as my brain is above it's capacity level haha

Thanks! -Joe

Hi Joe, You're absolutely

Submitted by support on Sat, 2009-12-05 09:25

Hi Joe,

You're absolutely right - in fact in a later version of this mod, I have suggested replacing the following code:

    $sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY price LIMIT ".$config_resultsPerPage;

    $numRows = database_querySelect($sql,$rows);

...with:

    $sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY price LIMIT ".$config_resultsPerPage;

    $numRows = database_querySelect($sql,$rows);

    $sku = "";

    if ($numRows)

    {

      foreach($rows as $row)

      {

        if ($row["sku"])

        {

          $sku = $row["sku"];

          break;

        }

      }

    }

    if ($sku)

    {

      $sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' OR sku='".database_safe($sku)."' ORDER BY price LIMIT ".$config_resultsPerPage;

      $numRows = database_querySelect($sql,$rows);

    }

Cheers,
David.