Because I don't think this is pretty:
Code: Select all
mysql_query("Lock table abc write");
$result = mysql_query("select max(val1) from abc");
$MaxVal1 = mysql_fetch_row($result);
mysql_query("insert into abc set val2='".(intval($MaxVal1ї0])+1)."'");
mysql_query("unlock tables");
...Hmmm...
Quite a bad example really.
The thing is, I want to be able to perform something like MSSQL Stored Procedures can do without taking information out of the SQL server to PHP and then back again:
Code: Select all
CREATE proc old_spAlterRankingOrder
@strTAnswerID nvarchar(50) --TAnswerID in the new order seperated by ,
as
set nocount on
set xact_abort on
Begin tran
declare @StartPos int, @EndPos int, @TAnswerID int, @TOrderNo int
set @TOrderNo = 1
set @StartPos = 1
set @EndPos = charindex(",", @strTAnswerID, @StartPos)
while (@EndPos <> 0)
begin
set @TAnswerID = convert(int, substring(@strTAnswerID, @StartPos, @EndPos-@StartPos))
update RankingAnswer set TOrderNo = @TOrderNo where TAnswerID = @TAnswerID
set @StartPos = @EndPos + 1
set @EndPos = charindex(",", @strTAnswerID, @StartPos)
set @TOrderNo = @TOrderNo + 1
end
set @TAnswerID = convert(int, substring(@strTAnswerID, @StartPos, len(@strTAnswerID)-@StartPos + 1))
update RankingAnswer set TOrderNo = @TOrderNo where TAnswerID = @TAnswerID
Commit tran
GO

I believe there is no transaction support for MySQL (in current alpha versions, am I right?), so maybe this whole topic is way off track...
If I am not off track, and things like the above is kind of possible to do, maybe someone can tell how?
