|
|
Posted by bill on 03/30/07 11:35
Erwin Moller wrote:
> 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.
Understood. I will add a autonumbering PK
>
>
>>>> 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.
Works for me.
Thanks
>
>>> 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
I certainly appreciate the tutorial. I understand the internals
of mySQL just a little bit better.
bill
Navigation:
[Reply to this message]
|