MYSQL Stored procedure PHP: July 2007

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)

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$$

```
<?php
```

$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]."<br>";
```

```
  
```

```
  
```

```
 }
```

```
 $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]."<br>";
```

```
 
```

```
}
```

```
cleanSP($con);
```

//cleans the result set in the connection

```
function cleanSP($con){
```

```
 while($con->next_result()){
```

```
 }
```

```
}
```

```
?>
```

MYSQL Stored procedure PHP

Monday, July 9, 2007

Looping in stored procedures

learning to use mysql functions

Finding max id using stored procedure (MYSQL)

PHP Pear to execute MYSQL Stored Procedure

Using stored Procedures MYSQL-PHP

Blog Archive