Multiple data in a row

[ronnieb]

Hi

I have some code made up for my oscommerce webshop

Basically its a coupons modification, but im having a problem with my database configuration.

Basically I can choose multiple products to apply a discount to
however, when it goes into the database it only stores the 1st one

how, or where do i get it to store many

this is my table

CREATE TABLE IF NOT EXISTS `coupons` (
`coupons_id` int(10) unsigned NOT NULL auto_increment,
`coupons_code` varchar(32) NOT NULL,
`coupons_value` decimal(15,4) NOT NULL,
`product_ids` int(10) unsigned NOT NULL,
`type` varchar(32) NOT NULL,
`num_times` int(11) NOT NULL default '0',
`total_times` int(11) NOT NULL default '0',
`coupons_notice` varchar(250) NOT NULL,
`coupons_min_order` int(11) NOT NULL default '0',
`coupons_date` date default NULL,
PRIMARY KEY (`coupons_id`),
KEY `idx_code` (`coupons_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

`product_ids` int(10) unsigned NOT NULL,

is the row it goes into - how do i make take multiple numbers

Thanks

[bazz]

likely, you'll need to put the insert clause in a loop and run it as per the conditions of that loop.

Say you hjave several discount amounts. out them in an array and loop through it so that each run of the loop, willinsert the given amount for that loop. I hope that makes sense.

bazz

[ronnieb]

no, sorry havn't a clue - isn't there just someway to change the database row ?

[bazz]

Just re-read your post.

Are you trying to select numerous products and apply the same coupon code to them? if so, you'll need a 1-to-many tbale which stores the product_id and the coupon _id.

bazz

[Old Pedant]

What data in that table you showed has to change? Under what circumstances? And from what value(s) to what value(s)??

I don't see how we can answer you without having all the information.

Yes, you *can* change multiple records at the same time *IF* the change is consistent in all records. (e.g., "change the coupons_value field by adding 25% to it for each product in the following list of product_ids" -- quite doable. But "change coupon 17 by 10% and coupon 33 by 12% and coupon 92 by 32% and..." No, you'd do those one record at a time.)

[Old Pedant]

if so, you'll need a 1-to-many tbale which stores the product_id and the coupon _id.


Well, you don't *need* that table, but it would sure as heck be a really good idea.

I don't think we have enough info to judge, yet, what he really needs.

[ronnieb]

Are you trying to select numerous products and apply the same coupon code to them? if so, you'll need a 1-to-many tbale which stores the product_id and the coupon _id.


yes this is exactly what I want to do - obviously at the moment it only holds one number (product id) and i want it to hold 3 or 4

so for example

$5 off pillowcases (id 1), carpets (id 2) and duvets (id 3)

I would like it to hold all that data in 1 field, instead of having to do it 3 times as I do now

Thankyou

[bazz]

@Old Pedant:
I agree with you about there being insufficient info. I am said to have Crystal Balls and they were workin' fine tonight. :D

@ronnieb:
So you want the coupons table to have a column for product_id and in that column, you want to store all the product_ids that the coupon relates to? Not good. You need to read up on database normalisation, which for this scenario would show you that one piece of data should be stored in one field. more than one piece of data needs/should/ought, to be stored in another way, where no column has more than one bit of data.

I envisage the table structure better to be like this.


create table products
( product_id int auto_increment primary key
, product_name varchar(99) not null
, other cols here
) engine.....

CREATE TABLE IF NOT EXISTS `coupons` (
`coupons_id` int(10) unsigned NOT NULL auto_increment,
`coupons_code` varchar(32) NOT NULL,
`coupons_value` decimal(15,4) NOT NULL,
`product_ids` int(10) unsigned NOT NULL,
`type` varchar(32) NOT NULL,
`num_times` int(11) NOT NULL default '0',
`total_times` int(11) NOT NULL default '0',
`coupons_notice` varchar(250) NOT NULL,
`coupons_min_order` int(11) NOT NULL default '0',
`coupons_date` date default NULL,
PRIMARY KEY (`coupons_id`),
KEY `idx_code` (`coupons_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

create table coupons_products
( coupons_id int not null
, product_id int not null
, primary key (coupons_id, product_id)
, index pro-cou_ix (product_id, coupons_id)
) engine = .....


remove the red bit because it is now dealt with by the third table.

I would also recommend you look again at your engines. I would use innodb and then use foreign keys to ensure better 'referential integrity'.

bazz

[ronnieb]

thankyou

[bazz]

Did you not believe us?

http://www.sitepoint.com/forums/showthread.php?t=645081

which you posted after all advice given here.


I would like it to hold all that data in 1 field, instead of having to do it 3 times as I do now


This issue isn't about doing things three or four times where you want just to do them once. It is about best practice and the matter of 'referential integrity'.


bazz