Hello everybody,
I have deveoped a portal using apache+php+mysql.
invoking mysql query through the php mysql_query fucntion is alright, but if I have to send three or four queries (say as a batch file), what should I do ?
Say I have to send:
locak tables mytable write;
update mytable set myfield="myvalue";
unlock tables;

One mehtod is that I should invoke mysq_query function for all the three queries seperately one after another. But I feel, that would inefficient, and there should be some better way.
Am I right ?
What other better alrtnatives are available.

Any guidance would be welcome.


Thanks in advance.

Prabhat Soni

Can't you create a stored procedure in MySql that does what you want and then
execute it in PhP as a normal query?

Hi folks,
I am sorry I am late. In fact, I was out of town for las few days.

Can you elaborate that "stored Proceedure" part. I feel the situation that I have encountered is not at all so un-common. php+mysql+apache is a very potent combination and the same must definitely be serving very high volume os traffick, making table-locking or sql-scripts unavoidable. Prbabley the stored proceedures as suggested is the answer. I would request you to please elaborate on the same.


Thanks in advance.

Prabhat Soni

A stored procedure is a block of SQL code, enclosed in BEGIN and END tags.
Check out
http://dev.mysql.com/doc/refman/5.0/...rocedures.html
for the MySql syntax.

Thanks a lot for your valuable pointers.

I believe stored procedures would suit the bill.

Unfortunately the same is not available in the mysql 4.1, which is the what I have.

I will think of upgrading in near future.


Thanks nevertheless for your help.


Prabhat Soni

Don't forget to use mysqldump to create a backup of all your databases before you upgrade,
just to be on the safe side.

the MySQL C API's mysql_query() function (as used in PHP) is quite intentionally designed to only allow one query at a time. So, without procedures, there's no real way to do it directly within PHP without some nasty shell_exec()/backtick calls to MySQL

Sir,
Please elabotrate !


Thanks


Prabhat Soni