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

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.

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:

  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

This block:

$products_new_query_raw = "SELECT products_id 
                             FROM " . TABLE_PRODUCTS . " p 
                             WHERE products_status = 1 ";

should be replaced with:

$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.

Let's break it down:```
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']

$breadcrumb->add(NAVBAR_TITLE);

$products_new_array = array();

--------------------------
All the above are not needed if this is to be run in a function
--------------------------

$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');

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

$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']);

A faster approach would be this simpler line:```
$array_ids[] = $check_products_all->fields['products_id'];

    $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:

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:```
$var1 = my_get_all_product_ids();
echo '<pre>';
print_r($var1);
echo '</pre>';

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.