Magento Template Path Hints For Admin side

If you love template path hints in Magento for quickly figuring out which template file or block you need to edit or override and have a requirement for some admin side coding, you are going to love this.

You might not have thought it was possible to enable template path hints in admin, but it is!

Just run this query:

SQL:

INSERT INTO core_config_data (scope, scope_id, path, value)
VALUES ('default', 0, 'dev/debug/template_hints', 1),
('default', 0, 'dev/debug/template_hints_blocks', 1);

To disable them again, run this query

SQL:

UPDATE core_config_data SET value = 0 WHERE scope = 'default' AND scope_id = 0 AND path ='dev/debug/template_hints'

To enable again run this query

SQL:

UPDATE core_config_data SET value = 1 WHERE scope = 'default' AND scope_id = 0 AND path ='dev/debug/template_hints'

Back Up and Restore a MySQL Database

Back up From the Command Line (using mysqldump)

If you have shell or telnet access to your web server, you can backup your MySQL data by using the mysqldump command. This command connects to the MySQL server and creates an SQL dump file. The dump file contains the SQL statements necessary to re-create the database. Here is the proper syntax:

$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

• [uname] Your database username


• [pass] The password for your database (note there is no space between -p and the password)


• [dbname] The name of your database


• [backupfile.sql] The filename for your database backup


• [--opt] The mysqldump option

For example, to backup a database named 'Tutorials' with the username 'root' and with no password to a file tut_backup.sql, you should accomplish this command:

$ mysqldump -u root -p Tutorials > tut_backup.sql

This command will backup the 'Tutorials' database into a file called tut_backup.sql which will contain all the SQL statements needed to re-create the database.

With mysqldump command you can specify certain tables of your database you want to backup. For example, to back up only php_tutorials and asp_tutorials tables from the 'Tutorials' database accomplish the command below. Each table name has to be separated by space.

$ mysqldump -u root -p Tutorials php_tutorials asp_tutorials > tut_backup.sql

Sometimes it is necessary to back up more that one database at once. In this case you can use the --database option followed by the list of databases you would like to backup. Each database name has to be separated by space.

$ mysqldump -u root -p --databases Tutorials Articles Comments > content_backup.sql

If you want to back up all the databases in the server at one time you should use the --all-databases option. It tells MySQL to dump all the databases it has in storage.

$ mysqldump -u root -p --all-databases > alldb_backup.sql

The mysqldump command has also some other useful options:

--add-drop-table: Tells MySQL to add a DROP TABLE statement before each CREATE TABLE in the dump.

--no-data: Dumps only the database structure, not the contents.

--add-locks: Adds the LOCK TABLES and UNLOCK TABLES statements you can see in the dump file.

The mysqldump command has advantages and disadvantages. The advantages of using mysqldump are that it is simple to use and it takes care of table locking issues for you. The disadvantage is that the command locks tables. If the size of your tables is very big mysqldump can lock out users for a long period of time.

Back up your MySQL Database with Compress

If your mysql database is very big, you might want to compress the output of mysqldump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.

$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

If you want to extract the .gz file, use the command below:

$ gunzip [backupfile.sql.gz]

Restoring your MySQL Database

Above we backup the Tutorials database into tut_backup.sql file. To re-create the Tutorials database you should follow two steps:

• Create an appropriately named database on the target machine


• Load the file using the mysql command:

$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

Have a look how you can restore your tut_backup.sql file to the Tutorials database.

$ mysql -u root -p Tutorials < tut_backup.sql

To restore compressed backup files you can do the following:

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

If you need to restore a database that already exists, you'll need to use mysqlimport command. The syntax for mysqlimport is as follows:

mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

Optimize MySQL response time Techniques

High loaded / Heavy Traffic website can get slow to respond when a lot of different visitors visit sites querying the same mysql database server, making it slow to respond.

There is many ways you can improve mysql server response time:

- by modifying the cache size
- stopping dns resolution ....

Let's see how to implement that.

Sometime it may happen when we got troubles with our databases system. The mysql servers were slow to respond, but when we were logging into those machines, the load was fine, there were quite a few queries going on, but mysql didn't report it was overwhelmed.

1. Disable DNS Hostname Lookup

After seeking out the reason why the traffic wasn't going flawlessly, we determine that the mysql server was doing loads of name resolution queries!!!! What for? Why would that machine to a hostname resolution when only local network machines connect to it.

Seeking out in mysqld manual page, we found that this could be disabled by adding the --skip-name-resolve switch.

Under debian based system, such as ubuntu, knoppix ... and on most linux distribution, mysql configuration files are located in /etc/mysql/my.cnf.

In order to apply the --skip-name-resolve switch when you start mysqld, simply add:

[mysqld]
.....
......
skip-name-resolve

NOTE: When this option is activated, you can only use IP numbers in the MySQL Grant table.

With DNS hostname resolution:

date; mysql -u root -h 192.168.1.4 ; date
Fri Jul 21 23:56:58 CEST 2006
ERROR 1130 (00000): Host '192.168.1.3' is not allowed to connect to this MySQL server

Fri Jul 21 23:57:00 CEST 2006

it take 2-3 seconds before the server reply that the client IP is not allowed to connect.

Once DNS hostname lookup is disabled:

date; mysql -u root -h 192.168.1.4 ; date

Fri Jul 21 23:56:37 CEST 2006

ERROR 1130 (00000): Host '192.168.1.3' is not allowed to connect to this MySQL server

Fri Jul 21 23:56:37 CEST 2006

The server is replying instantly.

2. Activate Query Cache

After we resolved that issue, we started seeing the database server load increasing, the response time was good after the previous change, but now, we had to lighten a bit the mysql database server's load.

By checking the Query cache memory:

mysql> SHOW STATUS LIKE 'Qcache%';

we could see that no query cache memory was left. It was neccessary to increase the query cache size.

To get an overview of your query_cache variables state, use the following syntax:

mysql> SHOW VARIABLES LIKE '%query_cache%';

You need to have the query cache enabled in the first place (have_query_cache | YES) and make sure that query_cache_type is set to ON. This is usually activated by default on most linux distribution.

Now, you can increase the query cache size (let say you want 50M) using:

mysql> SET GLOBAL query_cache_size = 52428800;

If you want this setting to be kept when restarting mysql, add:

[mysqld]
...
...
query_cache_size = 52428800;

query_cache_type = 1

3. Summary:

After doing those changes, there were much more queries resolved from the cache, the effect was that the server was responding quickly without calculating too much has most of the queries where cached.

Protecting Script using SQL injection For MySQL with PHP

If you are running a dynamic website coded in PHP, chances are you'll be using
MySQL for storing content or information.

MySQL is very well suited for running anything from small personal websites to large corporate
systems, as it is both simple to use and scalable.  However, it is easy to overlook potential
security problems, especially if you don't have much experience.

Example

For instance, you may have a login script for a secure page of your site:



<?php

    # Database connection code here



    $result=mysql_query('select * from users where

    user="'.$_POST['username'].'" and pass="'.$_POST['password'].'"');



    if(mysql_num_rows($result)==0):

        # Username or password incorrect

        exit;

    endif;



    # Send user protected page

?>



So for instance, if somebody sent " or ""=" for the username and the password,
the SQL query sent to the database would read: select * from users where user="" or
""="" and pass="" or ""="", which would allow access to the protected page without a
valid username or password.  This method is called SQL Injection.

Escaping

To prevent this from happening, the data provided by the user need to be 'escaped' - this means
putting backslashes in front of quotes, backslashes and other special characters.

This means that the MySQL engine will recognise that the quotes are part of the string, which
prevents SQL injection.

PHP has a built in function that is intended for escaping strings, called addslashes().

For instance, passing the form data from our example through addslashes would result in select * from users where user="\" or \"\"=\""
and pass="\" or \"\"=\"" being passed to the database, which can
be correctly interpreted by MySQL.

Magic Quotes

PHP has a feature called 'Magic Quotes', which automatically escapes get, post and cookie data, as if
addslashes had been called on them.  The idea of this is to prevent scripts written by
inexperienced coders being vulnerable to SQL injection.

However, there are several problems with this feature:

• Addslashes doesn't escape data exactly right for MySQL databases. (The MySQL function
  MySQL_real_escape_string() should really be used instead)


• Magic quotes can give programmers a false sense of security, and makes scripts completely
  vulnerable if the option is turned off.


• It has the irritating side effect that form inputs used in other parts of scripts have slashes added to
  them, which can be very puzzling for beginners, and adds extra coding to remove them again.

Because of these reasons, magic quotes are turned off by default in PHP 5, although they are on
by default in PHP 4.

Best Practice

To keep your code portable and protected against vulnerabilities whether Magic Quotes is enabled or not,
it is best to use a function similar to the one below:



<?php

    function proper_escape($datastring) {

        # Strip slashes if data has already been escaped by magic quotes

        if(get_magic_quotes_gpc()):

            $datastring=stripslashes($datastring);

        endif;



        # Escape string properly & return

        return mysql_real_escape_string($datastring);

    }

?>



Call this when sending input data to the MySQL database like: proper_escape($_POST['username']).

MySQL Regular Expressions - Part 2

Regular Expressions in MySQL

Introduction



A very interesting and useful capability of MySQL is to incorporate Regular Expressions (regex) in SQL queries. The regular expression support in MySQL is extensive. Let's take a look at using Regular Expressions in queries and the supported metacharacters.



Using Regular Expressions in queries



A simple example of using Regular Expressions in a SQL query would be to select all names from a table that start with 'A'.

Code: SQL

SELECT name FROM employees WHERE name REGEXP '^A'

A slight modification in the above example to look for names starting with 'A' or 'D' or 'F' will look like this.

Code: SQL

SELECT name FROM employees WHERE name REGEXP '^(A|D|F)'

If we want to select all names ending with 'P', then the SQL query goes like this

Code: SQL

SELECT name FROM employees WHERE name REGEXP 'P$'

We can use much complex patterns in our SQL queries, but first let's have a look at various MySQL Regular Expression metacharacters.



Regular Expression Metacharacters



*

Matches zero or more instances of the string preceding it

+

Matches one or more instances of the string preceding it

?

Matches zero or one instances of the string preceding it

.

Matches any single character, except a newline

[xyz]

Matches any of x, y, or z (match one of enclosed characters)

[^xyz]

Matches any character not enclosed

[A-Z]

Matches any uppercase letter

[a-z]

Matches any lowercase letter

[0-9]

Matches any digit

^

Anchors the match from the beginning

$

Anchors the match to the end

|

Separates alternatives

{n,m}

String must occur at least n times, but not more than m times

{n}

String must occur exactly n times

{n,}

String must occur at least n times

[[:<:]]

Matches beginning of words

[[:>:]]

Matches ending of words

[:class:]

match a character class i.e.,



[:alpha:] for letters

[:space:] for whitespace

[:punct:] for punctuation

[:upper:] for upper case letters

Extras



MySQL interprets a backslash (\) character as an escape character. To use a backslash in a regular expression, you must escape it with another backslash (\\).







Whether the Regular Expression match is case sensitive or otherwise is decided by the collation method of the table. If your collation method name ends with ci then the comparison/match is case-insensitive, else if it end in cs then the match is case sensitive.



Examples



Checking only for numbers

Code: SQL

SELECT age FROM employees WHERE age REGEXP '^[0-9]+$'

/* starts, ends and contains numbers */

Contains a specific word, for example the skill PHP in skill sets

Code: SQL

SELECT name FROM employees WHERE skill_sets REGEXP '[[:<:]]php[[:>:]]'

Fetching records where employees have entered their 10-digit mobile number as the contact number.

Code: SQL

SELECT name FROM employees WHERE contact_no REGEXP '^[0-9]{10}$'

For more information refer http://dev.mysql.com/doc/refman/5.1/en/regexp.html

MySQL Regular Expressions - Part 1

A regular expression (regex) is a powerful way of specifying a complex search.

MySQL uses Henry Spencer's implementation of regular
expressions, which is aimed at conformance with POSIX
1003.2. MySQL uses the extended version.

This is a simplistic reference that skips the details. To get more exact
information, see Henry Spencer's regex(7) manual page that is
included in the source distribution. See section C Credits.

A regular expression describes a set of strings. The simplest regexp is
one that has no special characters in it. For example, the regexp
hello matches hello and nothing else.

Non-trivial regular expressions use certain special constructs so that
they can match more than one string. For example, the regexp
hello|word matches either the string hello or the string
word.

As a more complex example, the regexp B[an]*s matches any of the
strings Bananas, Baaaaas, Bs, and any other string
starting with a B, ending with an s, and containing any
number of a or n characters in between.

A regular expression may use any of the following special
characters/constructs:

^

Match the beginning of a string.

 
mysql> SELECT "fo\nfo" REGEXP "^fo$";           -> 0
mysql> SELECT "fofo" REGEXP "^fo";              -> 1

$

Match the end of a string.

 
mysql> SELECT "fo\no" REGEXP "^fo\no$";         -> 1
mysql> SELECT "fo\no" REGEXP "^fo$";            -> 0

.

Match any character (including newline).

 
mysql> SELECT "fofo" REGEXP "^f.*";             -> 1
mysql> SELECT "fo\nfo" REGEXP "^f.*";           -> 1

a*

Match any sequence of zero or more a characters.

 
mysql> SELECT "Ban" REGEXP "^Ba*n";             -> 1
mysql> SELECT "Baaan" REGEXP "^Ba*n";           -> 1
mysql> SELECT "Bn" REGEXP "^Ba*n";              -> 1

a+

Match any sequence of one or more a characters.

 
mysql> SELECT "Ban" REGEXP "^Ba+n";             -> 1
mysql> SELECT "Bn" REGEXP "^Ba+n";              -> 0

a?

Match either zero or one a character.

 
mysql> SELECT "Bn" REGEXP "^Ba?n";              -> 1
mysql> SELECT "Ban" REGEXP "^Ba?n";             -> 1
mysql> SELECT "Baan" REGEXP "^Ba?n";            -> 0

de|abc

Match either of the sequences de or abc.

 
mysql> SELECT "pi" REGEXP "pi|apa";             -> 1
mysql> SELECT "axe" REGEXP "pi|apa";            -> 0
mysql> SELECT "apa" REGEXP "pi|apa";            -> 1
mysql> SELECT "apa" REGEXP "^(pi|apa)$";        -> 1
mysql> SELECT "pi" REGEXP "^(pi|apa)$";         -> 1
mysql> SELECT "pix" REGEXP "^(pi|apa)$";        -> 0

(abc)*

Match zero or more instances of the sequence abc.

 
mysql> SELECT "pi" REGEXP "^(pi)*$";            -> 1
mysql> SELECT "pip" REGEXP "^(pi)*$";           -> 0
mysql> SELECT "pipi" REGEXP "^(pi)*$";          -> 1

{1}

{2,3}

The is a more general way of writing regexps that match many
occurrences of the previous atom.

a*

Can be written as a{0,}.

a+

Can be written as a{1,}.

a?

Can be written as a{0,1}.

To be more precise, an atom followed by a bound containing one integer
i and no comma matches a sequence of exactly i matches of
the atom. An atom followed by a bound containing one integer i
and a comma matches a sequence of i or more matches of the atom.
An atom followed by a bound containing two integers i and
j matches a sequence of i through j (inclusive)
matches of the atom.

Both arguments must be in the range from 0 to RE_DUP_MAX
(default 255), inclusive. If there are two arguments, the second must be
greater than or equal to the first.

[a-dX]

[^a-dX]

Matches
any character which is (or is not, if ^ is used) either a, b,
c, d or X. To include a literal ] character,
it must immediately follow the opening bracket [. To include a
literal - character, it must be written first or last. So
[0-9] matches any decimal digit. Any character that does not have
a defined meaning inside a [] pair has no special meaning and
matches only itself.

 
mysql> SELECT "aXbc" REGEXP "[a-dXYZ]";         -> 1
mysql> SELECT "aXbc" REGEXP "^[a-dXYZ]$";       -> 0
mysql> SELECT "aXbc" REGEXP "^[a-dXYZ]+$";      -> 1
mysql> SELECT "aXbc" REGEXP "^[^a-dXYZ]+$";     -> 0
mysql> SELECT "gheis" REGEXP "^[^a-dXYZ]+$";    -> 1
mysql> SELECT "gheisa" REGEXP "^[^a-dXYZ]+$";   -> 0

[[.characters.]]

The sequence of characters of that collating element. The sequence is a
single element of the bracket expression's list. A bracket expression
containing a multi-character collating element can thus match more than
one character, for example, if the collating sequence includes a ch
collating element, then the regular expression [[.ch.]]*c matches the
first five characters of chchcc.

[=character_class=]

An equivalence class, standing for the sequences of characters of all
collating elements equivalent to that one, including itself.

For example, if o and (+) are the members of an
equivalence class, then [[=o=]], [[=(+)=]], and
[o(+)] are all synonymous. An equivalence class may not be an
endpoint of a range.

[:character_class:]

Within a bracket expression, the name of a character class enclosed in
[: and :] stands for the list of all characters belonging
to that class. Standard character class names are:

Name	Name	Name
alnum	digit	punct
alpha	graph	space
blank	lower	upper
cntrl	print	xdigit

These stand for the character classes defined in the ctype(3) manual
page. A locale may provide others. A character class may not be used as an
endpoint of a range.

 
mysql> SELECT "justalnums" REGEXP "[[:alnum:]]+";       -> 1
mysql> SELECT "!!" REGEXP "[[:alnum:]]+";               -> 0

[[:<:]]

[[:>:]]

These match the null string at the beginning and end of a word
respectively. A word is defined as a sequence of word characters which
is neither preceded nor followed by word characters. A word character is
an alnum character (as defined by ctype(3)) or an underscore
(_).

 
mysql> SELECT "a word a" REGEXP "[[:<:]]word[[:>:]]";      -> 1
mysql> SELECT "a xword a" REGEXP "[[:<:]]word[[:>:]]";     -> 0

 
mysql> SELECT "weeknights" REGEXP "^(wee|week)(knights|nights)$"; -> 1

Ebook Of Open Source CMS

Apress.Building.Online.Communities.With.Drupal.phpBB.and.WordPress.Dec.2005


Free Ebook for Apress Building Online Communities With Drupal phpBB and WordPress Dec 2005 Please Download this ebook freely and learn.

Dowanload Link :- Click here




Free e-book Pro Drupal Development (John K. VanDyk and Matt Westgate)


Download Link :- Click Here

Get metadata on MySQL databases

Getting information about databases if essential if you want to write generic and scalable applications. This code shows you how to get information such as all databases on the server, all tables in each database and all field and field info for each table. Even if you do not need to build on this code, you might want to copy the code which prints out all databases, tables and field information plus examples. Its a great way to get an overview of the tables you are working on for a project.

<?

//getting metadata on MySQL databases



//output the structure of a table

$connection_1 = mysql_connect("localhost");

$fields = mysql_list_fields("cmphp","rights");

for($i=0;$i<mysql_num_fields($fields);$i++) {

   echo mysql_field_name($fields,$i)." (".mysql_field_len($fields,$i).") - ".mysql_field_type($fields,$i)."<br>";

}

mysql_close;



//show the structure of ALL tables in ALL databases on the server

$server_connection_1 = mysql_connect("localhost");

$databases = mysql_query("SHOW DATABASES");

while($database = mysql_fetch_row($databases)) {

   echo '<h2>DATABASE: '.$database[0].'</h2>';

   $database_connection_1 = mysql_select_db($database[0]);

   $tables = mysql_query("SHOW TABLES");

   while($table = mysql_fetch_row($tables)){

       echo '<table border="1" cellpadding="5" width="500">';

       echo '<tr><td colspan="3" bgcolor="silver">TABLE: '.$table[0].'</td></tr>';

       $fields = mysql_list_fields($database[0],$table[0]);

       for($i=0;$i<mysql_num_fields($fields);$i++) {

           echo '<tr>';

           echo '<td>'.mysql_field_name($fields,$i)."</td>";

           echo '<td>'.mysql_field_len($fields,$i)."</td>";

           echo '<td>'.mysql_field_type($fields,$i)."</td>";

           echo '</tr>';

       }

       echo '</table><br>';

   }

}

mysql_close;



//show the nice structure of a particular database

$server_connection_1 = mysql_connect("localhost");

$the_database = "cmphp";

echo '<h2>DATABASE: '.$the_database.'</h2>';

$database_connection_1 = mysql_select_db($the_database);

$tables = mysql_query("SHOW TABLES");

while($table = mysql_fetch_row($tables)){

   echo '<table border="1" cellpadding="5" width="600">';

   echo '<tr><td colspan="4" bgcolor="silver"><b>TABLE: '.$table[0].'</b></td></tr>';

   echo '<tr><td bgcolor="silver">NAME</td><td bgcolor="silver">SIZE</td><td bgcolor="silver">TYPE</td><td bgcolor="silver">EXAMPLE</td></tr>';

   $fields = mysql_list_fields($the_database,$table[0]);

   for($i=0;$i<mysql_num_fields($fields);$i++) {

       echo '<tr>';

       echo '<td>'.mysql_field_name($fields,$i)."</td>";

       echo '<td>'.mysql_field_len($fields,$i)."</td>";

       echo '<td>'.mysql_field_type($fields,$i)."</td>";

       $rows = mysql_query("SELECT ".mysql_field_name($fields,$i)." FROM ".$table[0]." LIMIT 1");

       $row = mysql_fetch_array($rows);

       echo '<td bgcolor="eeeeee">'.$row[0].' </td>';

       echo '</tr>';

   }

   echo '</table><br>';

}

mysql_close;

?>

This Article is taken from http://www.developerfusion.co.uk/show/3945/

Code To Generate Excel Documents - Part 2

<?php
/* Excel Query Output
Allie Micka <allie@pajunas.com> 2/13/02
Outputs an excel spreadsheet for a PEAR query contained in the
variable "$result". If the array "$summaryCols" exists, a
summary row will be printed at the end of the query containing
an excel-calculated summary for that column.

This was generated by using MS Office's "Save as HTML" feature in
Excel, which produces what they call "round trip HTML". By changing
or adding content within their markup you can create a valid Excel
document.
*/
header('Content-Type:application/vnd.ms-excel');?>
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<!--[if gte mso 9]><xml>
<o:DocumentProperties>
<o:Author>Amicka</o:Author>
<o:LastAuthor>Amicka</o:LastAuthor>
<o:Created><?php echo date("Y-m-d\TG:i:s\Z",time())?></o:Created>
<o:LastSaved><?php echo date("Y-m-d\TG:i:s\Z",time())?></o:LastSaved>
<o:Company>pajunas interactive,inc.</o:Company>
<o:Version>9.2720</o:Version>
</o:DocumentProperties>
<o:OfficeDocumentSettings>
<o:DownloadComponents/>
<o:LocationOfComponents HRef="file:msowc.cab"/>
</o:OfficeDocumentSettings>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
tr
{mso-height-source:auto;}
col
{mso-width-source:auto;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:General;
text-align:general;
vertical-align:bottom;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:0;
border:none;
mso-protection:locked visible;
mso-style-name:Normal;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
.xl24
{mso-style-parent:style0;
font-size:8.0pt;
font-weight:700;
font-family:Verdana, sans-serif;
mso-font-charset:0;
text-align:center;
vertical-align:middle;
white-space:normal;}
.xl25
{mso-style-parent:style0;
font-size:8.0pt;
font-family:Verdana, sans-serif;
mso-font-charset:0;
white-space:normal;}
.xl26
{mso-style-parent:style0;
font-size:8.0pt;
font-family:Verdana, sans-serif;
mso-font-charset:0;
background:white;
mso-pattern:auto none;
white-space:normal;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name><?php echo $reportTitle ?></x:Name>
<x:WorksheetOptions>
<x:Print>
<x:ValidPrinterInfo/>
<x:HorizontalResolution>600</x:HorizontalResolution>
<x:VerticalResolution>600</x:VerticalResolution>
</x:Print>
<x:Selected/>
<x:Panes>
<x:Pane>
<x:Number>3</x:Number>
<x:ActiveCol>4</x:ActiveCol>
</x:Pane>
</x:Panes>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHeight>11340</x:WindowHeight>
<x:WindowWidth>17055</x:WindowWidth>
<x:WindowTopX>120</x:WindowTopX>
<x:WindowTopY>30</x:WindowTopY>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body link=blue vlink=purple leftmargin=0 topmargin=0>

<table x:str border=0 cellpadding=0 cellspacing=0 style='border-collapse:
collapse;table-layout:fixed;'>
<?php // <col width=64 span=4 style='width:48pt'>$summaryValues = Array();$summaryCols = isset($summaryCols) ? $summaryCols : Array();

while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
// print column labels:
if(!isset($column_labels_printed)) {
$xlCols = range(65,90);
echo '<tr height=28 style=\'height:21.0pt\'>';
foreach($row as $col =>$val) {
//print label header
echo '<td height=28 class=xl24 style=\'height:21.0pt;\'>',
ucwords(str_replace('_',' ',strtolower($col))),
'</td>';

// establish information needed for column summary
$columnArray[] = $col;
$xlCol = chr(array_shift($xlCols));
if(in_array($col,$summaryCols)) {
$summaryValues[$col] = Array(
xlCol => $xlCol,
start => 2,
end =>1,
total =>0);
}
}
echo '</tr>';
$column_labels_printed = true;
}
//print values
echo '<tr height=29 style=\'height:21.75pt\'>';
foreach($row as $col => $val) {
echo '<td height=29 class=xl25 align=right style=\'height:21.75pt;
width:48pt\'';
if(is_numeric($val)) { echo ' x:num'; }
echo '>',$val,'</td>';

if(in_array($col,$summaryCols)) {
$summaryValues[$col][total] += $val;
$summaryValues[$col][end] +=1;
}
}
echo '</tr>';
}
//print summary columns
// print_r($summaryValues);if(count($summaryValues)) {
foreach($columnArray as $col) {
echo '<td align=right';
if(isset($summaryValues[$col])) {
echo ' x:num x:fmla="=SUM('.$summaryValues[$col][xlCol].$summaryValues[$col][start].':'.$summaryValues[$col][xlCol].$summaryValues[$col][end].')">'.$summaryValues[$col][total].'</td>';
} else {
echo '>&nbsp;</td>';
}
}

if(!isset($column_labels_printed)) {
echo 'No Results.';
}
/*<tr height=17 style='height:12.75pt'>
<td height=17 colspan=2 style='height:12.75pt;mso-ignore:colspan'></td>
<td align=right x:num x:fmla="=SUM(C2:C7)">56308.71</td>
<td align=right x:num x:fmla="=SUM(D2:D7)">446.49</td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>*/
?></table>

</body>

</html>


Code To Generate Excel Documents - Part 1

<?php
header("Content-Type: application/vnd.ms-excel");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
?>
<html>
<head>
<title>Excel Spreadsheet</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<table width="200" border="1" cellspacing="0" cellpadding="2">
<tr align="center" bgcolor="#FFFF00">
<td colspan="2">
<font face="Arial, Helvetica, sans-serif"><strong>Article
</strong> </font>
</td>
</tr>
<tr>
<td width="155" align="right">
<font size="6" face="Times New Roman, Times, serif"><em>Apples:</em></font>
</td>
<td width="31" align="left" bgcolor="#FF9900">
<strong><font color="#FFFFFF">5</font></strong>
</td>
</tr>
<tr>
<td align="right">
<font size="6" face="Times New Roman, Times, serif"><em>Oranges:</em></font>
</td>
<td align="left" bgcolor="#FF9900">
<strong><font color="#FFFFFF">5
</font> </strong>
</td>
</tr>
<tr>
<td align="right">
Total:
</td>
<td align="left" bgcolor="#FF0000">
<font color="#FFFF00" size="5" face="Arial, Helvetica, sans-serif"><strong>=SUM(B2:B3)</strong></font>
</td>
</tr>
</table>
</body>
</html>