laravel, php

changing mac’s default php setting to xamp [ enabling mcrypt in mac lion]


To those that uses XAMPP 1.7.3 and Mac

  1. Go to Terminal
  2. Enter which php
    • If it says /usr/bin/php, then proceed to 3.
  3. Enter sudo nano ~/.bash_profile
  4. then paste this export PATH="/Applications/XAMPP/xamppfiles/bin:$PATH"
  5. ctrl – O then enter to save, then ctrl – X to exit.
  6. restart terminal.
  7. Enter which php
    • If you did it right, it should be the same as the path in #4.

The reason for the mcrypt error is because your mac uses it’s native php, you need to change it to the one xampp has.

Some of useful commands to test php settings in mac lion

>> which PHP  // to see if mac is using default php server or third party php setting [ xamp or mamp ]

>> php –version ¬†// to see the php version

>> php -m  // to see the active modules / extensions

 

 

Reference : http://stackoverflow.com/questions/16830405/laravel-requires-the-mcrypt-php-extension

 

ajax, JavaScript, php

country list arrray php/json


$options["AF"] = "Afghanistan";  
$options["AX"] = "√Öland Islands";  
$options["AL"] = "Albania ";  
$options["DZ"] = "Algeria ";  
$options["AS"] = "American Samoa";  
$options["AD"] = "Andorra";  
$options["AO"] = "Angola ";
$options["AI"] = "Anguilla ";
$options["AQ"] = "Antarctica ";
$options["AG"] = "Antigua and Barbuda ";
$options["AR"] = "Argentina";
$options["AU"] = "Australia ";
$options["AT"] = "Austria ";
$options["AZ"] = "Azerbaijan";
$options["BS"] = "Bahamas ";
$options["BH"] = "Bahrain ";
$options["BD"] = "Bangladesh ";
$options["BB"] = "Barbados ";
$options["BY"] = "Belarus";
$options["BE"] = "Belgium ";
$options["BZ"] = "Belize ";
$options["BJ"] = "Benin ";
$options["BM"] = "Bermuda ";
$options["BT"] = "Bhutan";
$options["BO"] = "Bolivia ";
$options["BA"] = "Bosnia and Herzegovina ";
$options["BW"] = "Botswana ";
$options["BV"] = "Bouvet Island ";
$options["BR"] = "Brazil";
$options["IO"] = "British Indian Ocean Territory ";
$options["BN"] = "Brunei Darussalam ";
$options["BG"] = "Bulgaria ";
$options["BF"] = "Burkina Faso ";
$options["BI"] = "Burundi";
$options["KH"] = "Cambodia ";
$options["CM"] = "Cameroon ";
$options["CA"] = "Canada ";
$options["CV"] = "Cape Verde ";
$options["KY"] = "Cayman Islands ";
$options["CF"] = "Central African Republic ";
$options["TD"] = "Chad ";
$options["CL"] = "Chile ";
$options["CN"] = "China ";
$options["CX"] = "Christmas Island";
$options["CC"] = "Cocos (Keeling) Islands ";
$options["CO"] = "Colombia ";
$options["KM"] = "Comoros ";
$options["CG"] = "Congo ";
$options["CD"] = "Congo, the Democratic Republic of the";
$options["CK"] = "Cook Islands ";
$options["CR"] = "Costa Rica ";
$options["CI"] = "C√īte D'Ivoire";
$options["HR"] = "Croatia ";
$options["CU"] = "Cuba ";
$options["CY"] = "Cyprus ";
$options["CZ"] = "Czech Republic ";
$options["DK"] = "Denmark";
$options["DJ"] = "Djibouti ";
$options["DM"] = "Dominica ";
$options["DO"] = "Dominican Republic ";
$options["EC"] = "Ecuador ";
$options["EG"] = "Egypt";
$options["SV"] = "El Salvador ";
$options["GQ"] = "Equatorial Guinea ";
$options["ER"] = "Eritrea ";
$options["EE"] = "Estonia ";
$options["ET"] = "Ethiopia ";
$options["FK"] = "Falkland Islands (Malvinas) ";
$options["FO"] = "Faroe Islands ";
$options["FJ"] = "Fiji ";
$options["FI"] = "Finland ";
$options["FR"] = "France";
$options["GF"] = "French Guiana ";
$options["PF"] = "French Polynesia ";
$options["TF"] = "French Southern Territories ";
$options["GA"] = "Gabon ";
$options["GM"] = "Gambia ";
$options["GE"] = "Georgia ";
$options["DE"] = "Germany ";
$options["GH"] = "Ghana ";
$options["GI"] = "Gibraltar ";
$options["GR"] = "Greece";
$options["GL"] = "Greenland ";
$options["GD"] = "Grenada ";
$options["GP"] = "Guadeloupe ";
$options["GU"] = "Guam ";
$options["GT"] = "Guatemala";
$options["GG"] = "Guernsey ";
$options["GN"] = "Guinea ";
$options["GW"] = "Guinea-Bissau ";
$options["GY"] = "Guyana ";
$options["HT"] = "Haiti";
$options["HM"] = "Heard Island and Mcdonald Islands ";
$options["VA"] = "Holy See (Vatican City State) ";
$options["HN"] = "Honduras ";
$options["HK"] = "Hong Kong ";
$options["HU"] = "Hungary";
$options["IS"] = "Iceland ";
$options["IN"] = "India ";
$options["ID"] = "Indonesia ";
$options["IR"] = "Iran, Islamic Republic of ";
$options["IQ"] = "Iraq ";
$options["IE"] = "Ireland ";
$options["IM"] = "Isle of Man ";
$options["IL"] = "Israel ";
$options["IT"] = "Italy ";
$options["JM"] = "Jamaica";
$options["JP"] = "Japan ";
$options["JE"] = "Jersey ";
$options["JO"] = "Jordan ";
$options["KZ"] = "Kazakhstan ";
$options["KE"] = "KENYA";
$options["KI"] = "Kiribati ";
$options["KP"] = "Korea, Democratic People's Republic of ";

echo json_encode($options);
php

Five common PHP design patterns


Five common PHP design patterns

 

Jack D Herrington (jherr@pobox.com), Senior Software Engineer, Leverage Software Inc.

 

Summary:¬† Design patterns are just for Java‚ĄĘ architects — at least that’s what you may have been led to believe. In fact, design patterns are useful for everyone. If these tools aren’t exclusive to architecture astronauts, what are they, and why are they useful in PHP applications? This article explains.

Tags for this article:  design, java, patterns, php

 

 

Date:  18 Jul 2006
Level:  Intermediate
Also available in:   Japanese

Activity:  274981 views
Comments:   6 (View | Add comment РSign in)

Average rating 4 stars based on 556 votes Average rating (556 votes)
Rate this article

 

Editor’s note: See the follow-up article “Five more PHP design patterns” to learn about five additional design patterns that you might find useful.

Design patterns were introduced to the software community in¬†Design Patterns, by Erich Gamma, Richard Helm, Ralph Johnson, and John Vlissides (colloquially known as the “gang of four”). The core concept behind design patterns, presented in the introduction, was simple. Over their years of developing software, Gamma et al found certain patterns of solid design emerging, just as architects designing houses and buildings can develop templates for where a bathroom should be located or how a kitchen should be configured. Having those templates, or¬†design patterns, means they can design better buildings more quickly. The same applies to software.

Design patterns not only present useful ways for developing robust software faster but also provide a way of encapsulating large ideas in friendly terms. For example, you can say you’re writing a messaging system to provide for loose coupling, or you can say you’re writing an¬†observer, which is the name of that pattern.

It’s difficult to demonstrate the value of patterns using small examples. They often look like overkill because they really come into play in large code bases. This article can’t show huge applications, so you need to think about ways to apply the principles of the example — and not necessarily this exact code — in your larger applications. That’s not to say that you shouldn’t use patterns in small applications. Most good applications start small and become big, so there is no reason not to start with solid coding practices like these.

Now that you have a sense of what design patterns are and why they’re useful, it’s time to jump into five common patterns for PHP V5.

The factory pattern

Many of the design patterns in the original¬†Design Patterns¬†book encourage¬†loose coupling. To understand this concept, it’s easiest to talk about a struggle that many developers go through in large systems. The problem occurs when you change one piece of code and watch as a cascade of breakage happens in other parts of the system — parts you thought were completely unrelated.

The problem is¬†tight coupling. Functions and classes in one part of the system rely too heavily on behaviors and structures in other functions and classes in other parts of the system. You need a set of patterns that lets these classes talk with each other, but you don’t want to tie them together so heavily that they become interlocked.

In large systems, lots of code relies on a few key classes. Difficulties can arise when you need to change those classes. For example, suppose you have a User class that reads from a file. You want to change it to a different class that reads from the database, but all the code references the original class that reads from a file. This is where the factory pattern comes in handy.

The factory pattern is a class that has some methods that create objects for you. Instead of using new directly, you use the factory class to create objects. That way, if you want to change the types of objects created, you can change just the factory. All the code that uses the factory changes automatically.

Listing 1 shows an example of a factory class. The server side of the equation comes in two pieces: the database, and a set of PHP pages that let you add feeds, request the list of feeds, and get the article associated with a particular feed.
Listing 1. Factory1.php

                
<?php
interface IUser
{
  function getName();
}

class User implements IUser
{
  public function __construct( $id ) { }

  public function getName()
  {
    return "Jack";
  }
}

class UserFactory
{
  public static function Create( $id )
  {
    return new User( $id );
  }
}

$uo = UserFactory::Create( 1 );
echo( $uo->getName()."\n" );
?>

An interface called IUser defines what a user object should do. The implementation of IUser is called User, and a factory class called UserFactory creates IUser objects. This relationship is shown as UML in Figure 1.
Figure 1. The factory class and its related IUser interface and user class
The factory class and its related IUser interface and user class

If you run this code on the command line using the php interpreter, you get this result:

% php factory1.php 
Jack
%

The test code asks the factory for a User object and prints the result of the getName method.

A variation of the factory pattern uses factory methods. These public static methods in the class construct objects of that type. This approach is useful when creating an object of this type is nontrivial. For example, suppose you need to first create the object and then set many attributes. This version of the factory pattern encapsulates that process in a single location so that the complex initialization code isn’t copied and pasted all over the code base.

Listing 2 shows an example of using factory methods.
Listing 2. Factory2.php

                
<?php
interface IUser
{
  function getName();
}

class User implements IUser
{
  public static function Load( $id ) 
  {
        return new User( $id );
  }

  public static function Create( ) 
  {
        return new User( null );
  }

  public function __construct( $id ) { }

  public function getName()
  {
    return "Jack";
  }
}

$uo = User::Load( 1 );
echo( $uo->getName()."\n" );
?>

This code is much simpler. It has only one interface, IUser, and one class called User that implements the interface. The Userclass has two static methods that create the object. This relationship is shown in UML in Figure 2.
Figure 2. The IUser interface and the user class with factory methods
The IUser interface and the user class with factory methods

Running the script on the command line yields the same result as the code in Listing 1, as shown here:

% php factory2.php 
Jack
%

As stated, sometimes such patterns can seem like overkill in small situations. Nevertheless, it’s still good to learn solid coding forms like these for use in any size of project.

The singleton pattern

Some application resources are¬†exclusive¬†in that there is one and only one of this type of resource. For example, the connection to a database through the database handle is exclusive. You want to share the database handle in an application because it’s an overhead to keep opening and closing connections, particularly during a single page fetch.

The singleton pattern covers this need. An object is a singleton if the application can include one and only one of that object at a time. The code in Listing 3 shows a database connection singleton in PHP V5.
Listing 3. Singleton.php

                
<?php
require_once("DB.php");

class DatabaseConnection
{
  public static function get()
  {
    static $db = null;
    if ( $db == null )
      $db = new DatabaseConnection();
    return $db;
  }

  private $_handle = null;

  private function __construct()
  {
    $dsn = 'mysql://root:password@localhost/photos';
    $this->_handle =& DB::Connect( $dsn, array() );
  }

  public function handle()
  {
    return $this->_handle;
  }
}

print( "Handle = ".DatabaseConnection::get()->handle()."\n" );
print( "Handle = ".DatabaseConnection::get()->handle()."\n" );
?>

This code shows a single class called¬†DatabaseConnection. You can’t create your own¬†DatabaseConnection¬†because the constructor is private. But you can get the one and only one¬†DatabaseConnection¬†object using the static¬†get¬†method. The UML for this code is shown in Figure 3.
Figure 3. The database connection singleton
The database connection singleton

The proof in the pudding is that the database handle returned by the handle method is the same between two calls. You can see this by running the code on the command line.

% php singleton.php 
Handle = Object id #3
Handle = Object id #3
%

The two handles returned are the same object. If you use the database connection singleton across the application, you reuse the same handle everywhere.

You could use a global variable to store the database handle, but that approach only works for small applications. In larger applications, avoid globals, and go with objects and methods to get access to resources.

The observer pattern

The observer pattern gives you another way to avoid tight coupling between components. This pattern is simple: One object makes itself observable by adding a method that allows another object, the¬†observer, to register itself. When the observable object changes, it sends a message to the registered observers. What those observers do with that information isn’t relevant or important to the observable object. The result is a way for objects to talk with each other without necessarily understanding why.

A simple example is a list of users in a system. The code in Listing 4 shows a user list that sends out a message when users are added. This list is watched by a logging observer that puts out a message when a user is added.
Listing 4. Observer.php

                
<?php
interface IObserver
{
  function onChanged( $sender, $args );
}

interface IObservable
{
  function addObserver( $observer );
}

class UserList implements IObservable
{
  private $_observers = array();

  public function addCustomer( $name )
  {
    foreach( $this->_observers as $obs )
      $obs->onChanged( $this, $name );
  }

  public function addObserver( $observer )
  {
    $this->_observers []= $observer;
  }
}

class UserListLogger implements IObserver
{
  public function onChanged( $sender, $args )
  {
    echo( "'$args' added to user list\n" );
  }
}

$ul = new UserList();
$ul->addObserver( new UserListLogger() );
$ul->addCustomer( "Jack" );
?>

This code defines four elements: two interfaces and two classes. The IObservable interface defines an object that can be observed, and the UserList implements that interface to register itself as observable. The IObserver list defines what it takes to be an observer, and the UserListLogger implements that IObserver interface. This is shown in the UML in Figure 4.
Figure 4. The observable user list and the user list event logger
The observable user list and the user list event logger

If you run this on the command line, you see this output:

% php observer.php 
'Jack' added to user list
%

The test code creates a UserList and adds the UserListLogger observer to it. Then the code adds a customer, and theUserListLogger is notified of that change.

It’s critical to realize that the¬†UserList¬†doesn’t know what the logger is going to do. There could be one or more listeners that do other things. For example, you may have an observer that sends a message to the new user, welcoming him to the system. The value of this approach is that the¬†UserList¬†is ignorant of all the objects depending on it; it focuses on its job of maintaining the user list and sending out messages when the list changes.

This pattern isn’t limited to objects in memory. It’s the underpinning of the database-driven message queuing systems used in larger applications.

The chain-of-command pattern

Building on the loose-coupling theme, the chain-of-command pattern routes a message, command, request, or whatever you like through a set of handlers. Each handler decides for itself whether it can handle the request. If it can, the request is handled, and the process stops. You can add or remove handlers from the system without influencing other handlers. Listing 5 shows an example of this pattern.
Listing 5. Chain.php

                
<?php
interface ICommand
{
  function onCommand( $name, $args );
}

class CommandChain
{
  private $_commands = array();

  public function addCommand( $cmd )
  {
    $this->_commands []= $cmd;
  }

  public function runCommand( $name, $args )
  {
    foreach( $this->_commands as $cmd )
    {
      if ( $cmd->onCommand( $name, $args ) )
        return;
    }
  }
}

class UserCommand implements ICommand
{
  public function onCommand( $name, $args )
  {
    if ( $name != 'addUser' ) return false;
    echo( "UserCommand handling 'addUser'\n" );
    return true;
  }
}

class MailCommand implements ICommand
{
  public function onCommand( $name, $args )
  {
    if ( $name != 'mail' ) return false;
    echo( "MailCommand handling 'mail'\n" );
    return true;
  }
}

$cc = new CommandChain();
$cc->addCommand( new UserCommand() );
$cc->addCommand( new MailCommand() );
$cc->runCommand( 'addUser', null );
$cc->runCommand( 'mail', null );
?>

This code defines a¬†CommandChain¬†class that maintains a list of¬†ICommand¬†objects. Two classes implement the¬†ICommandinterface — one that responds to requests for mail and another that responds to adding users. The UML is shows in Figure 5.
Figure 5. The command chain and its related commands
The command chain and its related commands

If you run the script, which contains some test code, you see the following output:

% php chain.php 
UserCommand handling 'addUser'
MailCommand handling 'mail'
%

The code first creates a CommandChain object and adds instances of the two command objects to it. It then runs two commands to see who responds to those commands. If the name of the command matches either UserCommand or MailCommand, the code falls through and nothing happens.

The chain-of-command pattern can be valuable in creating an extensible architecture for processing requests, which can be applied to many problems.

The strategy pattern

The last design pattern we will cover is the¬†strategy¬†pattern. In this pattern, algorithms are extracted from complex classes so they can be replaced easily. For example, the strategy pattern is an option if you want to change the way pages are ranked in a search engine. Think about a search engine in several parts — one that iterates through the pages, one that ranks each page, and another that orders the results based on the rank. In a complex example, all those parts would be in the same class. Using the strategy pattern, you take the ranking portion and put it into another class so you can change how pages are ranked without interfering with the rest of the search engine code.

As a simpler example, Listing 6 shows a user list class that provides a method for finding a set of users based on a plug-and-play set of strategies.
Listing 6. Strategy.php

                
<?php
interface IStrategy
{
  function filter( $record );
}

class FindAfterStrategy implements IStrategy
{
  private $_name;

  public function __construct( $name )
  {
    $this->_name = $name;
  }

  public function filter( $record )
  {
    return strcmp( $this->_name, $record ) <= 0;
  }
}

class RandomStrategy implements IStrategy
{
  public function filter( $record )
  {
    return rand( 0, 1 ) >= 0.5;
  }
}

class UserList
{
  private $_list = array();

  public function __construct( $names )
  {
    if ( $names != null )
    {
      foreach( $names as $name )
      {
        $this->_list []= $name;
      }
    }
  }

  public function add( $name )
  {
    $this->_list []= $name;
  }

  public function find( $filter )
  {
    $recs = array();
    foreach( $this->_list as $user )
    {
      if ( $filter->filter( $user ) )
        $recs []= $user;
    }
    return $recs;
  }
}

$ul = new UserList( array( "Andy", "Jack", "Lori", "Megan" ) );
$f1 = $ul->find( new FindAfterStrategy( "J" ) );
print_r( $f1 );

$f2 = $ul->find( new RandomStrategy() );
print_r( $f2 );
?>

The UML for this code is shown in Figure 6.
Figure 6. The user list and the strategies for selecting users
The user list and the strategies for selecting users

The UserList class is a wrapper around an array of names. It implements a find method that takes one of several strategies for selecting a subset of those names. Those strategies are defined by the IStrategy interface, which has two implementations: One chooses users randomly and the other chooses all the names after a specified name. When you run the test code, you get the following output:

% php strategy.php 
Array
(
    [0] => Jack
    [1] => Lori
    [2] => Megan
)
Array
(
    [0] => Andy
    [1] => Megan
)
%

The test code runs the same user lists against two strategies and shows the results. In the first case, the strategy looks for any name that sorts after J, so you get Jack, Lori, and Megan. The second strategy picks names randomly and yields different results every time. In this case, the results are Andy and Megan.

The strategy pattern is great for complex data-management systems or data-processing systems that need a lot of flexibility in how data is filtered, searched, or processed.

Conclusions

These are just a few of the most common design patterns used in PHP applications. Many more are demonstrated in the¬†Design Patterns¬†book. Don’t be put off by the mystique of architecture. Patterns are great ideas you can use in any programming language and at any skill level.
Resources

Learn

  • Wikipedia has a¬†good article¬†on design patterns.
  • The C2 Wiki is another good place to look for information about design patterns like¬†observer,¬†singleton, and more.
  • The original¬†Design Patterns¬†book is a must-read for any engineer.
  • O’Reilly’s¬†Head First Design Patterns¬†is a lighter-weight way to get into design patterns.
  • The book¬†PHP Hacks¬†has several hacks for design patterns that extend beyond the examples shown here.
  • See the developerWorks article “Five more PHP design patterns” to learn about five additional design patterns that you might find useful.
  • PHP.net¬†is the central resource for PHP developers.
  • Check out the “Recommended PHP reading list.”
  • Browse all the¬†PHP content¬†on developerWorks.
  • Expand your PHP skills by checking out IBM developerWorks’¬†PHP project resources.
  • To listen to interesting interviews and discussions for software developers, check out¬†developerWorks podcasts.
  • Using a database with PHP? Check out the¬†Zend Core for IBM, a seamless, out-of-the-box, easy-to-install PHP development and production environment that supports IBM DB2 V9.
  • Stay current with developerWorks’¬†Technical events and webcasts.
  • Check out upcoming conferences, trade shows, webcasts, and other¬†Events¬†around the world that are of interest to IBM open source developers.
  • Visit the developerWorks¬†Open source zone¬†for extensive how-to information, tools, and project updates to help you develop with open source technologies and use them with IBM’s products.
  • Watch and learn about IBM and open source technologies and product functions with the no-cost¬†developerWorks On demand demos.

Get products and technologies

  • Innovate your next open source development project with¬†IBM trial software, available for download or on DVD.
  • Download¬†IBM product evaluation versions, and get your hands on application development tools and middleware products from DB2¬ģ, Lotus¬ģ, Rational¬ģ, Tivoli¬ģ, and WebSphere¬ģ.

Discuss

About the author

A senior software engineer with more than 20 years of experience, Jack Herrington is the author of three books: Code Generation in Action, Podcasting Hacks , and PHP Hacks . He is also the author of more than 30 articles.

php

PHP Simple HTML DOM Parser


URL : http://simplehtmldom.sourceforge.net/
  • A HTML DOM parser written in PHP5+ let you manipulate HTML in a very easy way!
  • Require PHP 5+.
  • Supports invalid HTML.
  • Find tags on an HTML page with selectors just like jQuery.
  • Extract contents from HTML in a single line.

Download & Documents

// Create DOM from URL or file
$html = file_get_html(‘http://www.google.com/&#8217;);

// Find all images
foreach($html->find(‘img’) as $element)
echo $element->src . ‘<br>’;

// Find all links
foreach($html->find(‘a’) as $element)
echo $element->href . ‘<br>’;

===================================================

// Create DOM from string
$html = str_get_html(‘<div id=”hello”>Hello</div><div id=”world”>World</div>’);

$html->find(‘div’, 1)->class = ‘bar’;

$html->find(‘div[id=hello]’, 0)->innertext = ‘foo’;

echo $html;¬†// Output: <div id=”hello”>foo</div><div id=”world” class=”bar”>World</div>

=============================================
// Dump contents (without tags) from HTML
echo file_get_html(‘http://www.google.com/&#8217;)->plaintext;

===========================================

// Create DOM from URL
$html = file_get_html(‘http://slashdot.org/&#8217;);

// Find all article blocks
foreach($html->find(‘div.article’) as $article) {
$item[‘title’]¬†¬†¬†¬†¬†= $article->find(‘div.title’, 0)->plaintext;
$item[‘intro’]¬†¬†¬†¬†= $article->find(‘div.intro’, 0)->plaintext;
$item[‘details’]¬†= $article->find(‘div.details’, 0)->plaintext;
$articles[] = $item;
}

print_r($articles);

==========================================

Feedback

Author: S.C. Chen (me578022@gmail.com)
Original idea is from Jose Solorzano’s HTML Parser for PHP 4.
Contributions by: Yousuke Kumakura (Attribute Filters)
==============================================================

codeigniter, mysql, php

Exporting your MySQL table data with PHPExcel + CodeIgniter


Exporting your MySQL table data with PHPExcel + CodeIgniter

http://www.facebook.com/plugins/like.php?channel_url=https%3A%2F%2Fs-static.ak.fbcdn.net%2Fconnect%2Fxd_proxy.php%3Fversion%3D3%23cb%3Df195a68dc416bf4%26origin%3Dhttp%253A%252F%252Fwww.dannyherran.com%252Ff100e4067be86da%26relation%3Dparent.parent%26transport%3Dpostmessage&extended_social_context=false&href=http%3A%2F%2Fwww.dannyherran.com%2F2011%2F03%2Fexporting-your-mysql-table-data-with-phpexcel-codeigniter%2F&layout=button_count&locale=en_US&node_type=link&sdk=joey&show_faces=false&width=90 http://platform.twitter.com/widgets/tweet_button.html#_=1323938558067&_version=2&count=horizontal&enableNewSizing=false&id=twitter-widget-0&lang=en&original_referer=http%3A%2F%2Fwww.dannyherran.com%2F2011%2F03%2Fexporting-your-mysql-table-data-with-phpexcel-codeigniter%2F&size=m&text=Exporting%20your%20MySQL%20table%20data%20with%20PHPExcel%20%2B%20CodeIgniter&url=http%3A%2F%2Fwww.dannyherran.com%2F2011%2F03%2Fexporting-your-mysql-table-data-with-phpexcel-codeigniter%2F&via=DannyHerran

 

PHPExcel + CodeIgniter Most of the time my clients need to download data from their database tables. Exporting to CSV is a pain in the rear for users and it leads to confusion (you know the colon and semicolon stuff). Today, I decided to make a very small controller that is portable and efficient for exporting full MySQL tables to Excel 2003 using PHPExcel and CodeIgniter.

First of all, you need PHPExcel which should be installed as a CodeIgniter library. In order to do this, you should follow the steps posted in the CodeIgniter Wiki.

Once you have PHPExcel installed and configured, make a controller exactly like this one:

Code:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
class Table_export extends Controller {
    function __construct()
    {
        parent::Controller();
        // Here you should add some sort of user validation
        // to prevent strangers from pulling your table data
    }
    function index($table_name)
    {
        $query = $this->db->get($table_name);
        if(!$query)
            return false;
        // Starting the PHPExcel library
        $this->load->library('PHPExcel');
        $this->load->library('PHPExcel/IOFactory');
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setTitle("export")->setDescription("none");
        $objPHPExcel->setActiveSheetIndex(0);
        // Field names in the first row
        $fields = $query->list_fields();
        $col = 0;
        foreach ($fields as $field)
        {
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
            $col++;
        }
        // Fetching the table data
        $row = 2;
        foreach($query->result() as $data)
        {
            $col = 0;
            foreach ($fields as $field)
            {
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->$field);
                $col++;
            }
            $row++;
        }
        $objPHPExcel->setActiveSheetIndex(0);
        $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
        // Sending headers to force the user to download the file
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Products_'.date('dMy').'.xls"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
    }
}

Whenever you need to export data from a MySQL table, you just need to call this controller and pass the table name as a parameter, sort of like http://www.yoursite.com/table_export/products. Obviously, you should always add some sort of security measure in order to prevent strangers from pulling all your table information. Just add some session protection to your constructor and you’re set.

mysql, php

Dump MySQL database


<?php
/**
* Dump MySQL database
*
* Here is an inline example:
* <code>
* $connection = @mysql_connect($dbhost,$dbuser,$dbpsw);
* $dumper = new MySQLDump($dbname,'filename.sql',false,false);
* $dumper->doDump();
* </code>
*
* Special thanks to:
* - Andrea Ingaglio <andrea@coders4fun.com> helping in development of all class code
* - Dylan Pugh for precious advices halfing the size of the output file and for helping in debug
*
* @name    MySQLDump
* @author  Daniele Viganò - CreativeFactory.it <daniele.vigano@creativefactory.it>
* @version 2.20 - 02/11/2007
*/
class MySQLDump {
 /**
 * @access private
 */
 var $database = null;
 /**
 * @access private
 */
 var $compress = false;
 /**
 * @access private
 */
 var $hexValue = false;
  /**
 * The output filename
 * @access private
 */
 var $filename = null;
 /**
 * The pointer of the output file
 * @access private
 */
 var $file = null;
 /**
 * @access private
 */
 var $isWritten = false;
 /**
 * Class constructor
 * @param string $db The database name
 * @param string $filepath The file where the dump will be written
 * @param boolean $compress It defines if the output file is compress (gzip) or not
 * @param boolean $hexValue It defines if the outup values are base-16 or not
 */
 function MYSQLDump($db = null, $filepath = 'dump.sql', $compress = false, $hexValue = false){
  $this->compress = $compress;
  if ( !$this->setOutputFile($filepath) )
   return false;
  return $this->setDatabase($db);
 }
 /**
 * Sets the database to work on
 * @param string $db The database name
 */
 function setDatabase($db){
  $this->database = $db;
  if ( !@mysql_select_db($this->database) )
   return false;
  return true;
  }
 /**
 * Returns the database where the class is working on
 * @return string
 */
  function getDatabase(){
  return $this->database;
 }
 /**
 * Sets the output file type (It can be made only if the file hasn't been already written)
 * @param boolean $compress If it's true, the output file will be compressed
 */
 function setCompress($compress){
  if ( $this->isWritten )
   return false;
  $this->compress = $compress;
  $this->openFile($this->filename);
  return true;
  }
 /**
 * Returns if the output file is or not compressed
 * @return boolean
 */
  function getCompress(){
  return $this->compress;
 }
 /**
 * Sets the output file
 * @param string $filepath The file where the dump will be written
 */
 function setOutputFile($filepath){
  if ( $this->isWritten )
   return false;
  $this->filename = $filepath;
  $this->file = $this->openFile($this->filename);
  return $this->file;
  }
  /**
 * Returns the output filename
 * @return string
 */
  function getOutputFile(){
  return $this->filename;
 }
 /**
 * Writes to file the $table's structure
 * @param string $table The table name
 */
  function getTableStructure($table){
  if ( !$this->setDatabase($this->database) )
   return false;
  // Structure Header
  $structure = "-- \n";
  $structure .= "-- Table structure for table `{$table}` \n";
  $structure .= "-- \n\n";
  // Dump Structure
  $structure .= 'DROP TABLE IF EXISTS `'.$table.'`;'."\n";
  $structure .= "CREATE TABLE `".$table."` (\n";
  $records = @mysql_query('SHOW FIELDS FROM `'.$table.'`');
  if ( @mysql_num_rows($records) == 0 )
   return false;
  while ( $record = mysql_fetch_assoc($records) ) {
   $structure .= '`'.$record['Field'].'` '.$record['Type'];
   if ( !empty($record['Default']) )
    $structure .= ' DEFAULT \''.$record['Default'].'\'';
   if ( @strcmp($record['Null'],'YES') != 0 )
    $structure .= ' NOT NULL';
   if ( !empty($record['Extra']) )
    $structure .= ' '.$record['Extra'];
   $structure .= ",\n";
  }
  $structure = @ereg_replace(",\n$", null, $structure);
  // Save all Column Indexes
  $structure .= $this->getSqlKeysTable($table);
  $structure .= "\n)";
  //Save table engine
  $records = @mysql_query("SHOW TABLE STATUS LIKE '".$table."'");
  echo $query;
  if ( $record = @mysql_fetch_assoc($records) ) {
   if ( !empty($record['Engine']) )
    $structure .= ' ENGINE='.$record['Engine'];
   if ( !empty($record['Auto_increment']) )
    $structure .= ' AUTO_INCREMENT='.$record['Auto_increment'];
  }
  $structure .= ";\n\n-- --------------------------------------------------------\n\n";
  $this->saveToFile($this->file,$structure);
 }
 /**
 * Writes to file the $table's data
 * @param string $table The table name
 * @param boolean $hexValue It defines if the output is base 16 or not
 */
 function getTableData($table,$hexValue = true) {
  if ( !$this->setDatabase($this->database) )
   return false;
  // Header
  $data = "-- \n";
  $data .= "-- Dumping data for table `$table` \n";
  $data .= "-- \n\n";
  $records = mysql_query('SHOW FIELDS FROM `'.$table.'`');
  $num_fields = @mysql_num_rows($records);
  if ( $num_fields == 0 )
   return false;
  // Field names
  $selectStatement = "SELECT ";
  $insertStatement = "INSERT INTO `$table` (";
  $hexField = array();
  for ($x = 0; $x < $num_fields; $x++) {
   $record = @mysql_fetch_assoc($records);
   if ( ($hexValue) && ($this->isTextValue($record['Type'])) ) {
    $selectStatement .= 'HEX(`'.$record['Field'].'`)';
    $hexField [$x] = true;
   }
   else
    $selectStatement .= '`'.$record['Field'].'`';
   $insertStatement .= '`'.$record['Field'].'`';
   $insertStatement .= ", ";
   $selectStatement .= ", ";
  }
  $insertStatement = @substr($insertStatement,0,-2).') VALUES';
  $selectStatement = @substr($selectStatement,0,-2).' FROM `'.$table.'`';
  $records = @mysql_query($selectStatement);
  $num_rows = @mysql_num_rows($records);
  $num_fields = @mysql_num_fields($records);
  // Dump data
  if ( $num_rows > 0 ) {
   $data .= $insertStatement;
   for ($i = 0; $i < $num_rows; $i++) {
    $record = @mysql_fetch_assoc($records);
    $data .= ' (';
    for ($j = 0; $j < $num_fields; $j++) {
     $field_name = @mysql_field_name($records, $j);
     if ( $hexField[$j] && (@strlen($record[$field_name]) > 0) )
      $data .= "0x".$record[$field_name];
     else
      $data .= "'".@str_replace('\"','"',@mysql_escape_string($record[$field_name]))."'";
     $data .= ',';
    }
    $data = @substr($data,0,-1).")";
    $data .= ( $i < ($num_rows-1) ) ? ',' : ';';
    $data .= "\n";
    //if data in greather than 1MB save
    if (strlen($data) > 1048576) {
     $this->saveToFile($this->file,$data);
     $data = '';
    }
   }
   $data .= "\n-- --------------------------------------------------------\n\n";
   $this->saveToFile($this->file,$data);
  }
 }
  /**
 * Writes to file all the selected database tables structure
 * @return boolean
 */
 function getDatabaseStructure(){
  $records = @mysql_query('SHOW TABLES');
  if ( @mysql_num_rows($records) == 0 )
   return false;
  while ( $record = @mysql_fetch_row($records) ) {
   $structure .= $this->getTableStructure($record[0]);
  }
  return true;
  }
 /**
 * Writes to file all the selected database tables data
 * @param boolean $hexValue It defines if the output is base-16 or not
 */
 function getDatabaseData($hexValue = true){
  $records = @mysql_query('SHOW TABLES');
  if ( @mysql_num_rows($records) == 0 )
   return false;
  while ( $record = @mysql_fetch_row($records) ) {
   $this->getTableData($record[0],$hexValue);
  }
  }
 /**
 * Writes to file the selected database dump
 */
 function doDump() {
  $this->saveToFile($this->file,"SET FOREIGN_KEY_CHECKS = 0;\n\n");
  $this->getDatabaseStructure();
  $this->getDatabaseData($this->hexValue);
  $this->saveToFile($this->file,"SET FOREIGN_KEY_CHECKS = 1;\n\n");
  $this->closeFile($this->file);
  return true;
 }
 
 /**
 * @deprecated Look at the doDump() method
 */
 function writeDump($filename) {
  if ( !$this->setOutputFile($filename) )
   return false;
  $this->doDump();
    $this->closeFile($this->file);
    return true;
 }
 /**
 * @access private
 */
 function getSqlKeysTable ($table) {
  $primary = "";
  unset($unique);
  unset($index);
  unset($fulltext);
  $results = mysql_query("SHOW KEYS FROM `{$table}`");
  if ( @mysql_num_rows($results) == 0 )
   return false;
  while($row = mysql_fetch_object($results)) {
   if (($row->Key_name == 'PRIMARY') AND ($row->Index_type == 'BTREE')) {
    if ( $primary == "" )
     $primary = "  PRIMARY KEY  (`{$row->Column_name}`";
    else
     $primary .= ", `{$row->Column_name}`";
   }
   if (($row->Key_name != 'PRIMARY') AND ($row->Non_unique == '0') AND ($row->Index_type == 'BTREE')) {
    if ( (!is_array($unique)) OR ($unique[$row->Key_name]=="") )
     $unique[$row->Key_name] = "  UNIQUE KEY `{$row->Key_name}` (`{$row->Column_name}`";
    else
     $unique[$row->Key_name] .= ", `{$row->Column_name}`";
   }
   if (($row->Key_name != 'PRIMARY') AND ($row->Non_unique == '1') AND ($row->Index_type == 'BTREE')) {
    if ( (!is_array($index)) OR ($index[$row->Key_name]=="") )
     $index[$row->Key_name] = "  KEY `{$row->Key_name}` (`{$row->Column_name}`";
    else
     $index[$row->Key_name] .= ", `{$row->Column_name}`";
   }
   if (($row->Key_name != 'PRIMARY') AND ($row->Non_unique == '1') AND ($row->Index_type == 'FULLTEXT')) {
    if ( (!is_array($fulltext)) OR ($fulltext[$row->Key_name]=="") )
     $fulltext[$row->Key_name] = "  FULLTEXT `{$row->Key_name}` (`{$row->Column_name}`";
    else
     $fulltext[$row->Key_name] .= ", `{$row->Column_name}`";
   }
  }
  $sqlKeyStatement = '';
  // generate primary, unique, key and fulltext
  if ( $primary != "" ) {
   $sqlKeyStatement .= ",\n";
   $primary .= ")";
   $sqlKeyStatement .= $primary;
  }
  if (is_array($unique)) {
   foreach ($unique as $keyName => $keyDef) {
    $sqlKeyStatement .= ",\n";
    $keyDef .= ")";
    $sqlKeyStatement .= $keyDef;
   }
  }
  if (is_array($index)) {
   foreach ($index as $keyName => $keyDef) {
    $sqlKeyStatement .= ",\n";
    $keyDef .= ")";
    $sqlKeyStatement .= $keyDef;
   }
  }
  if (is_array($fulltext)) {
   foreach ($fulltext as $keyName => $keyDef) {
    $sqlKeyStatement .= ",\n";
    $keyDef .= ")";
    $sqlKeyStatement .= $keyDef;
   }
  }
  return $sqlKeyStatement;
 }
  /**
 * @access private
 */
 function isTextValue($field_type) {
  switch ($field_type) {
   case "tinytext":
   case "text":
   case "mediumtext":
   case "longtext":
   case "binary":
   case "varbinary":
   case "tinyblob":
   case "blob":
   case "mediumblob":
   case "longblob":
    return True;
    break;
   default:
    return False;
  }
 }
 
 /**
 * @access private
 */
 function openFile($filename) {
  $file = false;
  if ( $this->compress )
   $file = @gzopen($filename, "w9");
  else
   $file = @fopen($filename, "w");
  return $file;
 }
  /**
 * @access private
 */
 function saveToFile($file, $data) {
  if ( $this->compress )
   @gzwrite($file, $data);
  else
   @fwrite($file, $data);
  $this->isWritten = true;
 }
  /**
 * @access private
 */
 function closeFile($file) {
  if ( $this->compress )
   @gzclose($file);
  else
   @fclose($file);
 }
}
?>