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.,
