Many of my customers hold data in Excel files. Then, when they decide to build a web based, database driven project controlled by PHP, ask me to move their Excel data into the MySQL. Whether it’s possible and how to do it, will be the content of this post.

Since the PHP allows you to create an OLE compatible COM object with its methods and properties, the solution is more than easy. All you need is take a brief look into the PHP COM Manual Pages to be able to understand the following explanatory example. I wrote this code as a PHP CLI script which seems to me more usable for interaction with other applications.

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

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

// 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 . "");
}

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.

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

Regular expression is the basic functionality of pattern comparison. PHP offers two sets of functions for regular expressions - POSIX style and Perl style. Both types have their unique syntax and this post should give basic overview of the POSIX one.

Regular expression (called regex) is nothing more just sequence of characters (called pattern) which is compared agains a text in which we search. Patterns contain a combination of metacharacters and literals. Metacharacters (also called operators) define how literals (also called constants) should be treated on pattern evaluation against evaluated expression. For example, POSIX pattern [a-z0-9] which determines valid expression containig lowercase letters or number 0-9 has two metacharacters (opening square bracket and closing square bracket) and two literal ranges (a-z and 0-9, also called classes). In other words, literal means character itself whilst metacharacter means control character. Why it’s so important to distinguish between metacharacters and literals? The reason is that if you need to use metacharacters in pattern as a literal you must precede it by \ (backslash), very often said: it must be escaped. For example, if you need to add a dot in the regular expression pattern and don’t want to use this dot as a control character with meaning “any character” it is necessary to escape it - use it with backslash (see the table below for an example).

Following table lists POSIX metacharacters:

Following table lists POSIX character classes for more comfortable programming:

This table lists PHP POSIX regex functions:

Regular expressions are very usefull when we need to check some user inputs. If you have a contact form on your site which contains mandatory e-mail address field, how would you check whether user input string has valid e-mail format? Use regular expression match! Here are some examples for better understanding:

• ^(([A-Za-z0-9_-]+)[.]([A-Za-z0-9_-]+))+$ : matches a hostname expression (hostname.example.com)
• ^([0-9]{1,3})\.([0-9]{1,3})[.]([0-9]{1,3})\.([0-9]{1,3})$ : matches an IP address (192.168.10.122)
• ^([A-Za-z0-9._-]+)@([A-Za-z0-9._-]+)[.]([a-z]{2,4})$ : matches an e-mail address (mailbox@example.com)

Maybe you have noticed that sometimes there is a choice how to write regular expression pattern. In the first and third example above the dot character is expressed as a member of list class [.] whilst in the second example (IP address regexp) the dot is expressed as an escaped metacharacter \. at some places (this was done for demonstration purposes).

Another very important detail which should be noted is the fact that if you need to use metacharacters in a range class or list class it must be placed at the end of a content of such class, right before closing square bracket [... _-].

You can play with staed above examples by pasting the following code into a regexp.php file and run it in a browser:

  Enter String:



  



  Select Pattern:



  Hostname



  IP Address



  Email Address



  





' . $pattern . '

';

echo 'String: ' . $string . '



';



echo 'Match: ';

if (ereg($pattern, $string))

  echo 'OK';

else

  echo 'WRONG';

?>

I hope this post gave you at least basic overview of POSIX regular expressions and their use in PHP. In some of future articles we will take a look at Perl style regular expressions.

This tutorial will teach you the very basics on how to use a PHP #include command. The #include command is used to insert the content of an external HTML page into an existing PHP page. For example, the header and footer of this page you are reading right now are actually external files which are loaded into the page when you request the server to display the page. Using this technique makes it easier to update the header of all the pages of our website by simple updating a single included header file without having to update any code in any of our pages.

This technique can be used anyone without having any advanced PHP knowledge. This tutorial will teach you how to use this command.

Our tutorial is divided into two main sections:

• Server Requirements
• Code Implementation
  

Server Requirements

In order to run this command you will need to have PHP installed on your server. You can check this with your hosting service provider. Any modern version of PHP will be sufficient to execute this command. Other than that, there are no specific server requirements.

Code Implimentation

We are going to create a very simple example in which a HOME page includes a HEADER into it. This means that we are going to need two pages the HOME page and teh HEADER page.

We will start off with the header page. Using any web editing tool, simply create an HTML page with the following content:

Note that you do not need to create a proper HTML file with ,