Stored Procedures in MySQL

The programmers and developers all over the world are celebrating the existence of MySQL 5.0 for a good reason. The version has finally paved the way for using stored procedures with this popular open source database. The stored procedures are hoarded at database server in the form of commands and SQL statements and could be invoked for any type of application. Thus, there is advantage for programmers to shed away the need of creating their own SQL; a tiresome job.

Without taking much of the time, let us understand the syntax for utilizing the procedures:

1.

2.
CREATE ProcedureCREATE [DEFINER = { user | CURRENT_USER }]PROCEDURE sp_name ([proc_parameter[,…]])
3.

4.
[characteristic …] routine_body
5.

6.
IN the above syntax, the parameters are specified AS IN, OUT OR INOUT. However, IN case of CREATE FUNCTION displayed below, the only parameter allowed IS IN.
7.

8.
CREATE
9.

10.
[DEFINER = { user | CURRENT_USER }]
11.

12.
FUNCTION sp_name ([func_parameter[,…]])
13.

14.
RETURNS type
15.

16.
[characteristic …] routine_body

Return statement is included in the syntax for a function and it specifies the return type of the function. Routine body is included to define a valid SQL statement. The syntax contains characteristic to provide information regarding nature of data. This may include attributes like CONTAINS SQL, NO SQL, READS SQL DATA and MODIFIES SQL DATA.

Alter Procedure

This statement is used to modify or alter the characteristics possessed by a stored procedure. Here is the syntax:

1.

2.
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic …]

It offers you the advantage to bring in more than one change in the characteristics of stored procedures.

Drop Procedure

In order to remove a stored procedure, there is provision of DROP statement in MySQL database. The syntax for this takes the following shape:

1.

2.
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

IF EXISTS is used to check the existence of specified procedure or function. It saves the statement from generating an error in case the procedure or function doesn’t exist.

Other Statements For Procedures

Apart from those mentioned above, there are other statements used for stored procedures. Here is the list o these statements:

* Show Create Procedure – It is used to return the text of a stored procedure, which was defined previously using Create syntax. In the similar manner, there is Show Create Function statement to accomplish this task for stored function.
* Show Procedure Status – It is used to return the characteristic of the specified stored procedure. These characteristics include name, type, creation date and so on. You may use Show Function Status also.
* CALL – It is used to invoke the stored procedure created using Create Procedure statement.
* Begin End – It is used to specify multiple statements for carrying out different types of operations and executions.
* Declare – It is used to help programmers in declaring and defining local variables, conditions, procedures and so on.
* SET – This statement is used to modify the values of local as well as global variables.

Also, there are condition statements like IF THEN ELSE, WHILE, CASE WHEN amongst others, which could be employed to operate these procedures.

Thus, MySQL stored procedures are tools for better performance, secure environment, providing ease of maintenance and also, for optimization.

Recursive function in PHP

recursive function is a function that calls itself repeatedly for a specified condition.
Here is an example function which calls iteself to perform a tree structure for a category table
Here is the category table:
category_id parent_id category_name
1 0 CMS
2 0 Blogs
3 0 Forums
4 0 E-Commerce
5 1 Joomla
6 1 Mambo
7 6 Templates
8 6 Mods/Components
9 4 OSCommerce
10 2 Wordpress
11 10 Themes
12 10 Plugins

1.

2.
Root’;
3.
$allcats = getTree();
4.
foreach($allcats as $key=>$value)
5.
{
6.
echo "$value"
7.
}
8.
echo ‘’;
9.

10.
function getTree($id=0)
11.
{
12.
static $cates = array();
13.
static $times = 0;
14.
$times++;
15.
$result = mysql_query("SELECT category_id,category_name FROM category_table WHERE parent_id=$id ORDER BY category_name");
16.
while($row = mysql_fetch_assoc($result))
17.
{
18.
$cates[$row[‘category_id’]] = str_repeat("| ",$times-1)."|___".$row[‘category_name’];
19.
getTree($row[‘category_id’]);
20.
}
21.
$times—;
22.
return $cates;
23.
}
24.
?>

The out put of this program would be the following select box:

PHP Interview Questions and Answers

Question: Is PHP a case sensitive programming language?

Answer: PHP is a partially case sensitive programming language. We can use function names, class names in case insensitive manner.

Question: What is mean by LAMP?

Answer: LAMP means combination of Linux, Apache, MySQL and PHP.

Question: How do you get the user’s ip address in PHP?

Answer: Using the server variable: $_SERVER[’REMOTE_ADDR’]

Question: What is the difference between require and include?

Answer: When using require function to embed another file in php, it will give fatal error if the file is not exists.
When using include function to embed another file in php, it will give warning if the file is not exists.


Question: How to find the number of elements in an array?

Answer: Using count($array) or sizeof($array).

Question: How do you make one way encryption for your passwords in PHP?

Answer: Using md5 function or sha1 function

Question: How do you get ASCII value of a character?

Answer: By using ord function.

Generating CAPTCHA Image Using PHP

The CAPTCHA concept is very useful to prevent automated registration. If you have enabled gd library, you can create a captcha code for your registration form using PHP.

Consider the following parts of code, name the file as "captcha.php"

session_start();

if(isset($_SESSION[‘captcha’]))
{
unset($_SESSION[‘captcha’]);
}

The above code will start session and clear the old captcha’s session value if it set.

$num_chars = 5; //number of characters for captcha image
$characters = array_merge(range(0,9),range(‘A’,‘Z’),range(‘a’,‘z’)); //creating combination of numbers & alphabets
shuffle($characters); //shuffling the characters

The above part of code describes the number of captcha characters and total available characters, here I am using all the lower and upper case alphabets and all numerics.
//getting the required random 5 characters
$captcha_text = "";
for($i=0;$i<$num_chars;$i++)
{
$captcha_text .= $characters[rand(0,count($characters)-1)];
}

$_SESSION[‘captcha’] = $captcha_text; // assigning the text into session

This part of code generated the required captcha code in a random manner from the available character array, also It assigns the value to session variable.

header("Content-type: image/png"); // setting the content type as png
$captcha_image = imagecreatetruecolor(140, 30);

$captcha_background = imagecolorallocate($captcha_image, 225, 238, 221); //setting captcha background colour
$captcha_text_colour = imagecolorallocate($captcha_image, 58, 94, 47); //setting cpatcha text colour

imagefilledrectangle($captcha_image, 0, 0, 140, 29, $captcha_background); //creating the rectangle

$font = ‘Arial.ttf’; //setting the font path

imagettftext($captcha_image, 20, 0, 11, 21, $captcha_text_colour, $font, $captcha_text);
imagepng($captcha_image);
imagedestroy($captcha_image);

The remaining code will draw the image.

How to use this captcha?

It’s simple, in your registration form put this part of code:



Then you can put a text box to enter the captcha value, and then you can compare the entered captcha value with the assigned captcha session value.

File Uploading in PHP

File uploading is very simple in PHP when comparing to other server side programming languages. File uploading is very useful in most of the situations like uploading user’s photos, uploading csv reports, uploading pdf reports and so on.

Making a simple front end with file browsing box:

1.

2.

3.
Upload a file
4.

5.

6.

Note: You should have this enctype=”multipart/form-data”‘ in your form tag while uploading files.
The php source code to upload the files

1.

2.
if(isset($_FILES[‘my_file’][‘name’]) && $_FILES[‘my_file’][‘name’] != ”)
3.
{
4.
$upload_dir = ‘home/myaccount/public_html/images’;//uploading directory, this folder should have write permission to the current user
5.
$upload_file = $upload_dir.‘/’.basename($_FILES[‘my_file’][‘name’]);
6.

7.
//check if the file name already exists
8.
if(file_exists($upload_file))
9.
{
10.
echo "File Already Exists"; //if file already exists print the error message
11.
}
12.
else
13.
{
14.
if(move_uploaded_file($_FILES[‘my_file’][‘tmp_name’],$upload_file))
15.
{
16.
echo "File Uploaded";
17.
}
18.
else
19.
{
20.
echo "There was a problem to upload file";
21.
}
22.
}
23.
}
24.
?>