Обсуждение: 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/>