"Rosen" <[EMAIL PROTECTED]> wrote in message
> I have an orders with one main record in table1 ( client, date, e.t.c. )
> detail description in table2 ( all materials with quant, price, e.t.c. )
> and I save data in table1 for positions (range of id - autoinc field of
> records in table2) for detailed data of order . And I don't want someone
> else to insert data in table2, because will be a problem with orders.
> Now I insert data first in table2 and then insert main record in table1
> the range of id's of detail order data.
It seems to me that when you have a 1-to-many relationship (table1 = orders,
table2 = line items) it's very silly to store a range of table2 row ids in
table1. You should probably, instead, store the table1 row id in each
related record of table2, and slap an index on that for speed of retrieval
if needed. Then it won't matter what order records enter table2.
i.e. Mike orders some memory (3 sticks) and Janet orders some media (3
spindles) at the same time, and their data gets interleaved.
rowid:001 -- desc:Mike's Order
rowid:002 -- desc:Janet's Order
rowid:001 -- orderid:001 -- quant:1 -- price:150.00 -- desc:DIMM1GB
rowid:002 -- orderid:002 -- quant:1 -- price:20.00 -- desc:DVDRWSpindle
rowid:003 -- orderid:002 -- quant:1 -- price:9.99 -- desc:CDRSpindle
rowid:004 -- orderid:001 -- quant:2 -- price99.99 -- desc:MUSHKINREV2
rowid:005 -- orderid:002 -- quant:1 -- price:25.00 -- desc:CDRWSpindle
You see what I mean? To de-tangle, just:
SELECT table1.desc, table2.quant, table2.price, table2.desc as linedesc
FROM table1, table2
WHERE table1.rowid = table2.orderid
[...AND table1.rowid= (specific order)...]
ORDER BY table2.orderid, table2.rowid
Or something of that nature, as it suits you.
Unless you have a HARD need to do it where the parent knows the children,
it's just easier if the children know the parent.
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php