Incorrect advanced search product listing (v. 2.9x - 2.95)

When searching for items in the Advanced Item Search in the Products screen, if something is selected in the Options field, the Edit, Options, Inventory and Delete buttons do not point to the correct item. This is happening because the ID field is pulling the option ID, not the item ID.

To correct this in version 2.95 files:

  1. Open the itemlist.php file in the /storeadmin/ folder.

  2. Change the following code (around line ):

    $itemquery = "SELECT *, MAX(OptionNum) AS MaxOptionNum FROM " .SB_DB_PREFIX ."_items LEFT JOIN " .SB_DB_PREFIX ."_options ON " .SB_DB_PREFIX ."_items.ID=" .SB_DB_PREFIX ."_options.ItemID";

    to:

    $itemquery = "SELECT i.*, o.ItemID, o.OptionNum, o.Name, o.Type, o.Attributes, MAX(OptionNum) AS MaxOptionNum FROM " .SB_DB_PREFIX ."_items i LEFT JOIN " .SB_DB_PREFIX ."_options o ON i.ID=o.ItemID";
  3. Change the following code (around line ):

    $itemquery = "SELECT * FROM " .SB_DB_PREFIX ."_items WHERE ID<>''";

    to:

    $itemquery = "SELECT * FROM " .SB_DB_PREFIX ."_items i WHERE ID<>''";
  4. Change the following code (around line ):

    $itemquery .= " GROUP BY " . SB_DB_PREFIX . "_items.ID";

    to:

    $itemquery .= " GROUP BY i.ID";
  5. Save and reload the file.