Executing Stored Procedures and Functions From PHP in Windows (cont’d)

Calling Stored Procedures from PHP
To call MySQL stored procedures and functions from PHP, you need the following database extensions:

• MySQL
• MySQLi
• PDO

After installing those extensions, you’ll be able to call MySQL stored procedures and functions from PHP. As mentioned earlier, stored procedures and functions in MySQL are associated with a specific database. The examples in this section use a books database created using this SQL statement:

create table bookstore
(id int not null auto_increment primary key,
book varchar(50),
author varchar(50),
isbn varchar(50),
price int);

The SQL statements used to populate the bookstore table from Figure 1 are:

INSERT INTO bookstore (id,book,author,isbn,price) VALUES
(1,”Introduction to PHP”,”Mark User”,”3334-4424-334-3433″,500)
INSERT INTO bookstore (id,book,author,isbn,price) VALUES
(2,”DHTML and CSS”,”Teague Sanders”,”4545-23-23-23-23232″,1500)
INSERT INTO bookstore (id,book,author,isbn,price) VALUES
(3,”Introduction to PHP”,”Weeling Tom”,”4334-2323-23233-434″,300)
INSERT INTO bookstore (id,book,author,isbn,price) VALUES
(4,” Web design”,” Weeling Tom”,” 4334-2323-23233-434″,600)
INSERT INTO bookstore (id,book,author,isbn,price) VALUES
(5,” PHP 5″,” Weeling Tom”,” 444-87-67665-678678″,600)
INSERT INTO bookstore (id,book,author,isbn,price) VALUES
(6,” JavaServer Pages”,” Tick Own”,” 897-9898-987-099″,800)

Figure 1. Bookstore Table: The figure shows the table contents and structure from the books database

Figure 1 shows the table bookstore structure and some sample content.
Call Stored Procedures Using the MySQL Database Extension

The MySQL database extension gives you access to the MySQL database server. You install the php_mysql.dll like any other extension. You can find more information about the MySQL functions here.

First, you need a simple stored procedure. This one, called proc, selects all the fields in the bookstore table created earlier.

CREATE PROCEDURE proc ( )
BEGIN
SELECT * from bookstore;
END

The following PHP script connects to the MySQL server, selects the books database, calls the proc stored procedure, which has no arguments, and outputs the result:

<?php
//Create the connecting to MySQL
$con = mysql_connect('localhost','root','',false,65536);
mysql_select_db('books');

//Call the proc() procedure
$result= mysql_query("CALL proc();")
or die(mysql_error());

//Output the result
while($row = mysql_fetch_row($result))
{
for($i=0;$i<=6;$i++){
echo $row[$i]."
“;
}
echo “—”;
}
//Close the connection
mysql_close($con);
?>

Author’s Note: Using the syntax $con = mysql_connect(‘localhost’,'root’,”); will not work, because to return a result set from a stored procedure to PHP, you must use either the multiple-statements connect option or the multiple-results option (or both). If the routine does not return a result set, neither option is required.

The output is:

1—Introduction to PHP—Mark User—3334-4424-334-3433—500——–
2—DHTML and CSS—Teague Sanders—4545-23-23-23-23232—1500——-
3—Introduction to PHP—Weeling Tom—4334-2323-23233-434—300—–
4—Web design—Weeling Tom—4334-2323-23233-434—600———
5—PHP 5—Weeling Tom—444-87-67665-678678—600———
6—JavaServer Pages—Tick Own—897-9898-987-099—800———

Here’s a procedure example, named total_price, calculates the total of the price field from the bookstore table. It uses an OUT parameter to hold the total:

CREATE PROCEDURE total_price ( OUT total int)
BEGIN
SELECT sum(price) into total from bookstore;
END

The following PHP script calls the total_price procedure and displays the result using the OUT parameter total, which is an int:

The output is:

The total price is = 4300

Calling Stored Functions Using the MySQL Extension

To illustrate making stored function calls here’s a simple stored function:

CREATE FUNCTION simple_operation (price int) RETURNS int(11)
RETURN price*1000

The simple_operation function takes an integer argument, makes a simple calculation and returns an integer.

The output is:

The total price is = 5000

Executing Stored Procedures and Functions From PHP in Windows

Discover how to call stored procedures and functions in MySQL from PHP using three database extensions: MySQL, MySQLi, and PDO.

Stored procedures and functions are a new feature of MySQL 5.0. A stored procedure is a pre-built procedure containing one or more SQL statements stored in the database server.

GET THE CODE

This article shows how to create a few basic stored procedure and function examples, and call MySQL stored procedures and functions from PHP with the help of some database extensions.

Advantages of Using Stored Procedures
Stored procedures can provide improved performance because they can be precompiled, and because the client needs to send only a name and required parameters to the server to run a stored procedure rather than having to send the entire procedure code. In addition, stored procedures provide these other advantages:

* They simplify complex operations by encapsulating processes into a single easy-to-use unit.
* They help avoid errors because you can use a single well-tested stored procedure in many applications.
* A stored procedure runs the same way from all languages/environments. Because stored procedures reside on the database server, it makes no difference what application environment you use to call them—the stored procedure itself remains consistent.
* They reduce the risk of data damage by limiting access to the data.
* They can reduce network traffic. Complex, repetitive tasks may require getting some data, applying some logic to the retrieved values, and using the results to retrieve more data. When this multi-step process takes place completely on the database server, as in a stored procedure, it can eliminate the need to send result sets and new queries back and forth from to the database server.

Creating Stored Procedures in MySQL

MySQL 5.0 finally introduces functionality for stored procedures. In this implementation, each stored procedure or function is associated with a particular database, which has the following implications:

* When you call a stored procedure or function, the database issues an implicit USE db_name command, which remains in effect until the stored procedure terminates.
* You can create a stored procedure name for a given database name only if that name is unique in the current database. For example, to invoke a stored procedure named proc or function named func associated with the book database, you can write CALL book.proc() or CALL book.func().
* When you drop a database, MySQL drops all stored procedures and functions associated with that database as well.

Defining procedures or functions is a two-step process:

1. Define the name of the procedure or function, and set its parameters.
2. Define the body of the procedure or function between BEGIN and END statements.

Here’s the basic syntax:

CREATE PROCEDURE procedure_name ([procedure_parameter[,...]])
routine_body

The procedure_parameter is a list of parameters and their directions, composed using the following arguments:

* IN: Passes a value into a procedure. The procedure can modify the value, but the modification is not visible to the caller when the procedure returns.
* OUT: Passes a value from the procedure back to the caller. The parameter’s initial value in the procedure is NULL; the procedure usually changes that value, and the final value is visible to the caller when the procedure returns.
* INOUT: The caller initializes an INOUT parameter, but the procedure can modify the value, and the final value is visible to the caller when the procedure returns.

Calling a Stored Procedure in MySQL
Within MySQL, you call a stored procedure using the call method, for example:

call books.proc(@a);
select @a;

Creating Stored Functions in MySQL
There are a few key differences between creating a stored procedure and creating a function:

* The keyword function replaces the procedure keyword.
* You don’t need to specify parameter direction, because all parameters are IN.
* The RETURNS keyword after the parameter list specifies the return value type.
* You don’t need to use a BEGIN…END block.
* To call a function, use the syntax select function(parameter_list).

Here’s the function creation syntax:

CREATE FUNCTION function_name ([function_parameter[,...]])
RETURNS type
routine_body

Here’s a simple stored function example that calculates and returns an int:

CREATE FUNCTION simple_operation (price int) RETURNS int
RETURN price*1000

To call it from SQL Server, use:

SELECT simple_operation(5)

The return value in this case is 5000.

Download the SQL scripts and sample PHP code for this article.

Help Stop Cross-Site Scripting Attacks with HttpOnly Cookies

Did you know that there’s a simple little change you can make in the way you handle cookies that can help prevent your users from falling victim to a cross-site scripting attack? Implementing HttpOnly cookies is quick, easy, and goes a long way towards making your application safer for everyone.

HttpOnly cookies behave exactly like regular cookies with one important difference: they cannot be accessed by client-side script running in the user’s browser. This doesn’t seem like a big difference until you realize that many cross-site scripting exploits depend on this very capability.

As long as you’re running .NET 2.0 or higher, you can enable HttpOnly cookies in a couple different ways. The easiest is to simply edit your application’s Web.config file. Setting the value of the httpOnlyCookies attribute of the httpCookies element to true will convert all the cookies your application sends to the HttpOnly flavor.

You can also do the same thing for individual cookies that you set via code. It couldn’t be much easier as you can see in the following listing:

Dim myCookie As HttpCookie
myCookie = New HttpCookie(“LastVisit”, DateTime.Now.ToString())
myCookie.HttpOnly = True
Response.AppendCookie(myCookie)

Now for the bad news: HttpOnly cookies only work in relatively new browsers. Older browsers will either treat them as regular cookies or ignore them altogether. If you happen to have a user base which is particularly behind the times, you’ll need to do some testing to see how your application behaves in their browser(s) of choice.

For more information, you may find the following links useful:

Mitigating Cross-Site Scripting With HTTP-Only Cookies
ASP.NET Settings Schema: httpCookies Element
.NET Framework Class Library: HttpCookie.HttpOnly Property

Update: HttpOnly Cookies in ASP.NET 1.x and Classic ASP

I’ve gotten a number of email from users anxious to use HttpOnly cookies in their legacy Web projects. Rest assured, you can get the same HttpOnly functionality regardless of your server side tool of choice… it’s just takes a little more work.

For those of you using ASP.NET 1.x, try this code:

Dim myCookie As HttpCookie
myCookie = New HttpCookie(“LastVisit”, DateTime.Now.ToString())
myCookie.Path += “; HttpOnly”
Response.AppendCookie(myCookie)

It’s a little bit of a hack, but it should work in most cases. The only situation I can think of that might cause a problem is if your cookies are flagged as secure.

In classic ASP it’s a little more difficult. You can’t really use the Cookie object to accomplish the task, so you’ll need to resort to brute force and use the Response.AddHeader method to set the cookie.

Response.AddHeader “Set-Cookie”, “CookieName=CookieValue; path=/; HttpOnly”

As you can see, HttpOnly cookies aren’t just for developers lucky enough to be using the latest version of ASP.NET. With a few tweaks you can use you can use them with whichever server-side technology you prefer.

Visual Studio 2008 SP1 and .NET Framework 3.5 SP1

After a healthy run in beta, Microsoft has finally released both Visual Studio 2008 Service Pack 1 and .NET Framework 3.5 Service Pack 1.

The updates are available for download directly from Microsoft via the links below. If you’re curious what the updates provide, I’ve also included the brief description that Microsoft gave for each.

Visual Studio 2008 Service Pack (SP) 1

Visual Studio 2008 SP1 introduces full support for SQL Server 2008, improved performance in the IDE and WPF designers, improved Web development and site deployment, and many Team Foundation Server enhancements.

Microsoft .NET Framework 3.5 Service Pack (SP) 1

Microsoft .NET Framework 3.5 Service Pack 1 is a full cumulative update that contains many new features building incrementally upon .NET Framework 2.0, 3.0, 3.5, and includes cumulative servicing updates to the .NET Framework 2.0 and .NET Framework 3.0 subcomponents.

You can find additional information about each of the service packs on their respective download pages. So… what are you waiting for? Go get em!

HTML color codes and names

Quick links

About codes and colors and how to apply
Major hexadecimal color codes
Color Code Chart
HTML help

About color codes and how to apply

HTML color codes are hexadecimal triplets representing the colors red, green, and blue. For example, in the the color red, the color code is FF0000, which is ‘255′ red, ‘0′ green, and ‘0′ blue.

Complete information about how to apply HTML color codes using CSS, the tag and applying other font types can be found on document CH000072.

Click here to view the color code:

ASP Browser Capabilities Component

The ASP Browser Capabilities component creates a BrowserType object that determines the type, capabilities and version number of a visitor’s browser.

When a browser connects to a server, a User Agent header is also sent to the server. This header contains information about the browser.

The BrowserType object compares the information in the header with information in a file on the server called “Browscap.ini”.

If there is a match between the browser type and version number in the header and the information in the “Browsercap.ini” file, the BrowserType object can be used to list the properties of the matching browser. If there is no match for the browser type and version number in the Browscap.ini file, it will set every property to “UNKNOWN”.

ASP Browser Capabilities Example

The example below creates a BrowserType object in an ASP file, and displays some of the capabilities of your browser:

Example & Syntax

Aadhavan Trailer

This is something never expected from the great music director Harris Jayaraj. The recent audio release of ‘Aadhavan’ turned spotlights of entire town to heed for the melodies. However, soon after hearing, the masses were enraged as Harris Jayaraj had loosely inserted his old own compositions. It’s more illustrious with ‘Hasiliye Fisiliye’ that is nothing but ‘Ada Gada’ from Satyam and ‘Oh Mohalai’ from ‘Arasaaktchi’. ‘Vaarayo’ reminds us off nothing but ‘Un Sirpinil’ from Pacchaikili Muthucharam and ‘Nenje Nenje’ from Ayan.

Earlier, Harris Jayaraj used to add the words from Christian prayer songs…. The exemplification lies with ‘Mudhal Naal Indru’ from ‘Unnale Unnale’ where you will hear ‘Hosanna…Hosanna’. Likewise, the song ‘Oru Maalai’ from ‘Ghajini’ has ‘Alleluia’…

Well, the ‘Minnale’ song – Vaseegara was nothing but a strong base of A.R. Rahman’s ‘Pudhu Vellai Malai’ as we hear the instrumental on flute.

Watch the Aadhavan Trailer.

ASP.NET version of “Name Tag”

This sample shows you how to load a background image from a file, add some text to the image using ASP.NET’s graphics capabilities, and serve the resulting image to a browser.

As an example we’ll be using an image of one of those “Hello My Name Is” name tag stickers that we’ve all seen at conferences and get togethers. We’ll load the image, add a name to the tag and send the image to the browser. The name will be pulled from the QueryString so it can easily be changed.

Here’s a zip file of the code along with the sample background image (13 KB).

Here are a couple of links to the script that pass in different names for reference:

John
Fred Q. Smith

Play with the running version.

View the live source code.

Read-Only Session State

The fact that ASP.NET maintains a user’s session state for us is a great thing. It allows us to program applications for the Web and rarely give a second thought to the fact that we don’t actually maintain a connection to our users. That being said, session state does come at a cost and disabling it when you’re not using it is a standard tip for improving application performance. But what if you are using it?

There’s a little known option that can serve as a happy medium. Instead of setting your page’s EnableSessionState attribute to “True” or “False”, you can set it to “ReadOnly” instead. The resulting page directive should look something like this:

This only works on pages that need access to a user’s session state information, but do not modify it. If you take a close look at your application, you’ll probably find that the majority of the pages that use session state fall into this category.

Using this setting won’t give you the same performance benefit you’d get by disabling session state altogether, but then again you don’t have to give up using sessions in order to use it.

Instantly Find Sub and Function Declarations

Trying to quickly make sense of unfamiliar code can be quite frustrating. This tip will show you how you can instantly jump from the usage of any subroutine directly to its definition using any flavor of Visual Studio. This huge time saver lets you easily locate code that you might otherwise have spent hours trying to find.

Assuming you’ve already opened the Web site or application with Visual Studio, the first step is to find a usage of the subroutine whose definition you’re trying to find. Then simply place your cursor on the Sub or Function name and press the “F12″ key on your keyboard. The code window will instantly move you to the declaration of the algorithm in question. This works even if the source code is located in a different file altogether.

Once you’ve examined the code or made any changes you may need to, you’ll most likely want to return to your original location. Luckily Visual Studio provides a simple shortcut for this as well. Use the “Ctrl+-” keyboard combination and you’ll end up right back where you started.

Jumping around unfamiliar code this way can take a little getting used to, but once you get used to using “GoToDefinition” (“F12″) and “NavigateBackward” (“Ctrl+-”), you’ll wonder how you ever got along without them.