Friday, August 31, 2007

Send Header without error

One of the common errors a web developer often gets is "Headers already sent" error.This happens when we render some text/characters /space in the browser and then try to redirect the page using header function of php.
For long I always thought that header function will only work when there is nothing rendered before the header call.
But recently I discovered(ofcorse with help of google) that we can render anything before the header call and still redirect the page.

Normal Usage with error is

echo "This will give error"

echo "This will NOT give error";
header("refresh:0; url:");

Here refresh:0 means the page will load after 0 seconds(which is instantly).
Basically this can be used when we want to have delay the page loading say we want to load yahoo after 2 seconds.
In this case the code will be

header("refresh:2; url:");

So start using this header call and redirect the page anytime during page execution.

Saturday, August 18, 2007

Store procedure usage in PHP

Stored routines (procedures and functions) are supported in MySQL 5.1. A stored procedure is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing the individual statements but can refer to the stored procedure instead.

A stored routine is either a procedure . Stored routines are created with CREATE PROCEDURE statements. A procedure is invoked using a CALL statement, and can only pass back values using output variables. Stored routines may call other stored routines.
A stored procedure or function is associated with a particular database.

Procedures can be created using CREATE PROCEDURE command of mysql.
To know the exact syntax visit Online MySql documentation.

Once the procedure is created we have to use it in php code for this PHP's mysqli extension allows you to access the functionality provided by MySQL 4.1 and above

In order to have these functions available, you must compile PHP with support for the mysqli extension(MySQL Improved Extension).

To install the mysqli extension for PHP, use the --with-mysqli=mysql_config_path/mysql_config configuration option where mysql_config_path represents the location of the mysql_config program that comes with MySQL versions greater than 4.1.

mysqli functions are similar to mysql functions, there is substitute for each mysql functions in mysqli.
For e.g.

mysqli_query() has to be used instead of mysql_query()

using mysqli_query() the stored procedure query can be called and its result can be fetched.

Note: if you have to use any of mysqli functions then you will have to use mysqli functions throughout the current mysql connection
i.e. from mysqli_connect() to mysqli_close() all intermediate functions used must be of mysqli and not mysql.


Thursday, August 9, 2007

Control Flow Functions of MySQL

While doing web programming many times you fetch data from database and depending on the value fetched, the user is shown something or other.
In case of optional fields in table it is quite likely that the value may be NULL or something default that you have set.
Take for example and option field called product awards which lists all awards bagged by that product.

When you write query
SELECT pro_id, pro_awards FROM products

This will give info regarding all the awards bagged by products.
However since pro_awards is optional many records will contain value NULL.

To tackle this NULL value you check in php whether the fetched value is NULL, if yes you display "No awards bagged by this product".

You can also do this checking at database level to reduce some load on webserver.
And as Databases are configured at their optimum level the processing time is much faster than your php / any script.

This can be done this way:
SELECT pro_id, IF(pro_awards IS NULL,'No awards bagged by this product', pro_awards) as pro_awards
FROM products
Above was use of if-else construct in MySQL

You can also make use of Switch-case construct effectively.
SELECT pro_id, CASE WHEN pro_awards IS NULL THEN No awards bagged by this product' ELSE pro_awards END FROM products

Above examples are very simple to understand, I am sure you can come out with difficult situations and use these Control flow functions of MySQL effectively to reduce script execution time.