Procedures In Mysql

Procedures In Mysql

when you pass an sql query using mysql_query() function each the qyery gets compiled and after that it gets executed even if the query is same. This reduces the performance your application.

To overcome this ,we can use a procedure or a function in mysql.

Procedure will not a return a value back to the caller (but, using out parameters we are able to send multiple values to caller) where as a function returns a single a value back to the caller.

Procedure as associated with mainly two type of parameters.

1.      IN –pass by value

2.      OUT- pass by reference.

Steps to create a procedure :

1.      Start mysql db server.

2.      Open mysql prompt

C:\xampp\mysql\bin>mysql –u root –p

3.      Change the delimiter

Mysql> delimiter//

4.      Select a DB

Mysql>use employee //

Mysql> create a procedure myproc(OUT param1 INT)

BEGIN

Select count(*) into param1 from emp;

End//

Mysql>delimiter;

To invoke a procedure

Mysql> call myproc(@p);

Mysql> select @p;

Php  program to invoke the above procedure?

<?php

 $con = mysql_connect(“localhost”,”root”,””);

Mysql_select_db(“employee”);

$res = mysql_query(“call myproc(@s)”);

If($res)

{

$rs=mysql_query(“select @s”);

//var_dump($rs);

$row = mysql_fetch_row($rs);

Echo “number of records=”.$row[0];

}

Else

Echo “procedure not found”;

Mysql_close($con);

?>

Inserting images in mysql:

Mysql has a BLOB(binary large objects) datatype which can be used to store binary data . blobs are typically images ,audio or other multimedia blob objects. Mysql has 4 blobs types.

a.       TINYBLOB

b.      BLOB

c.       MEDIUMBLOB

d.      LONGBLOB

All these types differ only in their sizes.

1.      Create a table named “test_image” in mysql “employee” database having 3 columns show below.

Mysql> create table test_image(id int(10) auto_increment primary key, name varchar(20)m image blob);

Program to insert an image:

<?php

 $con = mysql_connect(‘localhost’,’root’,’’);

Mysql_select_db(“employee”);

$data = file_get_contents(“images/back1.jpg”);

$data = addslashes($data);

$res = mysql_query(“insert into test_image(name,image) values(‘back1.jpg’,’$data’)”);

If($res)

Echo “record inserted”;

Else

Echo mysql_error();

Mysql_close();

?>

Displaying images stored in mysql?

<?php

$con = mysql_connect('localhost','root','');

mysql_select_db("emp");

$rs = mysql_query("select * from test_image where id=1");

if(mysql_num_rows($rs)>0)

{

    $row = mysql_fetch_row($rs);

    $n = file_put_contents('Photo0104.jpg', $row[2]);

    header("content_type:image/jpeg");

    echo $row[2];

}

else

    echo "no matching record found";

mysql_close($con);

?>

OutPut:

Image displayed.

procedures in mysql, stored procedures in mysql, mysql variables in stored procedures, stored procedures in mysql examples.,