|
|
Posted by Erwin Moller on 03/29/07 13:49
bill wrote:
> Erwin Moller wrote:
>> bill wrote:
>>
>> Hi Bill,
>>
>>> In an application I am writing the user can define a series of
>>> steps to be followed. I save them in a sql database using the
>>> field "order" (a smallint) as the primary key.
>>> (there are in the range of 20 steps)
>>>
>>> On the admin page the steps are listed, in "order" order and the
>>> user can create new steps and assign an order and all is well.
>>>
>>> The problem may come in using a renumber function which should
>>> take the steps in their current order and reassign the "order"
>>> key, assigning each set an "order" that is the prior "order" + 10.
>>
>> Well, what about:
>>
>> UPDATE tblwhatever set order = order+10;
>
> I think I was unclear:
> the order might evolve to:
>
> 10
> 12
> 16
> 18
> 30
> 33
>
> and after renumbering it should be back to
> 10
> 20
> 30
> 40
> 50
> 60
Aha. I see now what you mean.
Hmm, that is tricky to do with 1 UPDATE statement since you didn't use a
'normal' autonumbering Primary Key.
I think you have to resort to a simple script to do that for you.
Can we assume that you NEVER have any double values for 'order'?
I mean, did you make that field PK or at least UNIQUE? Or as a last resort,
check before inserting that the 'order' didn't exist yet?
If they are all UNIQUE try something like:
-- pseudocode ADODB-like, adjust to your own prefered databaselogic
$SQL = "SELECT order FROM tblorder ORDER BY order";
$result = $connection->Execute($SQL)->getArray();
$count = 10;
foreach ($result as $oneOrder){
$oldOrderNum = $oneOrder["order"];
// Update
$SQL = "UPDATE tblorder SET order=".$count;
$SQL .= " WHERE (order=".$oldOrderNum.");";
$connection->Execute($SQL);
}
>>
>>
>>> In other databases I have worked with this is a major pain
>>> because as soon as you save a record the order may change.
>>
>> I never heard of a database that changes the values of other rows if you
>> insert a new row.
>> Are you maybe using this order filed as Primary Key?
>>
>> If so: That is a major designmistake.
>> (I'll elaborate if this turns out to be the case.)
>
> guilty.
> That is why the updating of the "order" would result in the
> problem.
>
> If I use another primary key (an arbitrary ID) then I still have
> to read the records in "order" number and reassign the value of
> "order" and then read the next record.
In this case: maybe.
But take it from an old db-fart like me: Start using an autonumbering PK for
each table you create from now on, unless you have a compelling reason not
to. I actually was never in a situation where an autonumbering PK hurts.
Only in some cases you really know it doesn't make sense (scratch tables,
temp tables, etc).
As a rule of thumb: Use autonumbering PK on every table.
In my above example I wouldn't need to be afraid you have some values for
'order' that are the same (which would make that approach fail).
A autonumbering PK would have eliminated that problem.
>>
>>> OTOH, in mysql it appears that if you do a select for the whole
>>> table you have them all in memory and can change the value of the
>>> field "order" without having to worry about getting that record
>>> again and then save the whole table.
>>>
>>> Is that correct ?
>>
>> No.
>
> bummer
Unless you mean my above script. That takes the all 'order' values from
tblorder in memory, but that is independent from the database, it is PHP's
memory.
>>
>> A query is a query: something that return results (or none).
>> If you SELECT something, nothing is changed.
>>
>> I don't understand what you mean by 'selecting the whole table in
>> memory'. If you need to UPDATE certain records, use the UPDATE command to
>> do so.
>
> but after having UPDATEd, one still has the problem of reading
> the next record
Not if you read them all at once first into memory of php and start updating
then.
>>
>>> bill
>>
>> Regards,
>> Erwin Moller
>
> your assistance is appreciated
>
You're welcome
> bill
Regards,
Erwin Moller
Navigation:
[Reply to this message]
|