Page 1 of 1

disable semicolon separator in mysql_query()

Posted: Wed Nov 13, 2002 5:53 am
by Heavy
The semicolon character in a MySQL script functions as a separator between SQL queries. The function mysql_query() in PHP reports an error if I use multiple queries in one request.

I would like to be able to disable that feature so that I can execute multiple queries using only one string. Example:

Code: Select all

mysql_query("delete from log;
insert into log set Timestamp='".time()."', Txt='".$message."'");
Is that possible?
I know it opens the door for dangerous things, but I could run into a situation where that would be very good.
Hmm... sorry I can't produce any great example right now.

OR

Is there any other good way to do cascaded multiple queries to mysql with PHP that I didn't mention?

Must it be just one query?

Posted: Wed Nov 13, 2002 7:27 am
by BDKR
Why don't you take a different tack. If you have one string that is made up of mulitple queries,
explode that string into an array. After that, just loop through the array and execute each element as the query that it is.

Cheers,
BDKR

Posted: Wed Nov 13, 2002 10:53 am
by Heavy
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
8O 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? :roll:

Posted: Wed Nov 13, 2002 11:27 am
by BDKR
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...
Off track? :!: Well, just so you know, there is support for transactions in MySQL that is well
beyond alpha. When most of us create tables in MySQL, we don't pay attention to the table type. That said, many people don't even know that there are even differing table types. It's those differing table types that provide things like transaction support and foriegn key constraints.

Check out the Inno Base tables (I think I got it right). There is transaction support there.

As for...
Because I don't think this is pretty:
I hear what you're saying. It may indeed be less than elegant. However, you sometimes have to come at coding like a Marine. Adapt and overcome. Sometimes you just don't have a choice.

Cheers,
BDKR

Posted: Wed Nov 13, 2002 12:25 pm
by Heavy
Hmmm...

You mean there is transaction support for InnoDB-tables in MySQL < 4.0 ? With Rollback?

I think (don't know really, revealing my newbieness...) that rollback is an SQL-server-feature that undoes the queries performed so far if a later query in a series of queries in a transaction fails???

Uh, was that understandable?...

I actually use MySQL-max with InnoDB tables in my major project right now. I chose InnoDB because of the support for foreign keys with ON DELETE CASCADE.

Posted: Wed Nov 13, 2002 12:57 pm
by volka

Posted: Wed Nov 13, 2002 1:41 pm
by BDKR
Hey Heavy,
I think (don't know really, revealing my newbieness...) that rollback is an SQL-server-feature that undoes the queries performed so far if a later query in a series of queries in a transaction fails???
It's my understanding that mysql is normally running in an autocommit mode. In other words, each executed query is immediately written to the disk and done with. However, when using innodb and transactions, you do not run in autocommit.

issue this query to make sure things are not running in autocommit.

Code: Select all

SET AUTOCOMMIT=0
Then use COMMIT to save the recent changes or ROLLBACK to abort.

Check out the mysql documentation online. It's not bad at all.

Anyways, to speak more directly to what you said in the above quote, if you start a series of queries with BEGIN, then each of them will be suceptible to a ROLLBACK or COMMIT. In other words, it's not going to count for a query that wasn't preceeded by a BEGIN.

Cheers,
BDKR
Uh, was that understandable?...
Ha ha ha......