Welcome To Tutorial Info

We’ll start here by determining the image type (is it a jpeg, gif, png?), dimensions (height and width) and a few other things by using the getimagesize() function. Note that for this example, the image we’re using - puppy.jpg - must be in the same directory as the PHP script we’re calling this function from

Before we start, let’s introduce input parameter of the function which is responsible for data retrieve from an Excel file and output it as a matrix, representing the Excel table.

• $file - (string) absolute path to the Excel data file
• $sheet - (int) order number of the sheet which data we want to extract
• $rows - (array) rows selected from the sheet
• $cols - (array) columns selected from the sheet

The names of variables were selected to represent their meaning (semantic names) and facilitate the understanding of script work. But if you’re still confused of input parameters or output, don’t be affraid, following examples will clarify it more. So, let’s move forward to the PHP and Excel interaction.

function getDataFromExcel($file, $sheet, $rows, $cols)
{
   // COM CREATE
   fwrite(STDOUT, "----------------------------------------\r\n");
   $excel = new COM("Excel.application") or die ("ERROR: Unable to instantaniate COM!\r\n");
   fwrite(STDOUT, "Application name: {$excel->Application->value}\r\n") ;
   fwrite(STDOUT, "Loaded version: {$excel->Application->version}\r\n");
   fwrite(STDOUT, "----------------------------------------\r\n\r\n");

   // DATA RETRIEVAL
   $Workbook = $excel->Workbooks->Open($file) or die("ERROR: Unable to open " . $file . "!\r\n");
   $Worksheet = $Workbook->Worksheets($sheet);
   $Worksheet->Activate;
   $i = 0;
   foreach ($rows as $row)
   {
       $i++; $j = 0;
       foreach ($cols as $col)
       {
           $j++;
           $cell = $Worksheet->Range($col . $row);
           $cell->activate();
           $matrix[$i][$j] = $cell->value;
       }
   }

   // COM DESTROY
   $Workbook->Close();
   unset($Worksheet);
   unset($Workbook);
   $excel->Workbooks->Close();
   $excel->Quit();
   unset($excel);

   return $matrix;
}

Now, when the key function is defined we can fire an extraction and insertion process:

// define inputs
$xls_path = "C:\\Users\\Johny\\Documents\\Business\\excel_data.xls"; // input file
$xls_sheet = 1; // sheet #1 from file excel_data.xls
$xls_rows = range(2, 270, 1); // I want extract rows 2 - 270 from excel_data.xls with step 1 row
$xls_cols = array("A", "B", "C", "D", "E", "F"); // I want to extract columns A - F from excel_data.xls

// initiate MySQL connection
mysql_connect("server", "username", "password") or die("Unable to connect MySQL server!");
mysql_select_db("database") or die("Unable to select requested database!");

// retrieve data from excel
$data = getDataFromExcel($xls_path, $xls_sheet, $xls_rows, $xls_cols);

// insert retrieved data into database
foreach ($data as $line)
{
   $i = 0;
   foreach ($line as $col => $entry)
   {
       // create the SET string for INSERT query
       $i++;
       $string .= "`" . $col . "` = '" . $entry . "'";
       if ($i < count($line))
           $string .= ", ";
   }
   mysql_query("INSERT INTO `table` SET " . $string . "");
}

The stated above example is simplified to emphasize the core of process, not necessary details may lead to unclear interpretation. It is supposed that readers have at least basic knowledge of PHP and MySQL.

So, as you can see there’s pretty simple way how to import data from excel file directly to database using PHP. The PHP COM interface allows you to do the same with many other types of application (Word, PowerPoint, etc.).

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:

Syntax for Stored Procedures

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”;

?>

There are many advantages of using PHP frameworks:

• Applications can be built quickly and easily
• Simple to debug
• Secured
• Easy to install and use
• Good for applications utilizing multiple platforms

CodeIgniter (CI)

One of the effective PHP Frameworks is the CodeIgniter. Web applications with advanced features can be readied using the CodeIgniter. Simpler for the beginners, CI follows an MVC (Model View Controller) pattern, thereby enabling in easy learning. Also, due to the usage of conventional PHP coding, the existing codes can be ported using this PHP framework. Also the simplicity with which it works and the speed that it has when compared to the other frameworks, is definitely considerable

Striking features of CodeIgniter

There are many features that are quite distinguishing, in CI. Few of the most important features are explained below:

User Guide

One of the most important features of the CodeIgniter is that it has a very impressive user guide. The way the documentation has been done is simply marvelous and highly useful for coders to know more about the working.

Simplicity

CodeIgniter is simple and a major portion of the work gets completed in the controllers and uploading the libraries. Since the workings are clear and open, it is very easy to understand what is happening and therefore, simple to use.

Model Handling

While working with models, CodeIgniter uses a straight way of handling. Standard SQL queries can be mimed using few simple commands. Also creation of model objects, loading it and introducing methods to deal with a customized task is also possible.

Data Validation

Data validation is a major thing while working on models. Here a validation class is used to validate data. First, few rules are defined and assigned to the object to be validated. The data that is sent through the URL is automatically validated by the validation object. Even certain error messages can also be automated the same way, using data validation class.

There are many other advantages of using CI:

• Migration from one server to another is easier and hassle-free. Also installation is easier as well.
• CI is very easy to handle and also to customize. In case a new functionality has to be applied, it can be done without affecting the customization.
• With MVC based framework, it offers flexibility and easy management
• Active Record Implementation is simply superb and easy to remember.
• Configuration and customization of these configuration files are also easy, thus facilitating easy working with various types of developers.
• The collection of libraries that it posses is also good enough.
• And as previously said, awesome documentation of the user guide, which makes any coder easy to use the whole framework.

Given below is a simple example to display user information from database. Using CodeIgniter, the programmer and designer can simultaneously work on the same project, without having to wait for each other to complete their parts. Here the first part “Create a controller named user.php and store it in Controllers folder” is prepared by the programmer, and the second part “Create a view named user.php and store it in views folder” is prepared by the designers simultaneously.

# Create a controller named user.php and store it in Controllers folder

class User extends Controller {

function User()

{

parent::Controller();

}

function view() {

$this->load->database(); # This line is not needed if database library is put into autoload

$query = $this->db->query("SELECT username, email FROM tbl_users");

if($query->num_rows() >0) {

foreach($query->result_array() as $row) {

$users[] = $row;

}

$data['users'] = $users;

}

$this->load->view('user', $data);

}

}

?>

# Create a view named user.php and store it in views folder

if(is_array($users)) {

foreach($users as $key => $value) {

?>

}

else {

?>

}

}

?>

The output comes as below: