new columns in category product listing in admin area

Hi there,

I’ve been trying to add new columns to the category product listing page so that I can view the “container type” of products in the list. I found some guidance in the forums, but have run into a bit of trouble.

What I’ve done so far:

I added the code below to the “category_product_listing.php” page.

I changed the header of the column.

I added code to display the container size.

I added the 3 pieces of code below to “function zen_reset_page” query in the “prod_cat_header_code.php” file. The container type is pulled from a “products_container” table, so I assumed that I would need to modify a query to pull the “container_type” variable to the page.

“c.container_type,”

" . TABLE_PRODUCTS_CONTAINER . " c,

and p.container_id = c.container_id

Unfortunately, the container types do not appear in the column. I’m not sure what I am missing. I am a beginner at php, but I am trying to learn.

I would be very grateful for any advice.

You have this code:

“c.container_type,”

" . TABLE_PRODUCTS_CONTAINER . " c,

and p.container_id = c.container_id

Can you post the whole sql statement?

Ajeh,

Thank you so much for responding.

Some additional facts:

I've tried inserting this same code in the "$products_query_raw" query in the "category_product_listing.php" page and it actually works! (There are two of these queries embedded in an if statement on that page. I put it in the second one.)

The problem is, almost all of the products in a category I call "Palms" disappear when I put the code in the "category_product_listing" page. I'm referring to the list on the "category_product_listing" page in the admin section where you add new products. (I think this may be due to the fact that I recently imported all of the products into that category and left something out?)

But, I'm just shooting in the dark. I'm a beginner at php, I'd love to learn how the zen code fits together if you can throw me a few bones.:smile:

A screen shot of the listing with the prices.

Screen shot of the empty "Palm" category. (There are actually about 50 products in this category.)

Sorry if I've over-explained, but I just want to make sure I'm clear.

thanks

Here's the code. I've highlighted the code I added.

function zen_reset_page() {
global $db, $current_category_id;
$look_up = $db->Execute("select
p.products_id,
pd.products_name,
p.products_model,

c.container_type,

p.products_quantity,
p.products_image,
p.products_price,
p.products_date_added,
p.products_last_modified,
p.products_date_available,
p.products_status,
p.products_quantity_order_min,
p.products_quantity_order_units,
p.products_priced_by_attribute,
p.product_is_free,
p.product_is_call,
p.products_quantity_mixed,
p.product_is_always_free_shipping,
p.products_quantity_order_max from

" . TABLE_PRODUCTS . " p,
" . TABLE_PRODUCTS_DESCRIPTION . " pd,
**" . TABLE_PRODUCTS_CONTAINER . " c, **
" . TABLE_PRODUCTS_TO_CATEGORIES . " p2c

where p.products_id = pd.products_id
and p.container_id = c.container_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p.products_id = p2c.products_id
and p2c.categories_id = '" . $current_category_id . "'

order by pd.products_name");

If you save your changes and put back the original code ... does it work with the data that you imported?

If not, are you missing the master_categories_id in your products?

Do you have an equal number of products and products_descriptions?

Do you have an equal number of categories and categories_descriptions?

Do you have references to your products in the products_to_categories table?

Ajeh,

Thank you for the questions.

If you save your changes and put back the original code ... does it work with the data that you imported?

When I restore the original code, all of the products are listed correctly. I also checked the products and products description tables and they are all there.

If not, are you missing the master_categories_id in your products?

the ids are there OK.

Do you have an equal number of products and products_descriptions?

I did not import product descriptions, just names of products.

Do you have an equal number of categories and categories_descriptions?

I have not added category descriptions to any of my categories yet.

Do you have references to your products in the products_to_categories table?

Yes. Each product I imported is assigned to a category.

It's the strangest thing.

If I insert the code in the second $products_query_raw in the product category listing page, the categories appear in the list, but it breaks my lists. (After further looking, I realize is the new code is not only breaking my category product list on the newly imported products in the "Palms" folder, it is breaking several others. Some of the products appear in the list, but not all. Some only have one product listed. But, when I remove the code. They are all fine.

Am I on the wrong track by trying to use the $products_query_raw query?

Is it possible just to write a new, simple query just to pull the container type from the table and put it in that column?

(Keep in mind I'm new with php/mysql. I usually bang my head against things until I understand them.:smile:)

Thank you for hanging with me on this.

Whether blank or not ... do you have a record in the products table and a matching record in the products_descriptions table?

Yes,

When I did the import (my first), I gave them product ids from #200 to #248. They appear in both the product table and the product description table.

I tried this with the manufacturers table in the same manner that you are using the your new table to get the manufacturers_name ...

I only touched the file:
categories_products_listing

and not the function at all in the:
prod_cat_header_code.php

to get this to display when looking at the list of products ...

I added the field and tables to the two $products_query_raw statements ... then to display the info I first just added it next to the $products->fields['products_name'] to make sure it was working ...

Perhaps you are trying to change too much ... :unsure:

I discovered what was causing the problem.

It's in the code I added to the query. The syntax is correct, but the logic is wrong.

where p.products_id = pd.products_id
and p.container_id = c.container_id

This query will only pull out records that have a matching container id. Unfortunately, many of the products (including the ones I imported) had not yet been assigned container sizes. Therefore, they had a value of 0. There was no container_id of 0 in the container table, so none of the products with a container_id of "0" were included in the query and therefore, not listed in the category product list.

To solve this, I created another option in the "container_types" table called "-none selected-" and gave it a value of 0.

I then set the default value in the table to "- none selected -" which automatically inserts a value of zero into the container_id field of all products in the product table. Therefore, there is now a container_id of "0" in both the container table and the products table and all products will always be included in the query.

And it works!

I'm sure there is another solution that is more elegant than this, but I'm just glad it is working for now. :clap:Meanwhile, I'm learning a lot more about php, mysql and how the Zen code fits together.

I'm also learning how wonderful the support for Zen Cart is. Your post about getting the query to work with the manufacturer_name was what triggered my thinking.

thank you for staying with me on this.

Bill

You are most welcome ... glad that you have things back on track ... :smile: