procedure with mySQL and PHP

Using Stored procedure with mySQL and PHP

Writing external scripts to perform complex data handling is a tedious affair. The best way to automate tasks straightaway into the server is by using Stored Procedures. It is very useful to make them as flexible as possible, as it facilitates easy identification of any errors and can be used for executing a variety of tasks as well.
What are Stored Procedures?

Stored procedures are set of SQL commands that are stored in the database data server. After the storing of the commands is done, the tasks can be performed or executed continuously, without being repeatedly sent to the server. This also helps in decreasing the traffic in the networks and also reduces the CPU load.
There are many advantages of using stored procedures, which include:

* The functionality is application and platform related.
* Functionality has to be developed only once, and all applications can call the same commands.
* Task execution becomes easier and less complicated.
* Network Traffic reduced to a greater extent.
* Centralization of all commands made possible, which is helpful for various applications that repeatedly call the same set of complicated commands.
* Runs on any kind of environment.

MySQL Stored Procedures

For few years, Oracle and Microsoft SQL servers were having one upper hand over MySQL by having the facility to use the advantage of Stored Procedures. But this advantage has become a thing of the past now. With MySQL 5, you can use Stored Procedures the way you have been utilizing with other servers.

The syntax for using Stored Procedures is as follows:
CREATE

[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type

func_parameter:
param_name type

type:
Any valid MySQL data type

characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

routine_body:
Valid SQL procedure statement


Application

MySQL Stored Procedures can be applied in absolutely any place. Right from complex applications to simple procedures, these stored procedures can be utilized in absolutely any place.
Few of the many places that MySQL Stored procedures can be used are:

* When diverse client applications are structured using various languages in different platforms.
* When security is of highest importance, like in financial institutions, the users and applications would have no direct access to the database tables. This provides excellent secured environment.
* When very few database servers service the client machines, thereby providing efficient performance.

Though not as mature as Oracle, DB2 or the SQL Server, the MySQL Stored Procedures is definitely worth a try. If the structure of the database is the same, the same stored procedures can be used for all.
A simple example for MySQL Stored Procedure

To calculate the area of a circle with given radius R, the following commands can be given
delimiter //
create function Area (R double) returns double
deterministic
begin
declare A double;
set A = R * R * pi();
return A;
end
//
delimiter ;

And to call it from php code to display the area of a circle with radius 22cm,

$rs_area = mysql_query(“select Area(22)”);
$area = mysql_result($rs_area,0,0);
echo “The area of the circle with radius 22cm is ”.$area.” sq.cm”;

How to Integrate Smarty Template to Code Igniter and to use as a View

Introduction

In this tutorial I will show you how to integrate Smarty Template in to Code Igniter and used it as view.

Requirements

1. Code Igniter

2. Smarty Template

Installation and Instructions

1. Download, unzip and install the Code Igniter Framework.

2. Download and unzip the Smarty Template

2.1 Locate the ‘libs’ directory in your Smarty directory ‘Smarty-*.*.**’

2.2 Copy the ‘libs’ directory to Code Igniter installation

[code igniter installation]/system/application/libraries/

2.3 Create a directory 'smarty' in Code Igniter

[code igniter installation]/system/application/libraries/

2.4 Rename the 'libs' to 'libs_*.*.**' (Ex. libs_2.6.22) *.*.** is the Smarty version

and place it to [code igniter installation]/system/application/libraries/smarty/

You can easily manage your Smarty version using the code below.

require "smarty/libs_2.6.22/Smarty.class.php";

3. Create the file below and name it to 'mysmarty.php' and place it to

[code igniter installation]/system/application/libraries/

File: [code igniter installation]/system/application/libraries/mysmarty.php

 ?php if (!defined('BASEPATH')) exit('No direct script access allowed');

 require "smarty/libs_2.6.22/Smarty.class.php";

 class Mysmarty extends Smarty
 {
  public function __construct ( )
  {
   parent::Smarty( );

   $config =& get_config( );   

   $this->left_delimiter =  '{{';
   $this->right_delimiter =  '}}';   

   // absolute path prevents "template not found" errors
   $this->template_dir = (!empty($config['smarty_template_dir']) ? $config['smarty_template_dir'] : BASEPATH . 'application/views/');                  

   $this->compile_dir  = (!empty($config['smarty_compile_dir']) ? $config['compile_dir'] : BASEPATH . 'cache/'); //use CI's cache folder
  }

  public function view ( $resource_name, $cache_id = null )
  {
   if ( strpos($resource_name, '.') === false )
    $resource_name .= '.tpl';

   return parent::display( $resource_name, $cache_id );
  }
 }

?

4. Edit your 'autoload.php' and autoload your 'mysmarty'

[code igniter installation]/system/application/config/autoload.php

See the code below

$autoload['libraries'] = array( 'database', 'session', 'mysmarty' );

IMPORTANT: If you are using 'mysmarty' in other library, it must be loaded first prior to your other plugin.

Correct:

$autoload['libraries'] = array( 'database', 'session', 'mysmarty', 'otherlibrary' );

Congratulation! You have successfully integrated the Smarty Template to your Code Igniter Framework. Now, the next step is how to use Smarty to your template.

Your templates must reside in

[code igniter installation]/system/application/views/

View:

[code igniter installation]/system/application/views/home.tpl

....
< body >
     {{* display value *}}
     {{$my_template_variable}}
< /body >
....

Controller:

[code igniter installation]/system/application/controller/home.php

 ?php  if (!defined('BASEPATH')) exit('No direct script access allowed');

 class Home extends Controller
 {
  function __construct ( )
  {
   parent::Controller( );
  }  

  public function index ( )
  {
   // set value ...
            $this->assign( 'my_template_variable', 'This is the value.' );

   // call the template ...
            $this->mysmarty->view( 'home' );
  }
 }

?

Now, try it! don't forget to leave comment ...

How to connect MSSQL with php

Please follow the steps given below to connect MSSQL with php:
1. Settings related to your php.ini file:
a) search the variable mssql.secure_connection in your php.ini file and put it to on mode if its off
b) remove comment from the dll extention php_mssql.dll (i.e. remove the ; from the front of the extention )
2. Settings related to the dll files.
download a file name ntwdblib.dll from the internet. you can download it from here or can search on internet for that. copy the downloaded dll to the apache/bin directory and for IIS copy it to the php extention directory (if path not known can be found in php.ini for variable extension_dir)
also you need to have your php_mssql.dll in your php extension directory. if its not present please download it and copy it to the default php extension directory.
3. restart all your services (i.e. php and apache or iis) and you can use the script given below to connect to your SQL Server.


Put in php script tags

//mssql.secure_connection = On
// Need to upload ntwdblib.dll from net
$myServer = “DVLP066\SQLEXPRESS”; // host/instance_name
$myUser = “sa”; // username
$myPass = “sadvlp066″; // paasword
$myDB = “myfraiche”; // database name
// connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
or die(“Couldn’t connect to SQL Server on $myServer”);
// select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
or die(“Couldn’t open database $myDB”);
echo “You are connected to the ” . $myDB . ” database on the ” . $myServer . “.”;
$query = “SELECT top 10 * FROM tbl_customers”; // your database query
$result = mssql_query($query);
while($row = mssql_fetch_assoc($result))
{
print_r($row);
}
// close the connection
mssql_close($dbhandle);

Retrieving XML With Curl and SimpleXML

Introduction

PHP 5 introduces SimpleXML and its a perfect name as parsing XML data is truly simple. In this tutorial we'll be using curl to retrieve the XML data from a remote web server. We're going to create a class to connect to the remote web server and pass POST data to the server and based on the POST data the remote server will return valid XML. The class will parse the XML response and return an array containing the data. For the sake of simplicity in this tutorial we're not going into detail on the workings of the remote server generating the XML responses.

The first thing we're going to cover is the simple script used to call the class.

$URL = 'http://www.example.com/XML';
$request = 'getInventory';
$parameters = array("param1" => "value1", "param2" => "value2");

$XMLClass = new getXML;
$response = $XMLClass->pullXML($URL,$request,$parameters);
 ?>

This simple script is all that is needed to use the class. We need to set the URL, request, and attributes being passed for the request. The $XMLClass = new getXML initializes the getXML class discussed later on in the tutorial. The response data of the request is stored in $response. For this example we're going to use the following XML.
       
               
               
       
 ?>
var_dump($response) would be
array(2) { [0]=> array(2) { ["attribA"]=> string(6) "valueA" ["attribB"]=> string(6) "valueB" } [1]=> array(2) { ["attribA"]=> string(6) "valueC" ["attribB"]=> string(6) "valueD" } } 

Retrieving Data

Now here is the meat, building a simple curl class to handle the retrieving of the data. More information on using curl in php.

Class getData {
   public $URL;
   public $XMLRequest;
   public $XMLResponseRaw;
   public $parameters;
   public $XPath;

   function buildCurlParamString() {
       $urlstring = '';

       foreach ($this->parameters as $key => $value) {
           $urlstring .= urlencode($key).'='.urlencode($value).'&';
       }

       if (trim($urlstring) != '') {
           $urlstring = preg_replace("/&$/", "", $urlstring);
           return ($urlstring);
       } else {
           return (-1);
       }
   }

 ?>

In this first part we declared a few variables for use in the class. Our first function buildCurlParamString() takes the class variable parameters, which is an associated array with the key being the variable name and the value being the variable value for the request, and turns them into the query string for our POST request to the remote server. We'll set the parameters array later just know it stores the names and values of the fields to post. We loop through the arrays urlencoding the data. The urlencode function formats our data properly for transmission to the server hosting the XML feed.

Then we trim off our trailing & from the previous loop. If for any reason we aren't returning a valid urlencoded string we return -1 out of buildCurlParamString(). 

Using curl

   function curlRequest() {
       $urlstring=$this->buildCurlParamString();

       if ($urlstring==-1) {
           echo "Couldn't Build Parameter String
"."n";
           return(-1);
       }
               
       $ch=curl_init();
       curl_setopt($ch, CURLOPT_URL, $this->URL.$this->XMLRequest);
       curl_setopt($ch, CURLOPT_TIMEOUT, 180);
       curl_setopt($ch, CURLOPT_HEADER, 0);
       curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
       curl_setopt($ch, CURLOPT_POST, 1);
       curl_setopt($ch, CURLOPT_POSTFIELDS, $urlstring);
       $data=curl_exec($ch);
       curl_close($ch);

       return($data);
   }
 ?>

This function handles the actual pulling of the data from the remote server with curl. We start by calling the buildCurlParamString() function and getting our POST data ready. Then we use curl_init() to initialize the curl handler and curl_setopt() to prepare curl. Notice we're using the class variables $this->URL and $this->XMLRequest, those will be set when we initialize our class and retrieve data. The data is pulled and returned to the calling function.

   function getFeed() {
       $rawData=$this->curlRequest();

       if ($rawData!=-1) {
           $this->XMLResponseRaw=$rawData;
       }
   }
 ?>

The last function in the curl class is the function that will get called from outside this class. Notice it stores the data in a class variable, that's because we're going to extend this class with our SimpleXML class. 

Finally SimpleXML

Class getXML extends getData {


   function pullXML($URL, $request, $parameters) {
       $this->URL = $URL;
       $this->XMLRequest = $request;
       $this->parameters = $parameters;
       $this->getFeed();
       $this->simpleXML = simplexml_load_string($this->XMLResponseRaw);
       return ($this->parseXPath($this->simpleXML));
   }
 ?>

This is the function that will be called externally to get the data in your scripts using this code. The function pullXML is where you pass the URL of the site, the request, and the arguments for the request. We return the value of our next function parseXPath. ParseXPath is used to parse any Xpath statements we might want to filter our results with.

   function parseXPath() {
       if ($this->XPath!='') {
           $this->XMLXPath=$this->simpleXML->xpath($this->XPath);
           $a=0;
           if (isset($this->XMLXPath[$a])) {
               $XMLParse = parseSimpleXMLData($this->XMLXPath);
           } else {
               $XMLParse=-1;
           }
           return($XMLParse);
       } else {
           $XMLParse = parseSimpleXMLData($this->simpleXML->DATA);
       }
       if (isset($XMLParse)) {
           return($XMLParse);
       } else {
           return(-1);
       }
   }

   function parseSimpleXMLData($data) {
       $i=0;
       while (isset($data[$i])) {
           foreach($data[$i]->attributes() as $attrib => $value) {
               $XMLParse[$a][$attrib]=$value;
           }
           $i++;
       }

       return($XMLParse);
   }
 ?>

In parseXPath we first check if there is a valid simpleXML resource, if the resource is invalid $this->simpleXML will equal FALSE so we return FALSE on this. Then if an Xpath is set we execute the xpath function of simplexml on our simpleXML handle with the $this->simpleXML->xpath($this->XPath). This lets simplexml handle the work of filtering our dataset. Now we pass the data into parseSimpleXMLData which parses through each row of our dataset with the while loop.

You'll notice that as you work your way through a simpleXML resource the nesting uses the same names as the tags. For lists where each record has the same tag an indexed array is created.

The foreach Loop takes the attributes of the row and stores them in the $XMLParse array. If there aren't any rows in the dataset we set $XMLParse to -1. The array $XMLParse is what is returned from the function. If there isn't a Xpath statement we do the same thing without processing an Xpath statement. 

SimpleXML Wrapup

simplexml_load_string($string) -- loads XML from a string variable
simplexml_load_file($filename) -- loads XML from a file specified

Once the data is loaded node names are stored as pseudo class variables and can be accessed via $simpleXML->child.

$simpleXML->xpath("XPath Statement") -- parses Xpath statements by calling the pseudo class function xpath on the simpleXML element.
$simpleXML->children() -- returns the names of child nodes
$simpleXML->attributes() -- returns the attributes for a node applied to

PHP Email: Protecting Email Address from Spam Collectors

PHP Email Step By Step Tutorial: Today, many spammers use software that scours websites looking for email address on pages. When found, they will send spam to these addresses. Several way may be can used such as add the text "NOSPAM" into email. In this post, we will use JavaScript as tactic. We convert the entire link into ASCII codes, save them in a JavaScript array, and then use JavaScript to turn it back into HTML.

 Create a file named "protected.php" within www/test/email. Enter above code. And test it:

 When you look source, you will get like this:

PHP Email: Sending Mass Email use BCC

Send Mass PHP Email Tutorial: May be you need to send out an email to many people (such as newsletter or birthday invitation). It is not efficient if we use multiple mail() for this job. Each call to mail() generates a separate call to sendmail on UNIX or a separate connection to an SMTP server on Windows.

May we don't want to display all email address at 'to'. We can modify by using dummy email address, like mailing-list@example.com. Then include everyone else as a Bcc onto this message. The mail server will handle this Bcc, parsing it and ensuring that everyone on the list receives a copy.

PHP Email: Using Embedded Images in HTML Email

PHP Email Tutorial: We ever talk how about send email with attachment. Now, in this post, we want to send email where there is images in there as embedded images. Usually, you get this kind of email from newsletter. So, after read this post, you can build your own newsletter.

Before we write line codes, we must understand following anatomy of email that use embedded images:

01

<?php

02	// Setting a timezone, mail() uses this.

03	date_default_timezone_set('America/New_York');

04	// recipients

05	$to  = "you@miscellaneous4all.blogspot.com" . ", " ; // note the comma

06	$to .= "we@miscellaneous4all.blogspot.com";

07

08	// subject

09	$subject = "Test for Embedded Image & Attachement";

10

11	// Create a boundary string.  It needs to be unique

12	$sep = sha1(date('r', time()));

13

14	// Add in our content boundary, and mime type specification:

15	$headers .=

16	"\r\nContent-Type: multipart/mixed;

17	boundary=\"PHP-mixed-{$sep}\"";

18

19	// Read in our file attachment

20	$attachment = file_get_contents('attachment.zip');

21	$encoded = base64_encode($attachment);

22	$attached = chunk_split($encoded);

23

24	// additional headers

25	$headers .= "To: You <you@miscellaneous4all.blogspot.com>,

26	We <we@miscellaneous4all.blogspot.com>\r\n";

27	$headers .= "From: Me <me@miscellaneous4all.blogspot.com>\r\n";

28	$headers .= "Cc: he@miscellaneous4all.blogspot.com\r\n";

29	$headers .= "Bcc: she@miscellaneous4all.blogspot.com\r\n";

30

31	$inline = chunk_split(base64_encode(

32	file_get_contents('mypicture.gif')));

33

34	// Your message here:

35	$body =<<<EOBODY

36	--PHP-mixed-{$sep}

37	Content-Type: multipart/alternative;

38	boundary="PHP-alt-{$sep}"

39

40	--PHP-alt-{$sep}

41	Content-Type: text/plain

42

43	Hai, It's me!

44

45

46	--PHP-alt-{$sep}

47	Content-Type: multipart/related; boundary="PHP-related-{$sep}"

48

49	--PHP-alt-{$sep}

50	Content-Type: text/html

51

52

<html>

53

<head>

54	<title>Test HTML Mail</title>

55

</head>

56

<body>

57	<font color='red'>Hai, it is me!</font>

58	Here is my picture:

59	<img src="cid:PHP-CID-{$sep}" />

60

</body>

61

</html>

62

63	--PHP-related-{$sep}

64	Content-Type: image/gif

65	Content-Transfer-Encoding: base64

66	Content-ID: <PHP-CID-{$sep}>

67

68

{$inline}

69	--PHP-related-{$sep}--

70

71	--PHP-alt-{$sep}--

72

73	--PHP-mixed-{$sep}

74	Content-Type: application/zip; name="attachment.zip"

75	Content-Transfer-Encoding: base64

76	Content-Disposition: attachment

77

78	{$attached}

79

80	--PHP-mixed-{$sep}--

81

EOBODY;

82

83	// Finally, send the email

84	mail($to, $subject, $body, $headers);

85

?>

86	</me@miscellaneous4all.blogspot.com>

PHP-Email: Send Attachement Mail

After learn send email with dual format (HTML/text), we will learn how to send email with attachment. In this situation, we will make the primary MIME type be multipart/mixed so that we can do an attachment or two.

01

<?php

02	// Setting a timezone, mail() uses this.

03	date_default_timezone_set('America/New_York');

04	// recipients

05	$to  = "you@miscellaneous4all.com" . ", " ; // note the comma

06	$to .= "we@miscellaneous4all.com";

07

08	// subject

09	$subject = "Test for Attachement";

10

11	// Create a boundary string.  It needs to be unique

12	$sep = sha1(date('r', time()));

13

14	// Add in our content boundary,

15	// and mime type specification:

16	$headers .=

17	"\r\nContent-Type: multipart/alternative;

18	boundary=\"PHP-alt-{$sep}\"";

19

20	// Read in our file attachment

21	$attachment = file_get_contents('attachment.zip');

22	$encoded = base64_encode($attachment);

23	$attached = chunk_split($encoded);

24

25	// additional headers

26	$headers .= "To: You <you@miscellaneous4all.com>,

27	We <we@miscellaneous4all.com>\r\n";

28	$headers .= "From: Me <me@miscellaneous4all.com>\r\n";

29	$headers .= "Cc: he@miscellaneous4all.com\r\n";

30	$headers .= "Bcc: she@miscellaneous4all.com\r\n";

31

32	// Your message here:

33	$body =<<<EOBODY

34	--PHP-alt-{$sep}

35	Content-Type: text/plain

36

37	Hai, It's me!

38

39	--PHP-alt-{$sep}

40	Content-Type: text/html

41

42

<html>

43

<head>

44	<title>Test HTML Mail</title>

45

</head>

46

<body>

47	<font color='red'>Hai, it is me!</font>

48

</body>

49

</html>

50

51	--PHP-alt-{$sep}--

52

53	--PHP-mixed-{$sep}

54	Content-Type: application/zip; name="attachment.zip"

55	Content-Transfer-Encoding: base64

56	Content-Disposition: attachment

57

58	{$attached}

59

60	--PHP-mixed-{$sep}--

61

EOBODY;

62

63	// Finally, send the email

64	mail($to, $subject, $body, $headers);

65

?>

66	</me@miscellaneous4all.com>

PHP-Email: Sending Dual Format (Part 3)

When we send only html email, it still have problems. How if someone is using a mail client that cannot understand HTML email. To solve this problems, it is best to send any HTML email as dual format. This means providing both a text and an HTML version in the same email. Your client can choose which version to display.

01

<?php

02	// Setting a timezone, mail() uses this.

03	date_default_timezone_set('America/New_York');

04	// recipients

05	$to  = "you@phpeveryday.com" . ", " ; // note the comma

06	$to .= "we@phpeveryday.com";

07

08	// subject

09	$subject = "Test for HTML Format";

10

11	// Create a boundary string.  It needs to be unique

12	$sep = sha1(date('r', time()));

13

14	// Add in our content boundary, and mime type

15	//specification:

16	$headers .=

17	"\r\nContent-Type: multipart/alternative;

18	boundary=\"PHP-alt-{$sep}\"";

19

20	// additional headers

21	$headers .= "To: You <you@phpeveryday.com>,

22	We <we@phpeveryday.com>\r\n";

23	$headers .= "From: Me <me@miscellaneous4all.com>\r\n";

24	$headers .= "Cc: he@miscellaneous4all.com\r\n";

25	$headers .= "Bcc: she@miscellaneous4all.com\r\n";

26

27	// Your message here:

28	$body =<<<EOBODY

29	--PHP-alt-{$sep}

30	Content-Type: text/plain

31

32	Hai, It's me!

33

34	--PHP-alt-{$sep}

35	Content-Type: text/html

36

37

<html>

38

<head>

39	<title>Test HTML Mail</title>

40

</head>

41

<body>

42	<font color='red'>Hai, it is me!</font>

43

</body>

44

</html>

45

46	--PHP-alt-{$sep}--

47

EOBODY;

48

49	// Finally, send the email

50	mail($to, $subject, $body, $headers);

51

?>

52	</me@miscellaneous4all.com>

PHP-Email: Sending a Simple HTML Mail (Part 2)

May be you ever get advertise email. They send you a html email. The layout is very nice. There are many tables, coloring font, and soon. Yeah, they use html mail. They just set the body of the email to have HTML in it and add one additional header of Content-type: text/html and have it work. Ok, look the code:

02	// Setting a timezone, mail() uses this.

03	date_default_timezone_set('America/New_York');

04	// recipients

05	$to  = "you@miscellaneous4all.com" . ", " ; // note the comma

06	$to .= "we@miscellaneous4all.com";

07

08	// subject

09	$subject = "Test for HTML Format";

10

11	// To send HTML mail, you can set the Content-type header.

12	$headers  = "MIME-Version: 1.0\r\n";

13	$headers .= "Content-type: text/html; charset=iso-8859-1\r\n";

14

15	// additional headers

16	$headers .= "To: You ,

17	We miscellaneous4all.com>\r\n";

18	$headers .= "From: Me miscellaneous4all.com>\r\n";

19	$headers .= "Cc: he@miscellaneous4all.com\r\n";

20	$headers .= "Bcc: she@miscellaneous4all.com\r\n";

21

22	// Your message here:

23

$body = "

24

25

26

27

28

29

'red'>Hai

30

31

32

";

33

34	// Finally, send the email

35	mail($to, $subject, $body, $headers);

36

?>

PHP-Email: Sending a Simple Email (Part 1)

This is a part of tutorial series about sending email in PHP. We know, many different type of email get sent. Email can contains file attachments, inline images, dual format (HTML/text), and so on. We begin with simple email. It use basic function, mail().