Thread: SQL question

Page 1 of 2 12 LastLast
Results 1 to 10 of 15
  1. #1
    Join Date
    Aug 2008
    Posts
    274
    Plugin Contributions
    0

    Default SQL question

    This is a dumb question but I'm trying to add a products manufacturer name to the end all the products.

    For example if the product is "Blue earrings" and the manufacterer's names is "Tim's Gemstones" then I would like to switch the name of that product to "Blue Earrings - Tim's Gemstones". For seo purposes.

    Can someone help me with the sql for this please. I have access to my database but can't figure out the correct syntax.

    Here is basically what I'm trying to do

    Set zen_products_description.products_name Where zen_products.manufacturers_id = 5 zen_products_description.products_name+zen_products.manufacturers_id

    thank you

  2. #2
    Join Date
    Feb 2012
    Location
    mostly harmless
    Posts
    1,809
    Plugin Contributions
    8

    Default Re: SQL question

    Why not just modify your templates instead of the database? The templates are used to customize how content is displayed on your website.

    Otherwise if you must do this directly in the database, take a look at http://dev.mysql.com/doc/refman/5.0/...unction_concat. You may need to use nested statements to accomplish your goal of doing this with one SQL command.

  3. #3
    Join Date
    Aug 2008
    Posts
    274
    Plugin Contributions
    0

    Default Re: SQL question

    Thanks, that is a great idea.

    Any idea which file I would edit and how to make it add the manufacturer-id to the end of the product name?

    I would need it on this page

    http://kgfinegifts.com/shop/index.ph...dex&cPath=2_23

    and this page

    http://kgfinegifts.com/shop/index.ph...roducts_id=846

    Ideally would like to display as

    Product Name - Manf Name


    thank you

  4. #4
    Join Date
    Feb 2005
    Location
    Lansing, Michigan USA
    Posts
    20,024
    Plugin Contributions
    3

    Default Re: SQL question

    I don't know how many products you have, so I don't know if it wouldn't be easier to just add the manufacturers name to the product name when you create/edit the product.

    p.s. Your header is all messed up in Internet Explorer 8 (and 7).

  5. #5
    Join Date
    Aug 2008
    Posts
    274
    Plugin Contributions
    0

    Default Re: SQL question

    Thank you, I'll check it out in IE 8

    Yeah it would be much easier to edit the templates or run an SQL statement if possible, she has 1100 products

    thanks

  6. #6
    Join Date
    Feb 2012
    Location
    mostly harmless
    Posts
    1,809
    Plugin Contributions
    8

    Default Re: SQL question

    Quote Originally Posted by timhersh View Post
    Any idea which file I would edit and how to make it add the manufacturer-id to the end of the product name?

    I would need it on this page

    http://kgfinegifts.com/shop/index.ph...dex&cPath=2_23
    Product_listing is used to list products in a category I believe. The module file puts the product information into an array which is then used by tpl_tabular_display.php to display product listings (At least in the default templates, yours may differ).

    Quote Originally Posted by timhersh View Post
    Take a look at tpl_product_info_display. This is used to display product_info for most products. Product types (if you use any custom product types) typically use product_xxxx_info_display where xxxx is the name of the product type.

    Don't Forget
    Use the override system! This will save you a fair amount of time in the future when upgrading or changing to a different template (or if your client changes their mind and decides they no longer want the MFG to be displayed).

    Other thoughts
    If the MFG name is intended to always be a part of the product name or should only be present for some products... you may want to look into messing around some more with SQL, writing some code in PHP to read / write the changes, or messing around with custom product types.
    Last edited by lhungil; 17 Sep 2012 at 02:37 PM.

  7. #7
    Join Date
    Aug 2008
    Posts
    274
    Plugin Contributions
    0

    Default Re: SQL question

    [QUOTE=lhungil;1150872]Product_listing is used to list products in a category I believe. The module file puts the product information into an array which is then used by tpl_tabular_display.php to display product listings (At least in the default templates, yours may differ).

    Thank you. I think I follow, but where is this file located tpl_tabular_display.php

    I can't find it, or what line of code is in it that you are recommending me to change. I can search for it with the toolbox that way.


    I'm using zen cart 1.39

    thanks

  8. #8
    Join Date
    Feb 2012
    Location
    mostly harmless
    Posts
    1,809
    Plugin Contributions
    8

    Default Re: SQL question

    Changing the template for displaying product info ("includes/templates/<your template>/templates/tpl_product_info_display.php") is relatively easy. The other part is not...

    The files used by default to display all product listings are listed below. It may take some time to familiarize yourself with them. Keep in mind changing those files may affect the search results page (for the built-in search feature). Alternatively you may want to create your own module / template for displaying the product list inside category pages :)

    tpl_modules_product_listing.php: Used to display product listings (such as a category with products). Found at "includes/templates/<your template>/templates/tpl_modules_product_listing.php".

    tpl_tabular_display.php: Used to display the list of products in a "tabular" format. Your templates may use a different file. Look in "tpl_modules_product_listing.php" to see what your template calls. Found at "includes/templates/<your template>/common/tpl_tabular_display.php".

    product_listing.php: Used to create an array containing product listing information. Your templates may use a different file. Look in the above file to see what your template calls. Found at "includes/modules/product_listing.php". Should be copied to "includes/modules/<your template>/product_listing.php" and the copy edited, not the original.

  9. #9
    Join Date
    Aug 2008
    Posts
    274
    Plugin Contributions
    0

    Default Re: SQL question

    Thank you, I have it somewhat nailed down now.

    It is one of these two files /includes/modules/premium6c/product_listing.php
    or
    /includes/modules/premium6c/product_listing_original.php

    I'm not sure what to edit to make it display both, here is the piece I think I need to edit in product_listing_original.php




    for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
    switch ($column_list[$col]) {
    case 'PRODUCT_LIST_MODEL':
    $lc_text = TABLE_HEADING_MODEL;
    $lc_align = '';
    $zc_col_count_description++;
    break;
    case 'PRODUCT_LIST_NAME':
    $lc_text = TABLE_HEADING_PRODUCTS + TABLE_HEADING_PRODUCTS;
    $lc_align = '';
    $zc_col_count_description++;
    break;
    case 'PRODUCT_LIST_MANUFACTURER':
    $lc_text = TABLE_HEADING_MANUFACTURER;
    $lc_align = '';
    $zc_col_count_description++;
    break;
    case 'PRODUCT_LIST_PRICE':
    $lc_text = TABLE_HEADING_PRICE;
    $lc_align = 'right' . (PRODUCTS_LIST_PRICE_WIDTH > 0 ? '" width="' . PRODUCTS_LIST_PRICE_WIDTH : '');
    $zc_col_count_description++;
    break;
    case 'PRODUCT_LIST_QUANTITY':
    $lc_text = TABLE_HEADING_QUANTITY;
    $lc_align = 'right';
    $zc_col_count_description++;
    break;
    case 'PRODUCT_LIST_WEIGHT':
    $lc_text = TABLE_HEADING_WEIGHT;
    $lc_align = 'right';
    $zc_col_count_description++;
    break;
    case 'PRODUCT_LIST_IMAGE':
    $lc_text = TABLE_HEADING_IMAGE;
    $lc_align = 'center';
    $zc_col_count_description++;
    break;
    }

  10. #10
    Join Date
    Aug 2008
    Posts
    274
    Plugin Contributions
    0

    Default Re: SQL question

    Ideally would like to display as

    Product Name - Manf Name

 

 
Page 1 of 2 12 LastLast

Similar Threads

  1. v154 SQL Script Question
    By Feznizzle in forum General Questions
    Replies: 2
    Last Post: 2 Jul 2015, 07:13 PM
  2. Question about using SQL
    By tcarden in forum All Other Contributions/Addons
    Replies: 2
    Last Post: 15 Jan 2013, 04:32 PM
  3. question about $sql statements
    By tcarden in forum Contribution-Writing Guidelines
    Replies: 3
    Last Post: 27 Dec 2012, 03:51 AM
  4. SQL question
    By pixelpadre in forum General Questions
    Replies: 9
    Last Post: 26 Apr 2012, 02:14 PM
  5. SQL question
    By DigitalShadow in forum General Questions
    Replies: 67
    Last Post: 7 Dec 2010, 12:56 AM

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