Thread: design/copying a bunch of records
Hi all, I hope to be able to answer more questions than I need to ask later on, but for now.. :-} I'm trying to make a system where the admin will create a set of categories and products, sort order and all, where multiple users will be able to make custom sets with the same records, with optional translations, different prices, different product- and category sortorder. If the admin removes a product, the users won't be able to use that anymore either (inner join, or a loop-delete from user sets), but they can add their own products as well. If the admin adds a record, it would be copied to all user sets as well. This probably screams "duplicate data" but nearly all records will need translations or price changes, and custom sorting, so I couldn't figure any other good way. So I figured the best way was to copy the records from the admin-set to user-sets (but suggestions for other ways are very welcome!), and was wondering if there is a 'good' way to copy about 50 records to different tables every time you add a user account - the only way I can think of is write some loops in PHP. Better ways? TIA! Regards, Martin -- <mailto:mac.com@nemo> <http://www.mechintosh.com/>
Martin, > So I figured the best way was to copy the records from the > admin-set to user-sets (but suggestions for other ways are very > welcome!), and was wondering if there is a 'good' way to copy about > 50 records to different tables every time you add a user account - > the only way I can think of is write some loops in PHP. Better ways? > TIA! Yes ... the better way is to just have all products in one big table with a column to indicate what user they belong to. And then to modify the PHP query for each user so that it always includes both his and the admin's products. The same approach should be used with other "user specific" tables. -- Josh Berkus Aglio Database Solutions San Francisco
> > So I figured the best way was to copy the records from the >> admin-set to user-sets (but suggestions for other ways are very >> welcome!), and was wondering if there is a 'good' way to copy about >> 50 records to different tables every time you add a user account - >> the only way I can think of is write some loops in PHP. Better ways? >> TIA! > >Yes ... the better way is to just have all products in one big table with a >column to indicate what user they belong to. And then to modify the PHP >query for each user so that it always includes both his and the admin's >products. The same approach should be used with other "user specific" >tables. I forgot to mention there were a few differences needed between the admin and user sets, so I already had gone with the two tables. Now I'm trying to get this nifty postgresql code to work for me.. can anyone point out what I'm doing wrong? Trying to copy the records from the first groups into cgroups: ------------------------- $sql = " DECLARE tRecord RECORD; BEGIN FOR tRecord IN (SELECT id, kind, name, col_1, col_2, col_3, display FROM groups) LOOP INSERT INTO cgroups (id, kind, name, col_1, col_2, col_3, display, user_id) VALUES (tRecord.id, tRecord.kind, tRecord.name, tRecord.col_1, tRecord.col_2, tRecord.col_3, tRecord.display, $user_id); END LOOP; END; "; pg_query($sql); ------------------------- -> PostgreSQL query failed: ERROR: parser: parse error at or near "record" PHP 4.1.2, PostgreSQL 7.1 TIA! Regards, Martin -- <mailto:mac.com@nemo> <http://www.mechintosh.com/>