Looping in stored procedures
drop procedure if exists maxid;
delimiter $$
create procedure maxid(IN tablename varchar(100),OUT mx int)
begin
declare exitWithMessage bool;
declare max_id,tmp int ;
declare cur cursor for select id from tv_user;
declare continue handler for not found
set exitWithMessage :=true;
set max_id=0;
set exitWithMessage :=false;
open cur;
LOOP1:loop
fetch cur into tmp;
if exitWithMessage then
set mx:=max_id;
leave LOOP1;
else
if tmp > max_id then
set max_id:=tmp;
end if;
end if;
end loop LOOP1;
end$$
call maxid('tv_user',@c)
select @c
learning to use mysql functions
Usually requires to have the privilege to write functions
Simple function to add 2 numbers
SET GLOBAL log_bin_trust_function_creators = 1;
drop function if exists addnum;
delimiter $$
create function addnum(onenum int, twonum int)
returns int
NO SQL
begin
declare tot int;
set tot:=onenum+twonum;
return tot;
end$$
Function to return a random number
drop function if exists numberRand;
delimiter $$
create function numberRand()
returns int
begin
declare x int;
set x=rand()*1000;
return x;
end $$
Calling a user defined function from within a function
drop function if exists callingfnc;
delimiter $$
create function callingfnc()
returns int
begin
declare a,b int;
set a=numberRand();
set b=numberRand();
return (a+b);
end$$
Finding max id using stored procedure (MYSQL)
drop procedure if exists maxid;
delimiter $$
create procedure maxid(IN tablename varchar(100),OUT mx int)
begin
declare exitWithMessage bool;
declare max_id,tmp int ;
declare cur cursor for select id from tv_user;
declare continue handler for not found
set exitWithMessage :=true;
set max_id=0;
set exitWithMessage :=false;
open cur;
LOOP1:loop
fetch cur into tmp;
if exitWithMessage then
set mx:=max_id;
leave LOOP1;
else
if tmp > max_id then
set max_id:=tmp;
end if;
end if;
end loop LOOP1;
end$$
call maxid('tv_user',@x);
PHP Pear to execute MYSQL Stored Procedure
Tried to google but seems like there is no other option than using a multiquery to execute multiple Stored procedures
http://pear.php.net/bugs/bug.php?id=6418
Test script:
---------------
Sample script, when no results are needed:
for($i=0; $i<3; $i++)
{
//asuming we have a valid mysqli object
if (!$mysqli->multi_query("call someStoredProc(1)"))
{
printf("Error: %s\n", $mysqli->error);
}
else
{
do
{
if ($result = $mysqli->store_result())
{
$result->close();
}
} while ($mysqli->next_result());
}
}
Using stored Procedures MYSQL-PHP
Usually MySQLi API fails when 2 subsiquent calls are made to a stored procedure from PHP and after much googling around and playing with code i finally found fix ...
Create procedure
delimiter $$
create procedure getLoginIds()
begin
select loginid from user order by id desc limit 100;
end$$
delimiter $$
create procedure getLatestSchedules()
begin
select * from tschedules where active=1 order by id desc limit 100;
end$$
$con=new mysqli('dev.host.com','uname','pwd','db');if(mysqli_connect_errno()){die("Failed to connect");}
$sql='call getLoginIds()';
$result=$con->query($sql);
if(!$result){die("failed");}
//$result=$result1->fetch_assoc();
while($row=$result->fetch_row()){echo $row[0]."
";}
$result->free();
cleanSP($con);
//calling second sp
$sql='call getLatestSchedules()';
$result=$con->query($sql);
if(mysqli_connect_errno()){die("Failed to connect");}
while($row=$result->fetch_row()){echo $row[0]."
";}
cleanSP($con);
//cleans the result set in the connection
function cleanSP($con){while($con->next_result()){}
}
?>
No comments:
Post a Comment