Product_info.php question

[lelchuck]

Here's an example of what I did and the help I need.

 

I added a new table to my OSC mysql database and called it grades. I populated it with A, B, and C (for example)

 

I made all of the necessary changes to allow the admin and customers (using CAP) to select the grade from a pulldown menu.

 

I have not figured out how to get it to display on the product_info page.

 

 

Here's some key information:

I named a field in both products and grades tables "grades_id"

 

The data I want in in the grades table is in "grades_title"

 

I was successful in displaying the "grades_id" from the products table, but not the "grades_title" field from the grades table.

 

I think there are at least two parts to the solution

 

First

----

I know that I need to modify this line as part of the solution (this one worked to display the "grades_id" from the products table.

 

"$product_info_query = tep_db_query("select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_quantity, p.products_image, pd.products_url, p.products_price, p.grades_id, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");

 

 

Second

----

I think a line pointing to the grades table has to be created.

 

Here's a line to show how to get the price from a separate table

Maybe I need to do an adaption of this?????

 

$products_price = $currencies->display_price($product_info['products_price'], tep_get_tax_rate($product_info['products_tax_class_id']));

 

----

 

Any help is appreciated

 

thanks

larry

[brandoneilers]

You could use this as your query:

 

"$product_info_query = tep_db_query("select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_quantity, p.products_image, pd.products_url, p.products_price, p.grades_id, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id, g.grades_title from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_GRADES . " g where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pd.products_id = p.products_id and p.grades_id = g.grades_id and pd.language_id = '" . (int)$languages_id . "'");

 

Then you would access the grades title using $product_info_query['grades_title'];

 

Just make sure you have the new grades table you added in the catalog/includes/database.php file.

 

Hope that helps!

[lelchuck]

You could use this as your query:

 

"$product_info_query = tep_db_query("select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_quantity, p.products_image, pd.products_url, p.products_price, p.grades_id, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id, g.grades_title from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_GRADES . " g where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pd.products_id = p.products_id and p.grades_id = g.grades_id and pd.language_id = '" . (int)$languages_id . "'");

 

Then you would access the grades title using $product_info_query['grades_title'];

 

Just make sure you have the new grades table you added in the catalog/includes/database.php file.

 

Hope that helps!

 

 

Thanks Brandon

 

I replaced the line in the code and received the following error:

 

======

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and ptf.products_extra_fields_value<>'' and (pef.languages_id='0' or pef.languag' at line 5

 

SELECT pef.products_extra_fields_name as name, ptf.products_extra_fields_value as value ,pef.products_extra_fields_status as status FROM products_extra_fields pef LEFT JOIN products_to_products_extra_fields ptf ON ptf.products_extra_fields_id=pef.products_extra_fields_id WHERE ptf.products_id= and ptf.products_extra_fields_value<>'' and (pef.languages_id='0' or pef.languages_id='1') ORDER BY products_extra_fields_order

 

[TEP STOP]

 

======

thanks

larry

[lelchuck]

Success

 

Thanks Brandon ever so much

 

I forgot the second line of code!

 

I actually had 3 such tables (grade, status, outcomes) that I wanted to include on the product_info page.

 

I was able to follow your logic and now all 3 entries are working properly.

 

 

thanks again

larry

[brandoneilers]

Glad I could help. :D

[lelchuck]

Glad I could help. :D

 

 

Two other question related to this.

 

1. If I have a field in the products table (called "grade"), how can I populate it with the value from "grades_title" in the "grades" table?

2. If I have a field in the products table (called "outcome"), how can I populate it with the first 4 characters of the value from "outcomes_name" in the "outcomes" table?

 

cheers

larry