Results 1 to 6 of 6
  1. #1

    help question retreive all product id's / database api

    Hi all,
    I'm writing a tag cloud contrib for zen cart and need a list of all the product id's from the database. I want to use the database abstraction layer, however am having difficulty finding documentation, and the code is a bit hard for me to follow.

    Can anyone point me in the direction of a good discussion about retrieve data from the database with existing function, or writing new functions to retrieve data.

    Also does the zen cart book cover such details?

    Thanks again code heads,

    Nathan

  2. #2
    Join Date
    Oct 2006
    Posts
    625
    Plugin Contributions
    0

    Default Re: retreive all product id's / database api

    Hi Nathan,

    The book does not go into such technical details as it is meant as a manual for store owners to administer and run their Zen Cart site without touching code.
    Goh Koon Hoek, author of "e-Start Your Web Store with Zen Cart".
    Printed book: www.lulu.com/content/10576284
    Electronic book and Errata: www.cucumbermedia.com/store

  3. #3

    Idea or Suggestion Re: retreive all product id's / database api

    Thanks Canopy,

    What I would give for some nice diagrams of zen carts program structure!!

    Anyhow I came up with a rough solution to get the values out of the db. Basically I copied all the new product header and template file and trimmed it down until I just had what I needed. (Although I'm still not 100% on the language references).

    The result was:
    Code:
      require(DIR_WS_MODULES . zen_get_module_directory('require_languages.php'));
      $breadcrumb->add(NAVBAR_TITLE);
    
      $products_new_array = array();
    
      $products_new_query_raw = "SELECT products_id 
                                 FROM " . TABLE_PRODUCTS . " p 
                                 WHERE products_status = 1 ";
    
      $products_new_query_raw = $db->bindVars($products_new_query_raw, ':languageID', $_SESSION['languages_id'], 'integer'); // If you know what this line does let me know. Thanks
    
    
    $check_products_all = $db->Execute($products_new_query_raw);
    
    $array_ids = array();
    while (!$check_products_all->EOF) 
    {
        array_push($array_ids,$check_products_all->fields['products_id']);
        $check_products_all->MoveNext();
    }
    When I try and turn this block of code into a function though, I get a whole host of php errors about members, classes and objects... I'm not 100% on the references to language in the code, perhaps it something to do with that.

    If you have some ideas, please comment, however I'm sure next session when I have a clear head i'll be able to figure it out.

    Thanks again,

    Nathan

  4. #4
    Join Date
    Aug 2004
    Posts
    1,590
    Plugin Contributions
    1

    Default Re: retreive all product id's / database api

    This block:

    PHP Code:
    $products_new_query_raw "SELECT products_id 
                                 FROM " 
    TABLE_PRODUCTS " p 
                                 WHERE products_status = 1 "

    should be replaced with:

    PHP Code:
    $products_new_query_raw "SELECT products_id 
                                 FROM (" 
    TABLE_PRODUCTS " p, " TABLE_PRODUCTS_DESCRIPTION " pd)
                                 WHERE p.products_id = pd.products_id AND p.products_status = 1 AND pd.language_id = :languageID"
    ;

    $products_new_query_raw $db->bindVars($products_new_query_raw':languageID'$_SESSION['languages_id'], 'integer'); 
    if you intend to gather the products ID with languages.

  5. #5
    Join Date
    Jan 2004
    Posts
    65,318
    Blog Entries
    7
    Plugin Contributions
    228

    Default Re: retreive all product id's / database api

    Let's break it down:
    Code:
     require(DIR_WS_MODULES . zen_get_module_directory('require_languages.php'));
    The above is used within page modules in order to load language files relevant to the page being referenced at runtime ... ie: the result of $_GET['main_page']

    Code:
      $breadcrumb->add(NAVBAR_TITLE);
    This adds the current page reference to the breadcrumb bar, using NAVBAR_TITLE (from the lang file loaded earlier) as the text name on the link.

    Code:
      $products_new_array = array();
    this line is redundant and not used here

    --------------------------
    All the above are not needed if this is to be run in a function
    --------------------------
    Code:
      $products_new_query_raw = "SELECT products_id 
                                 FROM " . TABLE_PRODUCTS . " p 
                                 WHERE products_status = 1 ";
    This is a basic query to retrieve products_id for all "active" products from the database

    Code:
      $products_new_query_raw = $db->bindVars($products_new_query_raw, ':languageID', $_SESSION['languages_id'], 'integer');
    This line is irrelevant, because you're not referencing languages in your query.
    It would normally be used to sanitize the SQL query and ensure that the value passed for language id was truly an integer before getting to the database. Basically, binding data-types in the query, enforcing proper use, largely to prevent injection risks

    Code:
    $check_products_all = $db->Execute($products_new_query_raw);
    This executes the query defined earlier, with all results ending up in the $check_products_all object

    Code:
    $array_ids = array();
    initialize array which will hold the requested product ids


    Code:
    while (!$check_products_all->EOF) 
    {
    begin a routine to loop through all retrieved records
    Code:
        array_push($array_ids,$check_products_all->fields['products_id']);
    store retrieved records into the $array_ids array.
    A faster approach would be this simpler line:
    Code:
    $array_ids[] = $check_products_all->fields['products_id'];

    Code:
        $check_products_all->MoveNext();
    }
    MoveNext continues the while loop ... which ends when the end of the retrieved records is reached.



    If you wanted to convert this all to a function, you'd need to reference the $db object as a global -- otherwise your database connection details are unreachable within the local scope of your function.

    ie:
    Code:
    function my_get_all_product_ids() {
      global $db;
      $products_new_query_raw = "SELECT products_id 
                                 FROM " . TABLE_PRODUCTS . " p 
                                 WHERE products_status = 1 ";
      $check_products_all = $db->Execute($products_new_query_raw);
      $array_ids = array();
    
      while (!$check_products_all->EOF) 
      {
        $array_ids[] = $check_products_all->fields['products_id'];
        $check_products_all->MoveNext();
      }
      return $array_ids;
    }
    Proof of concept:
    - upload the above code by inserting it in a file in the extra_functions folder
    - run this from somewhere on your site:
    Code:
    $var1 = my_get_all_product_ids();
    echo '<pre>';
    print_r($var1);
    echo '</pre>';
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  6. #6

    Default Re: retreive all product id's / database api

    Thanks Doc,

    Got the beta version of the ZenTagCloud contrib up today. Still has no neat GUI and the algorithms for generating the tag sizes are lacking. But its getting there.

    I think I'll marry my contrib with the user tracking contrib and use data about the popularity of tags at some stage soon.

    I'll keep you posted.

    Thanks again.
    -- -- -- -- -- -- -- -- -- --
    TRAVEL PHOTOGRAPHY, THAT DOESN'T COST THE EARTH!
    -- -- -- -- -- -- -- -- -- --
    Online Photo Showcase :: http://lightfootphotos.com
    Blog :: http://madteckhead.com

 

 

Similar Threads

  1. retreive order_total and customers_email_address in TABLE_ORDERS
    By george_usa in forum Managing Customers and Orders
    Replies: 1
    Last Post: 4 Jan 2011, 04:21 AM
  2. Retreive deleted product?
    By shelbyErgopro in forum General Questions
    Replies: 1
    Last Post: 13 Jul 2009, 06:40 PM
  3. SQL problem: How to retreive Attributes for a product ordered.
    By momopopo in forum Setting Up Categories, Products, Attributes
    Replies: 0
    Last Post: 6 Jul 2006, 05:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR