[How To] retrieve the name and not ID

Post Reply

12 posts Page 1 of 1

[How To] retrieve the name and not ID

LeorLindel

Active Member

Posts

175

Joined

Mon Feb 22, 2010 8:05 pm

Post by LeorLindel » Mon Aug 22, 2011 8:23 am

Hi,

I use v1.5.1.1

I create a class "color"

Code: Select all

CREATE TABLE IF NOT EXISTS `color_class` (
  `color_id` int(11) NOT NULL  AUTO_INCREMENT,
  `language_id` int(11) NOT NULL,
  `name` varchar(32) collate utf8_bin NOT NULL,
  PRIMARY KEY  (`color_id`,`language_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

in my product because I want the color does not appear in the options but as the model or manufacturer's name.
I get the ID of the color and not the name.

Product_color.PNG (211.89 KiB) Viewed 5409 times

Here's what I added to the query in file catalog/model/product/product.php:

Code: Select all

(SELECT cc.name FROM " . DB_PREFIX . "color_class cc WHERE cc.color_id = p.color_id AND cc.language_id = '" . (int)$this->config->get('config_language_id') . "') AS color_class,

Here is the modified query:

Code: Select all

		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT cc.name FROM " . DB_PREFIX . "color_class cc WHERE cc.color_id = p.color_id AND cc.language_id = '" . (int)$this->config->get('config_language_id') . "') AS color_class, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");

What have I forgotten?

Last edited by LeorLindel on Mon Aug 22, 2011 3:10 pm, edited 2 times in total.

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

LeorLindel

Active Member

Posts

175

Joined

Mon Feb 22, 2010 8:05 pm

Re: [How To] retrieve the name and not ID

rph

Expert Member

Posts

4102

Joined

Fri Jan 08, 2010 5:05 am

Location

Lincoln, Nebraska

Post by rph » Mon Aug 22, 2011 10:04 am

Double check your table. I see you have color_id and color_class_id which I'm assuming are supposed to be the same column.

-Ryan

rph

Expert Member

Posts

4102

Joined

Fri Jan 08, 2010 5:05 am

Location - Lincoln, Nebraska

Re: [How To] retrieve the name and not ID

LeorLindel

Active Member

Posts

175

Joined

Mon Feb 22, 2010 8:05 pm

Post by LeorLindel » Mon Aug 22, 2011 1:37 pm

Sorry,

This is a transcription error, the table is well designed like this:

Code: Select all

CREATE TABLE IF NOT EXISTS `color_class` (
   color_id `` int (11) NOT NULL AUTO_INCREMENT,
   `language_id` int (11) NOT NULL,
   `name` varchar (32) collate utf8_bin NOT NULL,
   PRIMARY KEY (`color_id`, `language_id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_bin;

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

LeorLindel

Active Member

Posts

175

Joined

Mon Feb 22, 2010 8:05 pm

Re: [How To] retrieve the name and not ID

rph

Expert Member

Posts

4102

Joined

Fri Jan 08, 2010 5:05 am

Location

Lincoln, Nebraska

Post by rph » Mon Aug 22, 2011 1:55 pm

Did you run the SQL query in phpmyadmin with some data to confirm it was the query and not the controller?

-Ryan

rph

Expert Member

Posts

4102

Joined

Fri Jan 08, 2010 5:05 am

Location - Lincoln, Nebraska

Re: [How To] retrieve the name and not ID

LeorLindel

Active Member

Posts

175

Joined

Mon Feb 22, 2010 8:05 pm

Post by LeorLindel » Mon Aug 22, 2011 2:50 pm

The SQL query works well as this I return the ID of the distinctive color for each product

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

LeorLindel

Active Member

Posts

175

Joined

Mon Feb 22, 2010 8:05 pm

Re: [How To] retrieve the name and not ID

LeorLindel

Active Member

Posts

175

Joined

Mon Feb 22, 2010 8:05 pm

Post by LeorLindel » Mon Aug 22, 2011 4:32 pm

Here are the changes I made

1/2

In admin\controller\catalog\product.php

Add:

Code: Select all

		$this->data['entry_energy'] = $this->language->get('entry_energy');
		$this->data['entry_engine'] = $this->language->get('entry_engine');
		$this->data['entry_registration'] = $this->language->get('entry_registration');
		$this->data['entry_vintage'] = $this->language->get('entry_vintage');
		$this->data['entry_mileage'] = $this->language->get('entry_mileage');
		$this->data['entry_fiscal_power'] = $this->language->get('entry_fiscal_power');
		$this->data['entry_number_of_doors'] = $this->language->get('entry_number_of_doors');
		$this->data['entry_power_din'] = $this->language->get('entry_power_din');
		$this->data['entry_transmission'] = $this->language->get('entry_transmission');
		$this->data['entry_color_class'] = $this->language->get('entry_color_class');

After:

Code: Select all

		$this->data['entry_layout'] = $this->language->get('entry_layout');

Add:

Code: Select all

    	$this->data['tab_characteristic'] = $this->language->get('tab_characteristic');

After:

Code: Select all

    	$this->data['tab_data'] = $this->language->get('tab_data');

Add:

Code: Select all

		$this->load->model('localisation/language');
		
		$this->data['languages'] = $this->model_localisation_language->getLanguages();

		if (isset($this->request->post['energy'])) {
      		$this->data['energy'] = $this->request->post['energy'];
    	} elseif (isset($product_info)) {
			$this->data['energy'] = $product_info['energy'];
		} else {
      		$this->data['energy'] = '';
    	}

		if (isset($this->request->post['engine'])) {
      		$this->data['engine'] = $this->request->post['engine'];
    	} elseif (isset($product_info)) {
			$this->data['engine'] = $product_info['engine'];
		} else {
      		$this->data['engine'] = '';
    	}
		
		if (isset($this->request->post['registration'])) {
      		$this->data['registration'] = $this->request->post['registration'];
    	} elseif (isset($product_info)) {
			$this->data['registration'] = $product_info['registration'];
		} else {
      		$this->data['registration'] = '';
    	}
		
		if (isset($this->request->post['vintage'])) {
      		$this->data['vintage'] = $this->request->post['vintage'];
    	} elseif (isset($product_info)) {
			$this->data['vintage'] = $product_info['vintage'];
		} else {
      		$this->data['vintage'] = '';
    	}
		
		if (isset($this->request->post['mileage'])) {
      		$this->data['mileage'] = $this->request->post['mileage'];
    	} elseif (isset($product_info)) {
			$this->data['mileage'] = $product_info['mileage'];
		} else {
      		$this->data['mileage'] = '';
    	}
		
		if (isset($this->request->post['fiscal_power'])) {
      		$this->data['fiscal_power'] = $this->request->post['fiscal_power'];
    	} elseif (isset($product_info)) {
			$this->data['fiscal_power'] = $product_info['fiscal_power'];
		} else {
      		$this->data['fiscal_power'] = '';
    	}
		
		if (isset($this->request->post['number_of_doors'])) {
      		$this->data['number_of_doors'] = $this->request->post['number_of_doors'];
    	} elseif (isset($product_info)) {
			$this->data['number_of_doors'] = $product_info['number_of_doors'];
		} else {
      		$this->data['number_of_doors'] = '';
    	}
		
		if (isset($this->request->post['power_din'])) {
      		$this->data['power_din'] = $this->request->post['power_din'];
    	} elseif (isset($product_info)) {
			$this->data['power_din'] = $product_info['power_din'];
		} else {
      		$this->data['power_din'] = '';
    	}
		
		if (isset($this->request->post['transmission'])) {
      		$this->data['transmission'] = $this->request->post['transmission'];
    	} elseif (isset($product_info)) {
			$this->data['transmission'] = $product_info['transmission'];
		} else {
      		$this->data['transmission'] = '';
    	}

		$this->load->model('localisation/color_class');
		
		$this->data['color_classes'] = $this->model_localisation_color_class->getColorClasses();
    	
		if (isset($this->request->post['color_id'])) {
      		$this->data['color_id'] = $this->request->post['color_id'];
    	} else if (isset($product_info)) {
      		$this->data['color_id'] = $product_info['color_id'];
    	} else {
			$this->data['color_id'] = 0;
		}

After:

Code: Select all

		$this->data['length_classes'] = $this->model_localisation_length_class->getLengthClasses();
    	
		if (isset($this->request->post['length_class_id'])) {
      		$this->data['length_class_id'] = $this->request->post['length_class_id'];
    	} elseif (isset($product_info)) {
      		$this->data['length_class_id'] = $product_info['length_class_id'];
    	} else {
      		$this->data['length_class_id'] = $this->config->get('config_length_class_id');
		}

In admin\model\catalog\product.php

Replace:

Code: Select all

		$this->db->query("INSERT INTO " . DB_PREFIX . "product SET model = '" . $this->db->escape($data['model']) . "', sku = '" . $this->db->escape($data['sku']) . "', upc = '" . $this->db->escape($data['upc']) . "', location = '" . $this->db->escape($data['location']) . "', quantity = '" . (int)$data['quantity'] . "', minimum = '" . (int)$data['minimum'] . "', subtract = '" . (int)$data['subtract'] . "', stock_status_id = '" . (int)$data['stock_status_id'] . "', date_available = '" . $this->db->escape($data['date_available']) . "', manufacturer_id = '" . (int)$data['manufacturer_id'] . "', shipping = '" . (int)$data['shipping'] . "', price = '" . (float)$data['price'] . "', points = '" . (int)$data['points'] . "', weight = '" . (float)$data['weight'] . "', weight_class_id = '" . (int)$data['weight_class_id'] . "', length = '" . (float)$data['length'] . "', width = '" . (float)$data['width'] . "', height = '" . (float)$data['height'] . "', length_class_id = '" . (int)$data['length_class_id'] . "', status = '" . (int)$data['status'] . "', tax_class_id = '" . (int)$data['tax_class_id'] . "', sort_order = '" . (int)$data['sort_order'] . "', date_added = NOW()");

By:

Code: Select all

		$this->db->query("INSERT INTO " . DB_PREFIX . "product SET model = '" . $this->db->escape($data['model']) . "', energy = '" . $this->db->escape($data['energy']) . "', engine = '" . $this->db->escape($data['engine']) . "', registration = '" . $this->db->escape($data['registration']) . "', vintage = '" . $this->db->escape($data['vintage']) . "', mileage = '" . $this->db->escape($data['mileage']) . "', fiscal_power = '" . $this->db->escape($data['fiscal_power']) . "', number_of_doors = '" . $this->db->escape($data['number_of_doors']) . "', power_din = '" . $this->db->escape($data['power_din']) . "', transmission = '" . $this->db->escape($data['transmission']) . "', color_id = '" . $this->db->escape($data['color_id']) . "', sku = '" . $this->db->escape($data['sku']) . "', upc = '" . $this->db->escape($data['upc']) . "', location = '" . $this->db->escape($data['location']) . "', quantity = '" . (int)$data['quantity'] . "', minimum = '" . (int)$data['minimum'] . "', subtract = '" . (int)$data['subtract'] . "', stock_status_id = '" . (int)$data['stock_status_id'] . "', date_available = '" . $this->db->escape($data['date_available']) . "', manufacturer_id = '" . (int)$data['manufacturer_id'] . "', shipping = '" . (int)$data['shipping'] . "', price = '" . (float)$data['price'] . "', points = '" . (int)$data['points'] . "', weight = '" . (float)$data['weight'] . "', weight_class_id = '" . (int)$data['weight_class_id'] . "', length = '" . (float)$data['length'] . "', width = '" . (float)$data['width'] . "', height = '" . (float)$data['height'] . "', length_class_id = '" . (int)$data['length_class_id'] . "', status = '" . (int)$data['status'] . "', tax_class_id = '" . (int)$data['tax_class_id'] . "', sort_order = '" . (int)$data['sort_order'] . "', date_added = NOW()");

Replace:

Code: Select all

	public function editProduct($product_id, $data) {
		$this->db->query("UPDATE " . DB_PREFIX . "product SET model = '" . $this->db->escape($data['model']) . "', sku = '" . $this->db->escape($data['sku']) . "', upc = '" . $this->db->escape($data['upc']) . "', location = '" . $this->db->escape($data['location']) . "', quantity = '" . (int)$data['quantity'] . "', minimum = '" . (int)$data['minimum'] . "', subtract = '" . (int)$data['subtract'] . "', stock_status_id = '" . (int)$data['stock_status_id'] . "', date_available = '" . $this->db->escape($data['date_available']) . "', manufacturer_id = '" . (int)$data['manufacturer_id'] . "', shipping = '" . (int)$data['shipping'] . "', price = '" . (float)$data['price'] . "', points = '" . (int)$data['points'] . "', weight = '" . (float)$data['weight'] . "', weight_class_id = '" . (int)$data['weight_class_id'] . "', length = '" . (float)$data['length'] . "', width = '" . (float)$data['width'] . "', height = '" . (float)$data['height'] . "', length_class_id = '" . (int)$data['length_class_id'] . "', status = '" . (int)$data['status'] . "', tax_class_id = '" . (int)$data['tax_class_id'] . "', sort_order = '" . (int)$data['sort_order'] . "', date_modified = NOW() WHERE product_id = '" . (int)$product_id . "'");

By:

Code: Select all

	public function editProduct($product_id, $data) {
		$this->db->query("UPDATE " . DB_PREFIX . "product SET model = '" . $this->db->escape($data['model']) . "', energy = '" . $this->db->escape($data['energy']) . "', engine = '" . $this->db->escape($data['engine']) . "', registration = '" . $this->db->escape($data['registration']) . "', vintage = '" . $this->db->escape($data['vintage']) . "', mileage = '" . $this->db->escape($data['mileage']) . "', fiscal_power = '" . $this->db->escape($data['fiscal_power']) . "', number_of_doors = '" . $this->db->escape($data['number_of_doors']) . "', power_din = '" . $this->db->escape($data['power_din']) . "', transmission = '" . $this->db->escape($data['transmission']) . "', color_id = '" . $this->db->escape($data['color_id']) . "', sku = '" . $this->db->escape($data['sku']) . "', upc = '" . $this->db->escape($data['upc']) . "', location = '" . $this->db->escape($data['location']) . "', quantity = '" . (int)$data['quantity'] . "', minimum = '" . (int)$data['minimum'] . "', subtract = '" . (int)$data['subtract'] . "', stock_status_id = '" . (int)$data['stock_status_id'] . "', date_available = '" . $this->db->escape($data['date_available']) . "', manufacturer_id = '" . (int)$data['manufacturer_id'] . "', shipping = '" . (int)$data['shipping'] . "', price = '" . (float)$data['price'] . "', points = '" . (int)$data['points'] . "', weight = '" . (float)$data['weight'] . "', weight_class_id = '" . (int)$data['weight_class_id'] . "', length = '" . (float)$data['length'] . "', width = '" . (float)$data['width'] . "', height = '" . (float)$data['height'] . "', length_class_id = '" . (int)$data['length_class_id'] . "', status = '" . (int)$data['status'] . "', tax_class_id = '" . (int)$data['tax_class_id'] . "', sort_order = '" . (int)$data['sort_order'] . "', date_modified = NOW() WHERE product_id = '" . (int)$product_id . "'");

Add:

Code: Select all

				'p.energy',
				'p.engine',
				'p.registration',
				'p.vintage',
				'p.mileage',
				'p.fiscal_power',
				'p.number_of_doors',
				'p.power_din',
				'p.transmission',
				'p.color_id',

After:

Code: Select all

			$sort_data = array(
				'pd.name',
				'p.model',

In admin\view\template\catalog\product_form.tpl

Replace:

Code: Select all

      <div id="tabs" class="htabs"><a href="#tab-general"><?php echo $tab_general; ?></a><a href="#tab-data"><?php echo $tab_data; ?></a><a href="#tab-links"><?php echo $tab_links; ?></a><a href="#tab-attribute"><?php echo $tab_attribute; ?></a><a href="#tab-option"><?php echo $tab_option; ?></a><a href="#tab-discount"><?php echo $tab_discount; ?></a><a href="#tab-special"><?php echo $tab_special; ?></a><a href="#tab-image"><?php echo $tab_image; ?></a><a href="#tab-reward"><?php echo $tab_reward; ?></a><a href="#tab-design"><?php echo $tab_design; ?></a></div>

By:

Code: Select all

      <div id="tabs" class="htabs"><a href="#tab-general"><?php echo $tab_general; ?></a><a href="#tab-data"><?php echo $tab_data; ?></a><a href="#tab-characteristic"><?php echo $tab_characteristic; ?></a><a href="#tab-links"><?php echo $tab_links; ?></a><a href="#tab-attribute"><?php echo $tab_attribute; ?></a><a href="#tab-option"><?php echo $tab_option; ?></a><a href="#tab-discount"><?php echo $tab_discount; ?></a><a href="#tab-special"><?php echo $tab_special; ?></a><a href="#tab-image"><?php echo $tab_image; ?></a><a href="#tab-reward"><?php echo $tab_reward; ?></a><a href="#tab-design"><?php echo $tab_design; ?></a></div>

Add:

Code: Select all

        <div id="tab-characteristic">
          <table class="form">
            <tr>
              <td><?php echo $entry_engine; ?></td>
              <td><input type="text" name="engine" value="<?php echo $engine; ?>" /></td>
            </tr>
            <tr>
              <td><?php echo $entry_energy; ?></td>
              <td><input type="text" name="energy" value="<?php echo $energy; ?>" /></td>
            </tr>
            <tr>
              <td><?php echo $entry_registration; ?></td>
              <td><input type="text" name="registration" value="<?php echo $registration; ?>" size="12" class="date" /></td>
            </tr>
            <tr>
              <td><?php echo $entry_vintage; ?></td>
              <td><input type="text" name="vintage" value="<?php echo $vintage; ?>" /></td>
            </tr>
            <tr>
              <td><?php echo $entry_mileage; ?></td>
              <td><input type="text" name="mileage" value="<?php echo $mileage; ?>" /></td>
            </tr>
            <tr>
              <td><?php echo $entry_fiscal_power; ?></td>
              <td><input type="text" name="fiscal_power" value="<?php echo $fiscal_power; ?>" /></td>
            </tr>
            <tr>
              <td><?php echo $entry_power_din; ?></td>
              <td><input type="text" name="power_din" value="<?php echo $power_din; ?>" /></td>
            </tr>
            <tr>
              <td><?php echo $entry_number_of_doors; ?></td>
              <td><input type="text" name="number_of_doors" value="<?php echo $number_of_doors; ?>" /></td>
            </tr>
            <tr>
              <td><?php echo $entry_transmission; ?></td>
              <td><input type="text" name="transmission" value="<?php echo $transmission; ?>" /></td>
            </tr>
            <tr>
              <td><?php echo $entry_color_class; ?></td>
              <td><select name="color_id">
                  <?php foreach ($color_classes as $color_class) { ?>
                  <?php if ($color_class['color_id'] == $color_id) { ?>
                  <option value="<?php echo $color_class['color_id']; ?>" selected="selected"><?php echo $color_class['name']; ?></option>
                  <?php } else { ?>
                  <option value="<?php echo $color_class['color_id']; ?>"><?php echo $color_class['name']; ?></option>
                  <?php } ?>
                  <?php } ?>
                </select></td>
            </tr>
          </table>
        </div>

Before:

Code: Select all

        <div id="tab-links">

Last edited by LeorLindel on Mon Aug 22, 2011 4:49 pm, edited 2 times in total.

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

LeorLindel

Active Member

Posts

175

Joined

Mon Feb 22, 2010 8:05 pm

Re: [How To] retrieve the name and not ID

LeorLindel

Active Member

Posts

175

Joined

Mon Feb 22, 2010 8:05 pm

Post by LeorLindel » Mon Aug 22, 2011 4:33 pm

2/2

In catalog\controller\product\product.php

Add;

Code: Select all

			$this->data['text_energy'] = $this->language->get('text_energy');
			$this->data['text_engine'] = $this->language->get('text_engine');
			$this->data['text_registration'] = $this->language->get('text_registration');
			$this->data['text_vintage'] = $this->language->get('text_vintage');
			$this->data['text_mileage'] = $this->language->get('text_mileage');
			$this->data['text_fiscal_power'] = $this->language->get('text_fiscal_power');
			$this->data['text_number_of_doors'] = $this->language->get('text_number_of_doors');
			$this->data['text_power_din'] = $this->language->get('text_power_din');
			$this->data['text_transmission'] = $this->language->get('text_transmission');
			$this->data['text_color_class'] = $this->language->get('text_color_class');

After:

Code: Select all

			$this->data['text_tags'] = $this->language->get('text_tags');

Add:

Code: Select all

			$this->data['energy'] = $product_info['energy'];
			$this->data['engine'] = $product_info['engine'];
			$this->data['registration'] = $product_info['registration'];
			$this->data['vintage'] = $product_info['vintage'];
			$this->data['mileage'] = $product_info['mileage'];
			$this->data['fiscal_power'] = $product_info['fiscal_power'];
			$this->data['number_of_doors'] = $product_info['number_of_doors'];
			$this->data['power_din'] = $product_info['power_din'];
			$this->data['transmission'] = $product_info['transmission'];
			$this->data['color_id'] = $product_info['color_id'];

After:

Code: Select all

			$this->data['model'] = $product_info['model'];

In catalog\model\catalog\product.php

Replace:

Code: Select all

		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS color_class_id, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");

By:

Code: Select all

		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT cc.name FROM " . DB_PREFIX . "color_class cc WHERE cc.color_id = p.color_id AND cc.language_id = '" . (int)$this->config->get('config_language_id') . "') AS color_class, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");

Add (2 occurrences):

Code: Select all

				'energy'			=> $query->row['energy'],
				'engine'            => $query->row['engine'],
				'registration'		=> $query->row['registration'],
				'vintage'			=> $query->row['vintage'],
				'mileage'			=> $query->row['mileage'],
				'fiscal_power'		=> $query->row['fiscal_power'],
				'number_of_doors'	=> $query->row['number_of_doors'],
				'power_din'			=> $query->row['power_din'],
				'transmission'		=> $query->row['transmission'],
				'color_id'			=> $query->row['color_id'],

After:

Code: Select all

				'model'				=> $query->row['model'],

Add:

Code: Select all

				'p.energy',
				'p.engine',
				'p.registration',
				'p.vintage',
				'p.mileage',
				'p.fiscal_power',
				'p.number_of_doors',
				'p.power_din',
				'p.transmission',
				'p.color_id',

After:

Code: Select all

				'p.model',

In catalog\view\theme\default\template\product\product.tpl

Replace:

Code: Select all

        <span><?php echo $text_reward; ?></span> <?php echo $reward; ?><br />

By:

Code: Select all

        <?php if ($engine >= 1)  { ?>
        <span><?php echo $text_engine; ?></span> <?php echo $engine; ?><br />
        <?php } ?>
        <?php if ($energy >= 1)  { ?>
        <span><?php echo $text_energy; ?></span> <?php echo $energy; ?><br />
        <?php } ?>
        <?php if ($registration >= 1)  { ?>
        <span><?php echo $text_registration; ?></span> <?php echo $registration; ?><br />
        <?php } ?>
        <?php if ($vintage >= 1)  { ?>
        <span><?php echo $text_vintage; ?></span> <?php echo $vintage; ?><br />
        <?php } ?>
        <?php if ($mileage >= 1)  { ?>
        <span><?php echo $text_mileage; ?></span> <?php echo $mileage; ?><br />
        <?php } ?>
        <?php if ($fiscal_power >= 1)  { ?>
        <span><?php echo $text_fiscal_power; ?></span> <?php echo $fiscal_power; ?><br />
        <?php } ?>
        <?php if ($number_of_doors >= 1)  { ?>
        <span><?php echo $text_number_of_doors; ?></span> <?php echo $number_of_doors; ?><br />
        <?php } ?>
        <?php if ($power_din >= 1)  { ?>
        <span><?php echo $text_power_din; ?></span> <?php echo $power_din; ?><br />
        <?php } ?>
        <?php if ($transmission >= 1)  { ?>
        <span><?php echo $text_transmission; ?></span> <?php echo $transmission; ?><br />
        <?php } ?>
        <?php if ($color_id >= 1)  { ?>
        <span><?php echo $text_color_class; ?></span> <?php echo $color_id; ?><br />
        <?php } ?>
        <?php if ($reward >= 1)  { ?>
        <span><?php echo $text_reward; ?></span> <?php echo $reward; ?><br />
        <?php } ?>

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

LeorLindel

Active Member

Posts

175

Joined

Mon Feb 22, 2010 8:05 pm

Re: [How To] retrieve the name and not ID

rph

Expert Member

Posts

4102

Joined

Fri Jan 08, 2010 5:05 am

Location

Lincoln, Nebraska

Post by rph » Mon Aug 22, 2011 10:32 pm

Well there you go. You're getting the color_id because that's exactly what you're asking for. You didn't add color_class into it.

-Ryan

rph

Expert Member

Posts

4102

Joined

Fri Jan 08, 2010 5:05 am

Location - Lincoln, Nebraska

Re: [How To] retrieve the name and not ID

LeorLindel

Active Member

Posts

175

Joined

Mon Feb 22, 2010 8:05 pm

Post by LeorLindel » Mon Aug 22, 2011 10:43 pm

What should I add and where?

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

LeorLindel

Active Member

Posts

175

Joined

Mon Feb 22, 2010 8:05 pm

Re: [How To] retrieve the name and not ID

rph

Expert Member

Posts

4102

Joined

Fri Jan 08, 2010 5:05 am

Location

Lincoln, Nebraska

Post by rph » Mon Aug 22, 2011 11:03 pm

There are a couple ways depending on how everything is set up but if you didn't do the development yourself you need to ask the person who did to fix it. The code is clearly bugged/incomplete.

-Ryan

rph

Expert Member

Posts

4102

Joined

Fri Jan 08, 2010 5:05 am

Location - Lincoln, Nebraska

Re: [How To] retrieve the name and not ID

LeorLindel

Active Member

Posts

175

Joined

Mon Feb 22, 2010 8:05 pm

Post by LeorLindel » Mon Aug 22, 2011 11:26 pm

It is I who made the changes and I see what's missing and where it is missing?

Tous les packs de langues Française depuis la v1.4.7 et toutes les versions intégrales 100 % Français sont disponibles sur le Portail Officiel Français.

LeorLindel

Active Member

Posts

175

Joined

Mon Feb 22, 2010 8:05 pm

Re: [How To] retrieve the name and not ID

rph

Expert Member

Posts

4102

Joined

Fri Jan 08, 2010 5:05 am

Location

Lincoln, Nebraska

Post by rph » Mon Aug 22, 2011 11:45 pm

Good.

The issue is you have two pieces of info, p.color_id and cc.name. What you keep using is p.color_id. You need to pass cc.name from the model, assign it in the controller, and display it in the template.

-Ryan

rph

Expert Member

Posts

4102

Joined

Fri Jan 08, 2010 5:05 am

Location - Lincoln, Nebraska

Post Reply