I am stupid please help me!

so I am having problems with oscommerce. I need to customize the products table witch means I also have to work with the following tables, products to categories, categories, products discription. my 4 tables look like this:

CREATE TABLE `products` (
`Products_id` int(11) NOT NULL auto_increment,
`Products_Name` varchar(100) NOT NULL default '',
`Products_Merchant` blob NOT NULL,
`Products_Description` blob NOT NULL,
`Products_Sku` varchar(50) NOT NULL default '',
`Products_Brand` varchar(50) NOT NULL default '',
`Products_SalePrice` varchar(7) NOT NULL default '0.00',
`Products_Price` varchar(7) NOT NULL default '0.00',
`Products_RetailPrice` varchar(7) NOT NULL default '0.00',
`Products_LinkURL` blob NOT NULL,
`Products_ImpressionURL` blob NOT NULL,
`Products_ImageURL` blob NOT NULL,
`Products_Currency` blob NOT NULL,
`Products_Category` varchar(100) NOT NULL default '',
PRIMARY KEY (`Products_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2298 ;

CREATE TABLE `products_description` (
`products_id` int(11) NOT NULL auto_increment,
`language_id` int(11) NOT NULL default '1',
`products_name` varchar(64) NOT NULL default '',
`products_description` text,
`products_url` varchar(255) default NULL,
`products_viewed` int(5) default '0',
PRIMARY KEY (`products_id`,`language_id`),
KEY `products_name` (`products_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;

CREATE TABLE `categories` (
`categories_id` int(11) NOT NULL auto_increment,
`categories_image` varchar(64) default NULL,
`parent_id` int(11) NOT NULL default '0',
`sort_order` int(3) default NULL,
`date_added` datetime default NULL,
`last_modified` datetime default NULL,
PRIMARY KEY (`categories_id`),
KEY `idx_categories_parent_id` (`parent_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

CREATE TABLE `products_to_categories` (
`products_id` int(11) NOT NULL default '0',
`categories_id` int(11) NOT NULL default '0',
PRIMARY KEY (`products_id`,`categories_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I need to make these tables talk to each other but I am to stupid to figure it out. I don't normally do databases but I really need this to work asap!
thanks for your help!

 

you need to use joins to get tables to talk together. here's a few links courtesy of our good friends at google:

http://www.plus2net.com/sql_tutorial/sql_inner_join.php
http://www.mysqlfreaks.com/statements/10.php
http://www.techbookreport.com/sql-tut3.html

HTH

VG

 

select * from table 1, table 2 where table1.row1 = table2.row1 ..........

Use that sort of thing mate.

Ste

 

On your product_info.php page there is a query which looks something like this

$product_info_query = tep_db_query("SELECT products_id p, products_description p2, ...... FROM products p, products_description p2 WHERE p.products_id = p2.products_id

So if you have extra fields in your products table then just add

p.Products_Sku to the SELECT query

If you want to update the admin pages to edit and add all the new fields then you'll have to add these new fields in about 5 different places unless you have some oscommerce mod where you can add new "type" fields easily.

 

Weirfire, thanks that was very helpfull, could you tell me the 5 places to update for admin or do you know of a mod that will add "type" fields?

 

I've no idea off the top of my head. I'd have to spend as much time as you figuring it out again.

Use dreamweaver to do search and replace on the whole file system.

 

http://www.oscommerce.com/community/contributions,2575/category,all/search,query+debug

Use that to see all the queries. If you're not familiar with all the files, it will be a nightmare for you to find all the instances of where things changed. With that tool it will show the queries on the bottom of the page (to you only). That will at least help find them but then still you don't know exactly which file but you're one step closer.

If you're on Linux, you can GREP a known field name from similar queries to find all instances.
http://my.brandeis.edu/bboard/q-and-a-fetch-msg?msg_id=0000Ug

Otherwise, indeed Dreamweaver or Zend Studio etc. will find in contents of a file.

Basically, if you need all product queries to change, then you have to edit some 100 files at least.

And I know from experience (now have 102 tables from 60 odd in the standard installation). Have many custom product fields myself.

 

Hey T0PS. Do you reckon there would be a market for a script that could add or remove fields from products on 1 simple page? How much would you pay for it?