Custom Coding -- Select statements not completing before page is rendered

[Allolex]

Hi,

I'm not certain whether this bug is in my modified code or in Zen Cart's somewhere, but I couldn't find a more appropriate forum than Bug Reports.

I'm running into some trouble with some modifications I made to Zen Cart 1.3.5 in order to get it to display artist names and release titles on the various product listings pages. Specifically, I made these changes to new_products.php and featured_products.php in the includes/modules/ directory. With the code I have written below, when you load the main index page for the web shop, you sometimes get the names and titles for the latest products, and sometimes you don't.

For featured products, the names and titles always display. When I look at the rendered XHTML code for the latest products, the variables are simply not there some of the time. I think this has to do with the display code not actually waiting for the artist name and release title lookups to return before executing the next bit of code, which goes ahead and renders the XHTML without any values. I also think that the sporadic nature of this problem is due to the likelihood that sometimes the lookup queries execute in time to be included in the rendering, and sometimes they return too late.

Does anyone have any thoughts as to where I should be poking around to fix this problem?

Thanks in advance.

Damon

I've modified the code in includes/modules/new_products.php as follows:

while (!$new_products->EOF) {

    $products_price = zen_get_products_display_price($new_products->fields['products_id']);
    $artists_name = zen_get_artists_name($new_products->fields['products_id']);
    $artists_id = zen_get_artists_id($new_products->fields['products_id']);

    $new_products->fields['products_name'] = zen_get_products_name($new_products->fields['products_id']);
    $list_box_contents[$row][$col] = array('params' => 'class="centerBoxContentsNew centeredContent back"' . ' ' . 'style="width:' . $col_width . '%;"',
    'text' => '<a href="' . zen_href_link(zen_get_info_page($new_products->fields['products_id']), 'products_id=' . $new_products->fields['products_id']) . '">' . zen_image(DIR_WS_IMAGES . $new_products->fields['products_image'], $new_products->fields['products_name'], IMAGE_PRODUCT_NEW_WIDTH, IMAGE_PRODUCT_NEW_HEIGHT) . '</a><br />'
    . '<a href="index.php?artists_id=' . $artists_id . '&typefilter=artists_id&main_page=index">' . '<div class="artistsName">' . $artists_name . '</div></a>' 
    . '<a href="' . zen_href_link(zen_get_info_page($new_products->fields['products_id']), 'products_id=' . $new_products->fields['products_id']) . '">' 
    . $new_products->fields['products_name'] . '</a><br />' . $products_price);

    $col ++;
    if ($col > (SHOW_PRODUCT_INFO_COLUMNS_NEW_PRODUCTS - 1)) {
      $col = 0;
      $row ++;
    } 

The initial variable assignments call some custom functions in includes/functions/functions_lookups.php:

function zen_get_artists_name($product_id) {
        global $db;

        $sql = "select p.artists_id from " . TABLE_PRODUCT_MUSIC_EXTRA . " p where products_id='" . $product_id . "'";
        $look_up = $db->Execute($sql);

        $sql2 = "select p.artists_name from " . TABLE_RECORD_ARTISTS . " p where artists_id='". $look_up->fields['artists_id'] . "'";
        $look_up2 = $db->Execute($sql2);

        return $look_up2->fields['artists_name'];
}

function zen_get_artists_id($product_id) {
        global $db;

        $sql = "select p.artists_id from " . TABLE_PRODUCT_MUSIC_EXTRA . " p where products_id='" . $product_id . "'";
        $look_up = $db->Execute($sql);

        return $look_up->fields['artists_id'];
} 

[DrByte]

There are no timing issues ... if the query doesn't finish, then the next line of code doesn't run.

Could it be that for some products there is no related data, and thus blanks are appearing?

Where can this be seen in-action?

[Allolex]

Thanks for the quick response.

Could it be that for some products there is no related data, and thus blanks are appearing?

No, I'm afraid not. It's either all of the items or none--not blanks. Unfortunately, I also cannot provide access to our test machine to you, but this has happened in an earlier version of 1.3.0.2 and now in 1.3.5. I was hoping it would just go away.

The code in both modules is identical, so I think it has to do with new_products.php generating a fresh list every time, while the problem has not occurred once with featured_products.php. And since both call the same functions (very simple ones at that!), the functions appear to be ruled out.

I thought this might be a problem with threading, or parallelisation of the code, but it looks like I'm just going to have to roll up my sleeves and add some debugging and error handling code. I won't rule out that it's my small amount of code that's causing the whole problem, but I really feel it's something else.

-d.

[DrByte]

I just added your code to modules/new_products.php on a fresh 1.3.6 install (and your functions) and it works fine, apart from the fact that to click on the artists link requires a new artists_filter to be defined, which I assume you've already done, etc.

[Allolex]

Thanks for testing this. I'm sending you the filter code as well. How does it run?

<?php
/**
 * artists_id_filter.php  for index filters
 *
 * index filter for recording artists
 *
 * @package productTypes
 * @copyright Copyright 2003-2005 Zen Cart Development Team
 * @copyright Portions Copyright 2003 osCommerce
 */
if (!defined('IS_ADMIN_FLAG')) {
  die('Illegal Access');
}
  if (!isset($select_column_list)) $select_column_list = "";
 // show the products of a specified record-company
  if (isset($_GET['artists_id']))
  {
    if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id']))
    {
      // We are asked to show only a specific category
      $listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, p.products_price, p.products_tax_class_id, pd.products_description, if(s.status = 1, s.specials_new_products_price, NULL) AS specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping
        from " . TABLE_PRODUCTS . " p, " .
        TABLE_PRODUCTS_DESCRIPTION . " pd, " .
        TABLE_PRODUCT_MUSIC_EXTRA . " pme left join " . TABLE_SPECIALS . " s on pme.products_id = s.products_id, " .
        TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " .
        TABLE_RECORD_ARTISTS . " m
        where m.artists_id = '" . (int)$_GET['artists_id'] . "'
          and p.products_id = pme.products_id
          and p.products_status = 1
          and pme.artists_id = '" . (int)$_GET['artists_id'] . "'
          and pme.products_id = p2c.products_id
          and pd.products_id = p2c.products_id
          and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
          and p2c.categories_id = '" . (int)$_GET['filter_id'] . "'";
    } else {
      // We show them all
      $listing_sql = "select " . $select_column_list . " pme.products_id, p.products_type, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping
        from " . TABLE_PRODUCTS . " p, " .
        TABLE_PRODUCTS_DESCRIPTION . " pd, " .
        TABLE_PRODUCT_MUSIC_EXTRA . " pme left join " . TABLE_SPECIALS . " s on pme.products_id = s.products_id, " .
        TABLE_RECORD_ARTISTS . " m
        where m.artists_id = '" . (int)$_GET['artists_id'] . "'
          and p.products_id = pme.products_id
          and p.products_status = 1
          and pd.products_id = pme.products_id
          and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
          and pme.artists_id = '" . (int)$_GET['artists_id'] . "'";
    }
  } else {
    // show the products in a given category
    if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id']))
    {
      // We are asked to show only specific category
      $listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, m.music_genre_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping
        from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " .
        TABLE_PRODUCTS_DESCRIPTION . " pd, " .
        TABLE_RECORD_ARTISTS . " m, " .
        TABLE_PRODUCTS_MUSIC_EXTRA . " pme, " .
        TABLE_PRODUCTS_TO_CATEGORIES . " p2c
        where p.products_status = 1
          and pme.artists_id = m.artists_id
          and m.artists_id = '" . (int)$_GET['filter_id'] . "'
          and p.products_id = p2c.products_id
          and pd.products_id = p2c.products_id
          and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
          and p2c.categories_id = '" . (int)$current_category_id . "'";
    } else {
      // We show them all
      if ($current_categories_id) {
        $listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, m.music_genre_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping
          from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " .
          TABLE_PRODUCTS . " p left join " . TABLE_RECORD_ARTISTS . " m, " . TABLE_PRODUCT_MUSIC_EXTRA . " pme on pme.artists_id = m.artists_id, " .
          TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id
          where p.products_status = 1
            and p.products_id = p2c.products_id
            and pd.products_id = p2c.products_id
            and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
            and p2c.categories_id = '" . (int)$current_category_id . "'";
      } else {
        $listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, m.music_genre_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping
          from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " .
          TABLE_PRODUCTS . " p left join " . TABLE_RECORD_ARTISTS . " m, " . TABLE_PRODUCT_MUSIC_EXTRA . " pme on pme.artists_id = m.artists_id, " .
          TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id
          where p.products_status = 1
            and p.products_id = p2c.products_id
            and pd.products_id = p2c.products_id
            and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'";
      }
    }
  }
  // set the default sort order setting from the Admin when not defined by customer
  if (!isset($_GET['sort']) and PRODUCT_LISTING_DEFAULT_SORT_ORDER != '') {
    $_GET['sort'] = PRODUCT_LISTING_DEFAULT_SORT_ORDER;
  }

  $listing_sql = str_replace('m.manufacturers_name', 'm.artists_name as manufacturers_name', $listing_sql);

  if (isset($column_list)) {
    if ( (!isset($_GET['sort'])) || (!ereg('[1-8][ad]', $_GET['sort'])) || (substr($_GET['sort'], 0, 1) > sizeof($column_list)) )
    {
      for ($i=0, $n=sizeof($column_list); $i<$n; $i++)
      {
        if ($column_list[$i] == 'PRODUCT_LIST_NAME')
        {
          $_GET['sort'] = $i+1 . 'a';
          $listing_sql .= " order by p.products_sort_order, pd.products_name";
          break;
        }
      }
      // if set to nothing use products_sort_order and PRODUCTS_LIST_NAME is off
      if (PRODUCT_LISTING_DEFAULT_SORT_ORDER == '') {
        $_GET['sort'] = '20a';
      }
    } else {
      $sort_col = substr($_GET['sort'], 0 , 1);
      $sort_order = substr($_GET['sort'], 1);
      $listing_sql .= ' order by ';
      switch ($column_list[$sort_col-1])
      {
        case 'PRODUCT_LIST_MODEL':
        $listing_sql .= "p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
        break;
        case 'PRODUCT_LIST_NAME':
        $listing_sql .= "pd.products_name " . ($sort_order == 'd' ? 'desc' : '');
        break;
        case 'PRODUCT_LIST_MANUFACTURER':
        $listing_sql .= "m.artists_name " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
        break;
        case 'PRODUCT_LIST_QUANTITY':
        $listing_sql .= "p.products_quantity " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
        break;
        case 'PRODUCT_LIST_IMAGE':
        $listing_sql .= "pd.products_name";
        break;
        case 'PRODUCT_LIST_WEIGHT':
        $listing_sql .= "p.products_weight " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
        break;
        case 'PRODUCT_LIST_PRICE':
        //          $listing_sql .= "final_price " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
        $listing_sql .= "p.products_price_sorter " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
        break;
      }
    }
  }
  // optional Product List Filter
  if (PRODUCT_LIST_FILTER > 0)
  {
    if (isset($_GET['artists_id']))
    {
      $filterlist_sql = "select distinct c.categories_id as id, cd.categories_name as name
        from " . TABLE_PRODUCTS . " p, " .
        TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " .
        TABLE_CATEGORIES . " c, " .
        TABLE_CATEGORIES_DESCRIPTION . " cd, " .
        TABLE_PRODUCT_MUSIC_EXTRA . " pme
        where p.products_status = 1
          and pme.products_id = p2c.products_id
          and p2c.categories_id = c.categories_id
          and p2c.categories_id = cd.categories_id
          and cd.language_id = '" . (int)$_SESSION['languages_id'] . "'
          and pme.artists_id = '" . (int)$_GET['artists_id'] . "'
        order by cd.categories_name";
    } else {
      $filterlist_sql= "select distinct m.music_genre_id as id, m.music_genre_name as name
        from " . TABLE_PRODUCTS . " p, " .
        TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " .
        TABLE_PRODUCT_MUSIC_EXTRA . " pme, " .
        TABLE_RECORD_ARTISTS . " m
        where p.products_status = 1
          and pme.artists_id = m.artists_id
          and p.products_id = p2c.products_id
          and p2c.categories_id = '" . (int)$current_category_id . "'
        order by m.artists_name";
    }
    $getoption_set =  false;
    $do_filter_list = false;
    $filterlist = $db->Execute($filterlist_sql);
    if ($filterlist->RecordCount() > 1)
    {
      $do_filter_list = true;
      if (isset($_GET['artists_id']))
      {
        $getoption_set =  true;
        $get_option_variable = 'artists_id';
        $options = array(array('id' => '', 'text' => TEXT_ALL_CATEGORIES));
      } else {
        $options = array(array('id' => '', 'text' => TEXT_ALL_MUSIC_GENRE));
      }
      while (!$filterlist->EOF) {
        $options[] = array('id' => $filterlist->fields['id'], 'text' => $filterlist->fields['name']);
        $filterlist->MoveNext();
      }
    }
  }

  // Get the right image for the top-right
  $image = DIR_WS_TEMPLATE_IMAGES . 'table_background_list.gif';
  if ($current_category_id) {

    $sql = "select categories_image from " . TABLE_CATEGORIES . "
                where  categories_id = '" . (int)$current_category_id . "'";

    $image_name = $db->Execute($sql);
    $image = $image_name->fields['categories_image'];
  }
?>

[Allolex]

I've gone back and reviewed the code and couldn't find anything wrong. Trying to further reduce variables to isolate the problem, I've been working with a much more limited selection from our database of several thousand releases and it turns out that by limiting it to a subset of 20 or so causes no problems for the new products module. So it looks like you were right and the select statement is choking on something. Now I'm off to find out what that might be.

[Allolex]

I talked with a colleague and we think there are several possibilities since the data shouldn't be able to affect the display.

1. The select statement is malformed.
2. The randomisation routine isn't working right for the number of records it has to return in our 4000+ item database.
3. It's something else related to how the new products are selected from the database.

I'm starting to think that my bespoke music release stuff is revealing some underlying problem in the existing Zen Cart code. I'll continue to debug...

[Allolex]

After taking a very close look at the data that was being generated, I discovered that the error was due to some duplication of products in the Database. I have eliminated the duplicates and the New Products display works fine. I suspect this has something to do with the way I populated the products in the database, and possibly due to the extra data from product_music_extras not being present or formatted correctly.

Case closed.

Thanks once again for your help.

[Ajeh]

Thanks for the update that the problem was due to bad data in your database tables and not related to the Zen Cart code itself ...