php guruji

PHP and MySQL are usually associated with LAMP (Linux, Apache, MySQL, PHP). However, most PHP developer ( including me ) are actually using Windows when developing the PHP application. So this page will only cover the WAMP ( Windows, Apache, MySQL, PHP ). You will learn how to install Apache, PHP, and MySQL under Windows platform.



The first step is to download the packages :

* Apache : www.apache.org
* PHP : www.php.net
* MySQL : www.mysql.com

You should get the latest version of each packages. As for the example in this tutorial i'm using Apache 2.0.50 ( apache_2.0.50-win32-x86-no_ssl.msi ), PHP 4.3.10 ( php-4.3.10-Win32.zip ) and MySQL 4.0.18 ( mysql-4.0.18-win.zip ).

Now let's start the installation process one by one.



Installing Apache

Installing apache is easy if you download the Microsoft Installer ( .msi ) package. Just double click on the icon to run the installation wizard. Click next until you see the Server Information window. You can enter localhost for both the Network Domain and Server Name. As for the administrator's email address you can enter anything you want.

I'm using Windows XP and installed Apache as Service so everytime I start Windows Apache is automatically started.

Install Apache PHP MySQL - Enter Apache server information

Click the Next button and choose Typical installation. Click Next one more time and choose where you want to install Apache ( I installed it in the default location C:\Program Files\Apache Group ). Click the Next button and then the Install button to complete the installation process.

To see if you Apache installation was successful open up you browser and type http://localhost in the address bar. You should see something like this :

Install Apache PHP MySQL - Testing Apache Installation ( thumbnail created using PHP graphics library )



By default Apache's document root is set to htdocs directory. The document root is where you must put all your PHP or HTML files so it will be process by Apache ( and can be seen through a web browser ). Of course you can change it to point to any directory you want. The configuration file for Apache is stored in C:\Program Files\Apache Group\Apache2\conf\httpd.conf ( assuming you installed Apache in C:\Program Files\Apache Group ) . It's just a plain text file so you can use Notepad to edit it.

For example, if you want to put all your PHP or HTML files in C:\www just find this line in the httpd.conf :

DocumentRoot "C:/Program Files/Apache Group/Apache2/htdocs"

and change it to :

DocumentRoot "C:/www"

After making changes to the configuration file you have to restart Apache ( Start > Programs > Apache HTTP Server 2.0.50 > Control Apache Server > Restart ) to see the effect.

Another configuration you may want to change is the directory index. This is the file that Apache will show when you request a directory. As an example if you type http://www.php-mysql-tutorial.com/ without specifying any file the index.php file will be automatically shown.

Suppose you want apache to use index.html, index.php or main.php as the directory index you can modify the DirectoryIndex value like this :

DirectoryIndex index.html index.php main.php

Now whenever you request a directory such as http://localhost/ Apache will try to find the index.html file or if it's not found Apache will use index.php. In case index.php is also not found then main.php will be used.


Installing PHP

First, extract the PHP package ( php-4.3.10-Win32.zip ). I extracted the package in the directory where Apache was installed ( C:\Program Files\Apache Group\Apache2 ). Change the new created directory name to php ( just to make it shorter ). Then copy the file php.ini-dist in PHP directory to you windows directory ( C:\Windows or C:\Winnt depends on where you installed Windows ) and rename the file to php.ini. This is the PHP configuration file and we'll take a look what's in it later on.

Next, move the php4ts.dll file from the newly created php directory into the sapi subdirectory. Quoting from php installation file you can also place php4ts.dll in other places such as :

* In the directory where apache.exe is start from ( C:\Program Files\Apache Group\Apache2 \bin)
* In your %SYSTEMROOT%\System32, %SYSTEMROOT%\system and %SYSTEMROOT% directory.
Note: %SYSTEMROOT%\System32 only applies to Windows NT/2000/XP)
* In your whole %PATH%

Side Note : Thanks to Shannon Tang for pointing this out



Modifying Apache Configuration

Apache doesn't know that you just install PHP. We need to tell Apache about PHP and where to find it. Open the Apache configuration file in C:\Program Files\Apache Group\Apache2\conf\httpd.conf and add the following three lines :

LoadModule php4_module php/sapi/php4apache2.dll
AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .phps

The first line tells Apache where to load the dll required to execute PHP and the second line means that every file that ends with .php should be processed as a PHP file. You can actually change it to anything you want like .html or even .asp! The third line is added so that you can view your php file source code in the browser window. You will see what this mean when you browse this tutorial and click the link to the example's source code like this one.

Now restart Apache for the changes to take effect ( Start > Programs > Apache HTTP Server 2.0.50 > Control Apache Server > Restart ) . To check if everything is okay create a new file, name it as test.php and put it in document root directory ( C:\Program Files\Apache Group\Apache2\htdocs ). The content of this file is shown below.


phpinfo();
?>

phpinfo() is the infamous PHP function which will spit out all kinds of stuff about PHP and your server configuration. Type http://localhost/test.php on your browser's address bar and if everything works well you should see something like this :


Installing MySQL

First extract the package ( mysql-4.0.18-win.zip ) to a temporary directory, then run setup.exe. Keep clicking the next button to complete the installation. By default MySQL will be installed in C:\mysql.

Open a DOS window and go to C:\mysql\bin and then run mysqld-nt --console , you should see some messages like these :
C:\mysql\bin>mysqld-nt --console
InnoDB: The first specified data file .\ibdata1 did not exist:
InnoDB: a new database to be created!
040807 10:54:09 InnoDB: Setting file .\ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
040807 10:54:11 InnoDB: Log file .\ib_logfile0 did not exist: new to be created

InnoDB: Setting log file .\ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
040807 10:54:12 InnoDB: Log file .\ib_logfile1 did not exist: new to be created

InnoDB: Setting log file .\ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
040807 10:54:31 InnoDB: Started
mysqld-nt: ready for connections.
Version: '4.0.18-nt' socket: '' port: 3306

Now open another DOS window and type C:\mysql\bin\mysql

if your installation is successful you will see the MySQL client running :


C:\mysql\bin>mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
Type exit on the mysql> prompt to quit the MySQL client.

Now let's install MySQL as a Service. The process is simple just type mysqld-nt --install to install the service and net start mysql to run the service. But make sure to shutdown the server first using mysqladmin -u root shutdown



C:\mysql\bin>mysqladmin -u root shutdown

C:\mysql\bin>mysqld-nt --install
Service successfully installed.

C:\mysql\bin>net start mysql

The MySQL service was started successfully.

C:\mysql\bin>mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>


Modifying PHP Configuration File ( php.ini )

PHP stores all kinds of configuration in a file called php.ini.You can find this file in the directory where you installed PHP. Sometimes you will need to modify this file for example to use a PHP extension. I won't explain each and every configuration available just the ones that often need modification or special attention.

Some of the configurations are :

1. register_globals
2. error_reporting and display_errors
3. extension and extension_path
4. session.save_path
5. max_execution_time

register_globals

Before PHP 4.2.0 the default value for this configuration is On and after 4.2.0 the default value is Off. The reason for this change is because it is so easy to write insecure code with this value on. So make sure that this value is Off in php.ini.
error_reporting and display_errors

Set the value to error_reporting = E_ALL during development but after production set the value to error_reporting = E_NONE .

The reason to use E_ALL during development is so you can catch most of the nasty bugs in your code. PHP will complain just about any errors you make and spit out all kinds of warning ( for example if you're trying to use an uninitialized variable ).

However, after production you should change the value to E_NONE so PHP will keep quiet even if there's an error in your code. This way the user won't have to see all kinds of PHP error message when running the script.

One important thing to note is that you will also need to set the value of display_erros to On. Even if you set error_reporting = E_ALL you will not get any error message ( no matter how buggy our script is ) unless display_errors is set to On.
extension and extension_path

PHP4 comes with about 51 extensions such as GD library ( for graphics creation and manipulation ), CURL, PostgreSQL support etc. These extensions are not turned on automatically. If you need to use the extension, first you need to specify the location of the extensions and then uncomment the extension you want.

The value of extension_path must be set to the directory where the extension is installed which is PHP_INSTALL_DIR/extensions, with PHP_INSTALL_DIR is the directory where you install PHP. For example I installed PHP in C:\Program Files\Apache Group\Apache2\php so the extensions path is :

extension_path = C:/Program Files/Apache Group/Apache2/php/extensions/

Don't forget to add that last slash or it won't work

After specifying the extension_path you will need to uncomment the extension you want to use. In php.ini a comment is started using a semicolon (;). As an example if you want to use GD library then you must remove the semicolon at the beginning of ;extension=php_gd2.dll to extension=php_gd2.dll
session.save_path

This configuration tells PHP where to save the session data. You will need to set this value to an existing directory or you will not be able to use session. In Windows you can set this value as session.save_path = c:/windows/temp/
max_execution_time

The default value for max_execution_time is 30 ( seconds ). But for some scripts 30 seconds is just not enough to complete it's task. For example a database backup script may need more time to save a huge database.

If you think your script will need extra time to finish the job you can set this to a higher value. For example to set the maximun script execution time to 15 minutes ( 900 seconds ) you can modify the configuration as max_execution_time = 900

PHP have a convenient function to modify PHP configuration in runtime, ini_set(). Setting PHP configuration using this function will not make the effect permanent. It last only until the script ends.

Mod_rewrite is an Apache module that can be accessed from .htaccess files to perform all kinds of complicated URL manipulation. A few months ago I posted an article called Beat Your Website into Submission with .htaccess explaining how to use several .htaccess features to do helpful tricks; but I didn't really touch on mod_rewrite or RewriteRule. Since then I was involved in a project that required extensive use of mod_rewrite and I've come to truly appreciate its power and usefulness. The main mod_rewrite function, RewriteRule, is powered by regular expressions. Regular expressions are used to search blocks of text for specific patterns. I barely have enough room in this article to scratch the surface of regular expressions; so if you need more detail in that area I recommend this website. For the purposes of this tutorial, though, I'll be sticking to commonly used URL rewriting tasks.
The Setup

Before you start messing with RewriteRule, you need to make sure your .htaccess file is ready for tinkering. The first two lines in the sample code below activate the mod_rewrite module. The third line prevents more than ten redirects per browser call. If this line is left out, a misplaced "+" or "." in your regular expression could create an infinite loop that will grind your server to a halt. Unfortunately, I didn't discover this little snippet until I was about half way through my project. But you can avoid my mistake. Put this at the top of your .htaccess file.

Options +FollowSymlinks
RewriteEngine on
RewriteOptions MaxRedirects=10
RewriteRule Basics

Options +FollowSymlinks
RewriteEngine on
RewriteOptions MaxRedirects=10
RewriteRule ^(.*).htm$ $1.php [NC]

301 Permanently Moved

302 Found

307

Options +FollowSymlinks
RewriteEngine on
RewriteOptions MaxRedirects=10
RewriteRule ^(.+).php$ http://new_domain.com/$1.php [R=301,NC]

Options +FollowSymlinks
RewriteEngine on
RewriteOptions MaxRedirects=10

RewriteRule ^(.{6,12})$ http://mydomain.com/pages/users/index.php?user_name=$1 [NC]
RewriteRule ^$ http://mydomain.com/pages/index.php [R=301,NC]

Options +FollowSymlinks
RewriteEngine off
Beautifying and Optimizing Your URLs

Options +FollowSymlinks
RewriteEngine on
RewriteOptions MaxRedirects=10

RewriteRule ^blog/([^/]+)/([^/]+)$ blog.php?category=$1&article_id=$2 [NC]

You can provide hints to give the optimizer information about how to choose indexes during query processing. Section 12.2.8.1, “JOIN Syntax”, describes the general syntax for specifying tables in a SELECT statement. The syntax for an individual table, including that for index hints, looks like this:

tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
index_hint [, index_hint] ...

index_hint:
USE {INDEX|KEY}
[{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
| IGNORE {INDEX|KEY}
[{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
| FORCE {INDEX|KEY}
[{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
index_name [, index_name] ...
By specifying USE INDEX (index_list), you can tell MySQL to use only one of the named indexes to find rows in the table. The alternative syntax IGNORE INDEX (index_list) can be used to tell MySQL to not use some particular index or indexes. These hints are useful if EXPLAIN shows that MySQL is using the wrong index from the list of possible indexes.

You can also use FORCE INDEX, which acts like USE INDEX (index_list) but with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the given indexes to find rows in the table.

Each hint requires the names of indexes, not the names of columns. The name of a PRIMARY KEY is PRIMARY. To see the index names for a table, use SHOW INDEX.

An index_name value need not be a full index name. It can be an unambiguous prefix of an index name. If a prefix is ambiguous, an error occurs.

Prior to MySQL 5.1.17, USE INDEX, IGNORE INDEX, and FORCE INDEX affect only which indexes are used when MySQL decides how to find rows in the table and how to process joins. They do not affect whether an index is used when resolving an ORDER BY or GROUP BY clause.

Examples:

SELECT * FROM table1 USE INDEX (col1_index,col2_index)
WHERE col1=1 AND col2=2 AND col3=3;

SELECT * FROM table1 IGNORE INDEX (col3_index)
WHERE col1=1 AND col2=2 AND col3=3;
As of MySQL 5.1.17, the syntax for index hints is extended in the following ways:

It is syntactically valid to specify an empty index_list for USE INDEX, which means “use no indexes.” Specifying an empty index_list for FORCE INDEX or IGNORE INDEX is a syntax error.

You can specify the scope of a index hint by adding a FOR clause to the hint. This provides more fine-grained control over the optimizer's selection of an execution plan for various phases of query processing. To affect only the indexes used when MySQL decides how to find rows in the table and how to process joins, use FOR JOIN. To influence index usage for sorting or grouping rows, use FOR ORDER BY or FOR GROUP BY. (However, if there is a covering index for the table and it is used to access the table, the optimizer will ignore IGNORE INDEX FOR {ORDER BY|GROUP BY} hints that disable that index.)

You can specify multiple index hints:

SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;
It is not a error to name the same index in several hints (even within the same hint):

SELECT * FROM t1 USE INDEX (i1) USE INDEX (i1,i1);
However, it is an error to mix USE INDEX and FORCE INDEX for the same table:

SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);
The default scope of index hints also is changed as of MySQL 5.1.17. Formerly, index hints applied only to how indexes are used for retrieval of records and not during resolution of ORDER BY or GROUP BY clauses. As of 5.1.17, if you specify no FOR clause for an index hint, the hint by default applies to all parts of the statement. For example, this hint:

IGNORE INDEX (i1)
is equivalent to this combination of hints:

IGNORE INDEX FOR JOIN (i1)
IGNORE INDEX FOR ORDER BY (i1)
IGNORE INDEX FOR GROUP BY (i1)
To cause the server to use the older behavior for hint scope when no FOR clause is present (so that hints apply only to row retrieval), enable the old system variable at server startup. Take care about enabling this variable in a replication setup. With statement-based binary logging, having different modes for the master and slaves might lead to replication errors.

When index hints are processed, they are collected in a single list by type (USE, FORCE, IGNORE) and by scope (FOR JOIN, FOR ORDER BY, FOR GROUP BY). For example:

SELECT * FROM t1
USE INDEX () IGNORE INDEX (i2) USE INDEX (i1) USE INDEX (i2);
is equivalent to:

SELECT * FROM t1
USE INDEX (i1,i2) IGNORE INDEX (i2);
The index hints then are applied for each scope in the following order:

{USE|FORCE} INDEX is applied if present. (If not, the optimizer-determined set of indexes is used.)

IGNORE INDEX is applied over the result of the previous step. For example, the following two queries are equivalent:

SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) USE INDEX (i2);

SELECT * FROM t1 USE INDEX (i1);
For FULLTEXT searches, index hints do not work before MySQL 5.1.31. As of 5.1.31, index hints work as follows:

For natural language mode searches, index hints are silently ignored. For example, IGNORE INDEX(i) is ignored with no warning and the index is still used.

For boolean mode searches, index hints with FOR ORDER BY or FOR GROUP BY are silently ignored. Index hints with FOR JOIN or no FOR modifier are honored. In contrast to how hints apply for non-FULLTEXT searches, the hint is used for all phases of query execution (finding rows and retrieval, grouping, and ordering). This is true even if the hint is given for a non-FULLTEXT index.

For example, the following two queries are equivalent:

SELECT * FROM t
USE INDEX (index1)
IGNORE INDEX (index1) FOR ORDER BY
IGNORE INDEX (index1) FOR GROUP BY
WHERE ... IN BOOLEAN MODE ... ;

SELECT * FROM t
USE INDEX (index1)
WHERE ... IN BOOLEAN MODE ... ;
Index hints are accepted but ignored for UPDATE statements.

The Import and Export Wizard in Outlook makes it easy to export contact information from Outlook into an Excel worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.).

Note Before you use the wizard, it helps to understand the difference between the Outlook Address Book (Address Book: The collection of address books that you can use to store names, e-mail addresses, fax numbers, and distribution lists. The Address Book may contain a Global Address List, an Outlook Address Book, and a Personal Address Book.) and Outlook Contacts. Both are part of Outlook. However, the Address Book is a compilation of the different address lists you might have stored in Outlook, such as a Personal Address Book (.pab), LDAP (Lightweight Directory Access Protocol (LDAP): A protocol that provides access to Internet Directories.) Internet directories, the Global Address List (GAL) (Global Address List: The address book that contains all user, group, and distribution list e-mail addresses in your organization. The administrator creates and maintains this address book. It may also contain public folder e-mail addresses.), or other third-party address books. Contacts is just one of the address lists that make up the Address Book. Only contacts can be exported directly from Outlook to Excel.

1. In Outlook, on the File menu, click Import and Export.
2. Click Export to a file, and then click Next.
3. Click Microsoft Excel, and then click Next.
4. In the folder list, click the Contacts folder, and then click Next.
5. Browse to the folder where you want to save the contacts as an Excel file (.xls).
6. Type a name for the exported file, and then click OK.
7. Click Next.
8. To add or remove fields to determine the way the contact information is saved in the new Excel worksheet, click Map Custom Fields.

ShowHow?
1. In the From box, drag the field you want to convert onto the Outlook field that is listed in the To box.

Note The Name field doesn't appear in the From box, because Outlook reserves the first row of a named (name: A word or string of characters in Excel that represents a cell, range of cells, formula, or constant value.) range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) for field names. If the range that you import does not contain field names, the first row of data will be interpreted as field names and will not be imported as data. To ensure that all of the data in the named range is imported, use the first row for field names.
* To see additional records in the From box, click Previous or Next.
* To remove all mapping, click Clear Map.
* To reset the original mapping, click Default Map.
2. To display additional fields in the From box, click the plus sign (+) next to the field. For example, to display the Business Street and Business City fields, click the + next to Business Address.
9. Click Finish.

MySQL 5.0 introduced Stored Procedures which allow us to automate or program our way out of many tasks on directly on the server rather than having to write external scripts to do complex manipulation of data.

As you get used to writing stored Procedures in MySQL 5.0, you will, as with any other programming language, want to generalize your stored procedures as much as possible. The more flexible your stored procedure is, the more tasks it can be used for -- and the less places you have to go searching for that elusive bug that just keeps giving you the wrong result. The day you end up making a copy of a stored procedure just to change a name or two is the day you need to think how tweaking the original procedure can accomplish what you want without breaking old functionality.

Most stored procedures that you will be writing for MySQL 5.0 will undoubtedly reference static table and column names. However, sometimes it is desirable to be able to pass these details to a stored procedure as parameters.

There is no direct support for dynamic declaration of details such as column or table names in MySQLs stored procedures. However, by utilizing user variables (which have been around for a while) and prepared statements (introduced in MySQL 4.1), we can do some trickery to get the results we want.

What follows is a step-by step guide to demonstrate how we can build a prepared statement in the command-line client, then use the ideas gathered from these examples and build a stored procedure which can manipulate data from any table and column that we specify. The samples are by nature simplistic, but you should quickly be able to gather how you can extend these to much more complex usage.

The examples below are shown as you would type them into the command-line client, but the input prompts have been omitted for easy cut-and-paste.

The base tables for the examples are those found it the world database, which you can download on the MySQL documentation page.

In this article, we explore how to find the average of a given column in some table. For example, we might want to find the average life expectancy in all countries:

SELECT AVG(LifeExpectancy) FROM Country;
+---------------------+
| AVG(LifeExpectancy) |
+---------------------+
| 66.48604 |
+---------------------+
Another example is the average population of the cities defined in the world database:

SELECT AVG(Population) FROM City;
+-----------------+
| AVG(Population) |
+-----------------+
| 350468.2236 |
+-----------------+
The goal is to build a stored procedure that will take a table name and a column name and display the average of the values in the column, just as the two examples above.

The first thing we need to understand is how user variables are used and assigned values. A user variable is distinguished by having a '@' symbol in front of it, and values are assigned using the SET statement:

SET @a := 'abc';
SELECT @a;
+------+
| @a |
+------+
| abc |
+------+
We can assign the value of any expression to a user variable, so the following also works:

SET @s := CONCAT('SELECT AVG(' , 'Population' , ') FROM ' , 'City');
SELECT @s;
+----------------------------------+
| @s |
+----------------------------------+
| SELECT AVG(Population) FROM City |
+----------------------------------+
...which is exactly one of the statements we'd like our stored procedure to build on-the-fly and execute.

Next in line to consider is prepared statements. Prepared statements are statements (such as queries) which are sent to the server, but the processing of the statement is halted as soon as the statement has been parsed, and the parsed statement is remembered by the server. We then have the option of telling the server to execute that statement one or more times as we wish.

We can prepare a statement from within the command-line client, and we will do much the same later on in our stored procedure:

PREPARE stmt FROM "SELECT AVG(Population) FROM City";
EXECUTE stmt;
+-----------------+
| AVG(Population) |
+-----------------+
| 350468.2236 |
+-----------------+
As you can see, we prepare the statement using a simple string expression. A statement can also be prepared from a user variable:

PREPARE stmt FROM @s;
EXECUTE stmt;
+-----------------+
| AVG(Population) |
+-----------------+
| 350468.2236 |
+-----------------+
Note that a literal string expression or a user variable are the only ways you can specify the statement to be prepared. You cannot prepare a statement using an expression (which is also the reason why we're taking the seemingly roundabout way of using user variables to complete this task).

We're now a the stage where we can build our stored procedure colavg that will take a table and column name, and return the average of the values found in the column.

The first thing we do is to change the command-line client's command delimiter, to ensure that we can use semicolons inside the stored procedure without the client regarding them as end-of-statement. If you're using another client, you should of course skip the delimiter commands.

The input parameters are both of CHAR(64), which is the maximum size of these identifiers in MySQL. Since we are indeed reading from tables, we specify READS SQL DATA in the procedure declaration.

Inside the procedure, we concatenate the two static parts of the SELECT statement with the parameter values col and tbl into the user variable @s (remember, we can't use expressions in PREPARE statements). Finally, we execute the statement which will return the result to our client.

Finally, we reset the mysql client delimiter to the well-known semicolon.

delimiter //
DROP PROCEDURE IF EXISTS colavg//
CREATE PROCEDURE colavg(IN tbl CHAR(64), IN col CHAR(64))
READS SQL DATA
COMMENT 'Selects the average of column col in table tbl'
BEGIN
SET @s = CONCAT('SELECT AVG(' , col , ') FROM ' , tbl);
PREPARE stmt FROM @s;
EXECUTE stmt;
END;
//
delimiter ;
We now have a stored procedure which will allow us to select the average of any column in any table:

CALL colavg('Country', 'LifeExpectancy');
+---------------------+
| AVG(LifeExpectancy) |
+---------------------+
| 66.48604 |
+---------------------+

CALL colavg('City', 'Population');
+-----------------+
| AVG(Population) |
+-----------------+
| 350468.2236 |
+-----------------+
One final note on compatibility with other SQL dialects: In the stored procedure, we execute simple SELECT statements which returns the output of the SELECT statement to the client. This behavior is a MySQL extention to the SQL standard, which is not likely to work in other RDBMSs. In most cases, you'll probably want to SELECT into one or more user variables, then pass the result back in an OUT or INOUT parameter.

There are a number of tricks that you can use to squeeze the last bit of performance from your scripts. These tricks won't make your applications much faster, but can give you that little edge in performance you may be looking for. More importantly it may give you insight into how PHP internals works allowing you to write code that can be executed in more optimal fashion by the Zend Engine. Please keep in mind that these are not the 1st optimization you should perform. There are some far easier and more performance advantageous tricks, however once those are exhausted and you don't feel like turning to C, these maybe tricks you would want to consider. So, without further ado...

1) When working with strings and you need to check that the string is either of a certain length you'd understandably would want to use the strlen() function. This function is pretty quick since it's operation does not perform any calculation but merely return the already known length of a string available in the zval structure (internal C struct used to store variables in PHP). However because strlen() is a function it is still somewhat slow because the function call requires several operations such as lowercase & hashtable lookup followed by the execution of said function. In some instance you can improve the speed of your code by using a isset() trick.

Ex.
if (strlen($foo) < style="font-weight: bold;">print vs echo

Even both of these output mechanism are language constructs, if you benchmark the two you will quickly discover that print() is slower then echo(). The reason for that is quite simple, print function will return a status indicating if it was successful or not, while echo simply print the text and nothing more. Since in most cases (haven't seen one yet) this status is not necessary and is almost never used it is pointless and simply adds unnecessary overhead.

printf

Using printf() is slow for multitude of reasons and I would strongly discourage it's usage unless you absolutely need to use the functionality this function offers. Unlike print and echo printf() is a function with associated function execution overhead. More over printf() is designed to support various formatting schemes that for the most part are not needed in a language that is typeless and will automatically do the necessary type conversions. To handle formatting printf() needs to scan the specified string for special formatting code that are to be replaced with variables. As you can probably imagine that is quite slow and rather inefficient.

heredoc

This output method comes to PHP from PERL and like most features adopted from other languages it's not very friendly as far as performance is concerned. While this method allows you to easily output large chunks of text while preserving things like newlines and even allow for variable handling inside the text block this is quite slow and there are better ways to do that. Performance wise this is just marginally faster then printf() however it does not offer nearly as much functionality.

?> <?

When you need to output a large or even a medium sized static bit of text it is faster and simpler to put it outside the of PHP. This will make the PHP's parser effectively skipover this bit of text and output it as is without any overhead. You should be careful however and not use this for many small strings in between PHP code as multiple context switches between PHP and plain text will ebb away at the performance gained by not having PHP print the text via one of it's functions or constructs.

4) Many scripts tend to reply on regular expression to validate the input specified by user. While validating input is a superb idea, doing so via regular expression can be quite slow. In many cases the process of validation merely involved checking the source string against a certain character list such as A-Z or 0-9, etc... Instead of using regex in many instances you can instead use the ctype extension (enabled by default since PHP 4.2.0) to do the same. The ctype extension offers a series of function wrappers around C's is*() function that check whether a particular character is within a certain range. Unlike the C function that can only work a character at a time, PHP function can operate on entire strings and are far faster then equivalent regular expressions.
Ex.
preg_match("![0-9]+!", $foo);
vs
ctype_digit($foo);

5) Another common operation in PHP scripts is array searching. This process can be quite slow as regular search mechanism such as in_array() or manuall implementation work by itterating through the entire array. This can be quite a performance hit if you are searching through a large array or need to perform the searches frequently. So what can you do? Well, you can do a trick that relies upon the way that Zend Engine stores array data. Internally arrays are stored inside hash tables when they array element (key) is the key of the hashtables used to find the data and result is the value associated with that key. Since hashtable lookups are quite fast, you can simplify array searching by making the data you intend to search through the key of the array, then searching for the data is as simple as $value = isset($foo[$bar])) ? $foo[$bar] : NULL;. This searching mechanism is way faster then manual array iteration, even though having string keys maybe more memory intensive then using simple numeric keys.

Ex.

$keys = array("apples", "oranges", "mangoes", "tomatoes", "pickles");
if (in_array('mangoes', $keys)) { ... }

vs

$keys = array("apples" => 1, "oranges" => 1, "mangoes" => 1, "tomatoes" => 1, "pickles" => 1);
if (isset($keys['mangoes'])) { ... }

The bottom search mechanism is roughly 3 times faster.

If you know or have any additional optimization tricks let me know .

One of PHP5’s most interesting new features is the addition of Iterators, a collection of ready-made interfaces designed to help in navigating and processing hierarchical data structures. These Iterators significantly reduce the amount of code required to process an XML document tree or a file collection. A number of Iterators are available, including the ArrayIterator, CachingIterator, LimitIterator, RecursiveIterator, SimpleXMLIterator and DirectoryIterator.

It’s this last Iterator that’s the subject of this How do I… tutorial. The DirectoryIterator provides a quick and efficient way of processing the files in a directory; with a little creative coding, it can also be used to recursively process a nested directory tree. Both these tasks can be accomplished using just a few lines of code, representing a significant improvement over the “standard” way of doing things.

Processing a single-level directory

Let’s begin with something simple: processing a single-level directory. Type (or copy) the following script (Listing A), altering the directory path to reflect your local configuration:

Listing A

$it = new DirectoryIterator("/tmp/mystuff");
foreach($it as $file) {
if (!$it->isDot()) {
echo $file . "\n";
}
}
?>
When you view the output of this script in your browser, you should see a list of the files in the named directory. How did this happen? Well, the DirectoryIterator class provides a pre-built interface to iterating over the contents of a directory; once instantiated with the location of the target directory, it can then be processed as though it were a standard PHP array, with each element representing a file in the directory. Note the use of the isDot() method to filter out the “.” and “..” directories, respectively.

Processing a nested directory tree

Recursively processing a nested directory tree is almost as simple. In this case, the DirectoryIterator needs to check each object it encounters within the first-level directory, determine whether it is a file or directory, and, if a directory, drill one level deeper to examine the next level of contents. This sounds fairly complex, and in the past could easily add up to 15-plus lines of code.

With PHP5, though, all you need are two new Iterators: the RecursiveDirectoryIterator and the RecursiveIteratorIterator, which together incorporate all the above functionality. Take a look at Listing B:

Listing B

$it = new RecursiveDirectoryIterator("/tmp");
foreach(new RecursiveIteratorIterator($it) as $file) {
echo $file . "\n";
}
?>
In this case, the output should now include a list of all the files and directories under the starting directory. Needless to say, this kind of built-in recursive interface is very handy for situations that require you to process all the files under a particular directory level — for example, when recursively compressing a directory tree, or altering group/owner permissions on a series of nested files.

A real-world application: Printing a directory tree

A common application of directory recursion involves printing a graphical directory tree. With Iterators, this task is a snap, because included within the Iterator class documentation is an example class written specifically for this purpose. The DirectoryTreeIterator (credit: Marcus Boerger) provides additional enhancements to the RecursiveIteratorIterator discussed previously, most notably ASCII markers that represent depth and location within the tree structure.

You can examine the source code for this example class on the php.net Web site.

Listing C shows how the DirectoryTreeIterator can be used.

Listing C

$it = new DirectoryTreeIterator("/tmp/cookbook/");
foreach($it as $path) {
echo $path . "\n";
}
?>
And here’s a brief snippet of the output you might see:
|-ch01
| |-recipe01
| | |-example01.php
| | \-example02.php
| |-recipe02
| | |-example01.php
| | \-example02.php
| |-recipe03
| | \-example01.php
...
To better understand the value-add of these various DirectoryIterators, try coding the three applications demonstrated in this tutorial using standard file and directory functions. Once you’re done, you’ll have a new appreciation for the simplicity and ease of use the DirectoryIterators bring to PHP5. Happy coding!

The end appears to be in sight for the beloved version 4 of PHP, the open-sourced scripting language that allows seasoned programmers and beginners alike to quickly and easily write code for the World Wide Web.

According to the terse announcement on the main PHP Web site:

The PHP development team hereby announces that support for PHP 4 will continue until the end of this year only. After 2007-12-31 there will be no more releases of PHP 4.4. We will continue to make critical security fixes available on a case-by-case basis until 2008-08-08. Please use the rest of this year to make your application suitable to run on PHP 5.

The announcement came on the third anniversary since PHP 5 was launched. Project programmers says that they want to devote their finite resources on the upcoming PHP 6 instead.

According to Rasmus Lerdorf, the original PHP author and now a Yahoo programmer:

“Ending PHP 4 support is driven by practical necessity. We are an open-source project with limited resources. With PHP 6 on the way, we don’t have the resources to support three different versions of PHP at the same time.”

Detractors to the decision have less kind words to say. According to Matt Mullenweg, the founder of the WordPress blogging software and site, which uses PHP:

“PHP 5 has been, from an adoption point of view, a complete flop. Most estimates place it in the single-digit percentages or at best the low tens.”

“Now the PHP core team seems to have decided that the boost their failing product needs is to kill off their successful one instead of asking the hard questions: What was it that made PHP 4 so successful?…Why wasn’t PHP 5 compelling to that same audience? Are the things we’re doing in PHP 6 crucial to our core audience or simply ‘good’ language problems to solve?”

Zend Technologies has announced the availability of version 3.6 of Zend Platform. The new version of the PHP Web application server delivers new and enhanced functionality in three major areas: PHP intelligence, performance management, and cluster management.

PHP Intelligence has to do with the monitoring of HTTP, Apache, and Java events, as well as offering better diagnostics to improve the overall reliability and stability of an infrastructure based on PHP. Downtime is reduced by recording full context for reported problems so that “root cause” diagnostics and resolution can be made within a short time. The other two aspects are pretty much self-explanatory.

Excerpt from eWeek:

Performance has remained an issue for dynamic languages such as PHP and Ruby. Zend Platform 3.6 improves the performance of PHP applications by caching pre-optimized PHP byte code, Zend officials said. The product features support for file- or URL-based page caching, client-side caching and in-memory or disk-based data caching.

The PHP development team would like to announce the immediateavailability of PHP 5.2.6. This release focuses on improving the stability ofthe PHP 5.2.x branch with over 120 bug fixes, several of which are security related.All users of PHP are encouraged to upgrade to this release.

Further details about the PHP 5.2.6 release can be found in the release announcement for 5.2.6, the full list of changes is available in the ChangeLog for PHP 5.

Security Enhancements and Fixes in PHP 5.2.6:

• Fixed possible stack buffer overflow in the FastCGI SAPI identified by Andrei Nigmatulin.
• Fixed integer overflow in printf() identified by Maksymilian Aciemowicz.
• Fixed security issue detailed in CVE-2008-0599 identified by Ryan Permeh.
• Fixed a safe_mode bypass in cURL identified by Maksymilian Arciemowicz.
• Properly address incomplete multibyte chars inside escapeshellcmd() identified by Stefan Esser.
• Upgraded bundled PCRE to version 7.6

The PHP team is once again proud to participate in the Google Summer of Code. Ten students will "flip bits instead of burgers" this summer:

• Zend LLVM Extension by Joonas Govenius, mentored by Nuno Lopes
• PHP Optimizer by Samuel Graham Kelly IV, mentored by Derick Rethans
• PhD (PHP Docbook) Project by Rudy Nappée, mentored by Hannes Magnusson
• Replace auto* with CMake by Alejandro Leiva Rojas, mentored by Pierre A. Joye
• gsoc:2008 - XDebug by Chung-Yang Lee, mentored by David Coallier
• Rewrite the run-tests.php script by Cesar Montedonico, mentored by Travis Swicegood
• PHP Bindings for Cairo by Akshat Gupta, mentored by Anant Narayanan
• Algorithm Optimizations by Michal Dziemianko, mentored by Scott MacVicar
• PECL, Website Improvements by Barry Carlyon, mentored by Helgi Þormar Þorbjörnsson
• Implement Unicode into PHP 6 by Henrique do Nascimento Angelo, mentored by Scott MacVicar