Using multipe products tables in mySQL

[Vom_Fass_USA_Guy]

What I need to do is like this place is:

 

http://www.vomfassuk.com/Webdocs/shop/prod...=233&xSec=6

 

It works when I leave it set to the standard products catalog.

 

I added a second products table to mySQL. I just tried to find everything that could have possibly been related to the product tables, and renamed "product" with "bottle".

 

I tried to create all of the tables that products could possible be dependent on, though my code blows up:

1146 - Table 'oscommerce.table_bottles' doesn't exist

 

select p.bottles_id, pd.bottles_name from TABLE_BOTTLES p, TABLE_BOTTLES_DESCRIPTION pd where p.bottles_id = pd.bottles_id and pd.language_id = '1' order by pd.bottles_name

 

[TEP STOP]

I've made a drop-down list for the various bottles that can be chosen for the liquid that is shown in the product_info.php page.

 

Then, what I'll need to do is have the image of the bottle appear on-click from the drop-down.

 

When the customer clicks "add to cart":

 

(the bottle) + (the volume of the bottle) * (the price-per-unit volume of the product)

 

must appear in the cart in some logical form.

 

This is the drop-down list I'm using:

<?php
new infoBox($info_box_contents, false, false);

$bottles_query = tep_db_query("select p.bottles_id, pd.bottles_name from " . TABLE_BOTTLES . " p, " . TABLE_BOTTLES_DESCRIPTION . " pd where p.bottles_id = pd.bottles_id and pd.language_id = '" . (int)$languages_id . "' order by pd.bottles_name");
if ($number_of_rows = tep_db_num_rows($bottles_query)) {

    $bottles_array = array();

 $bottles_array[] = array('id' => '', 'text' => 'Select a bottle for your product');

    while ($bottles = tep_db_fetch_array($bottles_query)) {
      $bottles_name = $bottles['bottles_name'];
      $bottles_array[] = array('id' => $bottles['bottles_id'],
                                     'text' => $bottles_name);
    }

    $info_box_contents = array();
    $info_box_contents[] = array('form' => tep_draw_form('bottles', tep_href_link(FILENAME_PRODUCT_INFO, '', 'NONSSL', false), 'get'),
                                 'text' => tep_draw_pull_down_menu('bottles_id', $bottles_array, (isset($HTTP_GET_VARS['bottles_id']) ? $HTTP_GET_VARS['bottles_id'] : ''), 'onChange="this.form.submit();" size="' . MAX_BOTTLES_LIST . '" style="width: 200"') . tep_hide_session_id());


  new infoBox($info_box_contents);
}
?>
	</td>
</tr>

 

This is the mySQL I used to add the second product table.

 

l-- 
-- Table structure for table `bottles`
-- 

CREATE TABLE `bottles` (
 `bottles_id` int(11) NOT NULL auto_increment,
 `bottles_quantity` decimal(65,2) NOT NULL,
 `bottles_model` varchar(12) collate latin1_general_ci default NULL,
 `bottles_image` varchar(64) collate latin1_general_ci default NULL,
 `bottles_price` decimal(15,4) NOT NULL,
 `bottles_date_added` datetime NOT NULL,
 `bottles_last_modified` datetime default NULL,
 `bottles_date_available` datetime default NULL,
 `bottles_weight` decimal(5,2) NOT NULL,
 `bottles_status` tinyint(1) NOT NULL,
 `bottles_tax_class_id` int(11) NOT NULL,
 `manufacturers_id` int(11) default NULL,
 `bottles_ordered` decimal(65,2) NOT NULL default '0.00',
 PRIMARY KEY  (`bottles_id`),
 KEY `idx_bottles_date_added` (`bottles_date_added`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=110 ;

-- 
-- Table structure for table `bottles_attributes`
-- 

CREATE TABLE `bottles_attributes` (
 `bottles_attributes_id` int(11) NOT NULL auto_increment,
 `bottles_id` int(11) NOT NULL,
 `options_id` int(11) NOT NULL,
 `options_values_id` int(11) NOT NULL,
 `options_values_price` decimal(15,4) NOT NULL,
 `price_prefix` char(1) collate latin1_general_ci NOT NULL,
 PRIMARY KEY  (`bottles_attributes_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=28 ;

-- 
-- Dumping data for table `bottles_attributes`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `bottles_attributes_download`
-- 

CREATE TABLE `bottles_attributes_download` (
 `bottles_attributes_id` int(11) NOT NULL,
 `bottles_attributes_filename` varchar(255) collate latin1_general_ci NOT NULL default '',
 `bottles_attributes_maxdays` int(2) default '0',
 `bottles_attributes_maxcount` int(2) default '0',
 PRIMARY KEY  (`bottles_attributes_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- 
-- Table structure for table `bottles_description`
-- 

CREATE TABLE `bottles_description` (
 `bottles_id` int(11) NOT NULL auto_increment,
 `language_id` int(11) NOT NULL default '1',
 `bottles_name` varchar(64) collate latin1_general_ci NOT NULL default '',
 `bottles_description` text collate latin1_general_ci,
 `bottles_url` varchar(255) collate latin1_general_ci default NULL,
 `bottles_viewed` int(5) default '0',
 PRIMARY KEY  (`bottles_id`,`language_id`),
 KEY `bottles_name` (`bottles_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=110 ;


-- 
-- Table structure for table `bottles_notifications`
-- 

CREATE TABLE `bottles_notifications` (
 `bottles_id` int(11) NOT NULL,
 `customers_id` int(11) NOT NULL,
 `date_added` datetime NOT NULL,
 PRIMARY KEY  (`bottles_id`,`customers_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- 
-- Dumping data for table `bottles_notifications`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `bottles_options`
-- 

CREATE TABLE `bottles_options` (
 `bottles_options_id` int(11) NOT NULL default '0',
 `language_id` int(11) NOT NULL default '1',
 `bottles_options_name` varchar(32) collate latin1_general_ci NOT NULL default '',
 PRIMARY KEY  (`bottles_options_id`,`language_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;


-- 
-- Table structure for table `bottles_options_values`
-- 

CREATE TABLE `bottles_options_values` (
 `bottles_options_values_id` int(11) NOT NULL default '0',
 `language_id` int(11) NOT NULL default '1',
 `bottles_options_values_name` varchar(64) collate latin1_general_ci NOT NULL default '',
 PRIMARY KEY  (`bottles_options_values_id`,`language_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;


-- 
-- Table structure for table `bottles_options_values_to_bottles_options`
-- 

CREATE TABLE `bottles_options_values_to_bottles_options` (
 `bottles_options_values_to_bottles_options_id` int(11) NOT NULL auto_increment,
 `bottles_options_id` int(11) NOT NULL,
 `bottles_options_values_id` int(11) NOT NULL,
 PRIMARY KEY  (`bottles_options_values_to_bottles_options_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=14 ;

 

Also, suggestions to give the ability to modify this new product table through the admin interface would be helpful in the long run, though not essential initially.

 

Please help! I'm in a real time crunch to help my employer get this thing on-line.

[Vom_Fass_USA_Guy]

Ahhhh...I need to declare variables somewhere? Like a TABLE_BOTTLES? I see...still, a sage of PHP, mySQL, osC helping me would be nice!

 

peace!

 

Michael

[Vom_Fass_USA_Guy]

Alright, where I'm at now, after learning a bit more about the variables associated with osCommerce and mySQL, is still trying to get the two product combinations submitted to the cart. I'll, again, plan on using two separate mySQL tables. One is for bottles of various volumes, and one is for the liquid that is priced per unit volume. I'm going with the drop-down list idea with the bottles that is embedded on the product_info.php page, either as a separate php module included, or by integrating the code (most likely). Any help, plz?