URL Rewriting for Beginners

Introduction

URL rewriting can be one of the best and quickest ways to improve the usability and search friendliness of your site. It can also be the source of near-unending misery and suffering. Definitely worth playing carefully with it - lots of testing is recommended. With great power comes great responsibility, and all that.

There are several other guides on the web already, that may suit your needs better than this one.

• Apache URL Rewriting Guide - The best guide around

Before reading on, you may find it helpful to have the mod_rewrite cheat sheet and/or the regular expressions cheat sheet handy. A basic grasp of the concept of regular expressions would also be very helpful.

What is "URL Rewriting"?

Most dynamic sites include variables in their URLs that tell the site what information to show the user. Typically, this gives URLs like the following, telling the relevant script on a site to load product number 7.

http://www.pets.com/show_a_product.php?product_id=7

The problems with this kind of URL structure are that the URL is not at all memorable. It's difficult to read out over the phone (you'd be surprised how many people pass URLs this way). Search engines and users alike get no useful information about the content of a page from that URL. You can't tell from that URL that that page allows you to buy a Norwegian Blue Parrot (lovely plumage). It's a fairly standard URL - the sort you'd get by default from most CMSes. Compare that to this URL:

http://www.pets.com/products/7/

Clearly a much cleaner and shorter URL. It's much easier to remember, and vastly easier to read out. That said, it doesn't exactly tell anyone what it refers to. But we can do more:

http://www.pets.com/parrots/norwegian-blue/

Now we're getting somewhere. You can tell from the URL, even when it's taken out of context, what you're likely to find on that page. Search engines can split that URL into words (hyphens in URLs are treated as spaces by search engines, whereas underscores are not), and they can use that information to better determine the content of the page. It's an easy URL to remember and to pass to another person.

Unfortunately, the last URL cannot be easily understood by a server without some work on our part. When a request is made for that URL, the server needs to work out how to process that URL so that it knows what to send back to the user. URL rewriting is the technique used to "translate" a URL like the last one into something the server can understand.

Platforms and Tools

Depending on the software your server is running, you may already have access to URL rewriting modules. If not, most hosts will enable or install the relevant modules for you if you ask them very nicely.

Apache is the easiest system to get URL rewriting running on. It usually comes with its own built-in URL rewriting module, mod_rewrite, enabled, and working with mod_rewrite is as simple as uploading correctly formatted and named text files.

IIS, Microsoft's server software, doesn't include URL rewriting capability as standard, but there are add-ons out there that can provide this functionality. ISAPI_Rewrite is the one I recommend working with, as I've so far found it to be the closest to mod_rewrite's functionality. Instructions for installing and configuring ISAPI_Rewrite can be found at the end of this article.

The code that follows is based on URL rewriting using mod_rewrite.

Basic URL Rewriting

To begin with, let's consider a simple example. We have a website, and we have a single PHP script that serves a single page. Its URL is:

http://www.pets.com/pet_care_info_07_07_2008.php

We want to clean up the URL, and our ideal URL would be:

http://www.pets.com/pet-care/

In order for this to work, we need to tell the server to internally redirect all requests for the URL "pet-care" to "pet_care_info_07_07_2008.php". We want this to happen internally, because we don't want the URL in the browser's address bar to change.

To accomplish this, we need to first create a text document called ".htaccess" to contain our rules. It must be named exactly that (not ".htaccess.txt" or "rules.htaccess"). This would be placed in the root directory of the server (the same folder as "pet_care_info_07_07_2008.php" in our example). There may already be an .htaccess file there, in which case we should edit that rather than overwrite it.

The .htaccess file is a configuration file for the server. If there are errors in the file, the server will display an error message (usually with an error code of "500"). If you are transferring the file to the server using FTP, you must make sure it is transferred using the ASCII mode, rather than BINARY. We use this file to perform 2 simple tasks in this instance - first, to tell Apache to turn on the rewrite engine, and second, to tell apache what rewriting rule we want it to use. We need to add the following to the file:

RewriteEngine On # Turn on the rewriting engine RewriteRule ^pet-care/?$ pet_care_info_01_02_2008.php [NC,L] # Handle requests for "pet-care"

A couple of quick items to note - everything following a hash symbol in an .htaccess file is ignored as a comment, and I'd recommend you use comments liberally; and the "RewriteEngine" line should only be used once per .htaccess file (please note that I've not included this line from here onwards in code example).

The "RewriteRule" line is where the magic happens. The line can be broken down into 5 parts:

• RewriteRule - Tells Apache that this like refers to a single RewriteRule.
• ^/pet-care/?$ - The "pattern". The server will check the URL of every request to the site to see if this pattern matches. If it does, then Apache will swap the URL of the request for the "substitution" section that follows.
• pet_care_info_01_02_2003.php - The "substitution". If the pattern above matches the request, Apache uses this URL instead of the requested URL.
• [NC,L] - "Flags", that tell Apache how to apply the rule. In this case, we're using two flags. "NC", tells Apache that this rule should be case-insensitive, and "L" tells Apache not to process any more rules if this one is used.
• # Handle requests for "pet-care" - Comment explaining what the rule does (optional but recommended)

The rule above is a simple method for rewriting a single URL, and is the basis for almost all URL rewriting rules.

Patterns and Replacements

The rule above allows you to redirect requests for a single URL, but the real power of mod_rewrite comes when you start to identify and rewrite groups of URLs based on patterns they contain.

Let's say you want to change all of your site URLs as described in the first pair of examples above. Your existing URLs look like this:

http://www.pets.com/show_a_product.php?product_id=7

And you want to change them to look like this:

http://www.pets.com/products/7/

Rather than write a rule for every single product ID, you of course would rather write one rule to manage all product IDs. Effectively you want to change URLs of this format:

http://www.pets.com/show_a_product.php?product_id={a number}

And you want to change them to look like this:

http://www.pets.com/products/{a number}/

In order to do so, you will need to use "regular expressions". These are patterns, defined in a specific format that the server can understand and handle appropriately. A typical pattern to identify a number would look like this:

[0-9]+

The square brackets contain a range of characters, and "0-9" indicates all the digits. The plus symbol indicates that the pattern will idenfiy one or more of whatever precedes the plus - so this pattern effectively means "one or more digits" - exactly what we're looking to find in our URL.

The entire "pattern" part of the rule is treated as a regular expression by default - you don't need to turn this on or activate it at all.

RewriteRule ^products/([0-9]+)/?$ show_a_product.php?product_id=$1 [NC,L] # Handle product requests

The first thing I hope you'll notice is that we've wrapped our pattern in brackets. This allows us to "back-reference" (refer back to) that section of the URL in the following "substitution" section. The "$1" in the substitution tells Apache to put whatever matched the earlier bracketed pattern into the URL at this point. You can have lots of backreferences, and they are numbered in the order they appear.

And so, this RewriteRule will now mean that Apache redirects all requests for domain.com/products/{number}/ to show_a_product.php?product_id={same number}.

Regular Expressions

A complete guide to regular expressions is rather beyond the scope of this article. However, important points to remember are that the entire pattern is treated as a regular expression, so always be careful of characters that are "special" characters in regular expressions.

The most instance of this is when people use a period in their pattern. In a pattern, this actually means "any character" rather than a literal period, and so if you want to match a period (and only a period) you will need to "escape" the character - precede it with another special character, a backslash, that tells Apache to take the next character to be literal.

For example, this RewriteRule will not just match the URL "rss.xml" as intended - it will also match "rss1xml", "rss-xml" and so on.

RewriteRule ^rss.xml$ rss.php [NC,L] # Change feed URL

This does not usually present a serious problem, but escaping characters properly is a very good habit to get into early. Here's how it should look:

RewriteRule ^rss\.xml$ rss.php [NC,L] # Change feed URL

This only applies to the pattern, not to the substitution. Other characters that require escaping (referred to as "metacharacters") follow, with their meaning in brackets afterwards:

• . (any character)
• * (zero of more of the preceding)
• + (one or more of the preceding)
• {} (minimum to maximum quantifier)
• ? (ungreedy modifier)
• ! (at start of string means "negative pattern")
• ^ (start of string, or "negative" if at the start of a range)
• $ (end of string)
• [] (match any of contents)
• - (range if used between square brackets)
• () (group, backreferenced group)
• | (alternative, or)
• \ (the escape character itself)

Using regular expressions, it is possible to search for all sorts of patterns in URLs and rewrite them when they match. Time for another example - we wanted earlier to be able to indentify this URL and rewrite it:

http://www.pets.com/parrots/norwegian-blue/

And we want to be able to tell the server to interpret this as the following, but for all products:

http://www.pets.com/get_product_by_name.php?product_name=norwegian-blue

And we can do that relatively simply, with the following rule:

RewriteRule ^parrots/([A-Za-z0-9-]+)/?$ get_product_by_name.php?product_name=$1 [NC,L] # Process parrots

With this rule, any URL that starts with "parrots" followed by a slash (parrots/), then one or more (+) of any combination of letters, numbers and hyphens ([A-Za-z0-9-]) (note the hyphen at the end of the selection of characters within square brackets - it must be added there to be treated literally rather than as a range separator). We reference the product name in brackets with $1 in the substitution.

We can make it even more generic, if we want, so that it doesn't matter what directory a product appears to be in, it is still sent to the same script, like so:

RewriteRule ^[A-Za-z-]+/([A-Za-z0-9-]+)/?$ get_product_by_name.php?product_name=$1 [NC,L] # Process all products

As you can see, we've replaced "parrots" with a pattern that matches letter and hyphens. That rule will now match anything in the parrots directory or any other directory whose name is comprised of at least one or more letters and hyphens.

Flags

Flags are added to the end of a rewrite rule to tell Apache how to interpret and handle the rule. They can be used to tell apache to treat the rule as case-insensitive, to stop processing rules if the current one matches, or a variety of other options. They are comma-separated, and contained in square brackets. Here's a list of the flags, with their meanings (this information is included on the cheat sheet, so no need to try to learn them all).

• C (chained with next rule)
• CO=cookie (set specified cookie)
• E=var:value (set environment variable var to value)
• F (forbidden - sends a 403 header to the user)
• G (gone - no longer exists)
• H=handler (set handler)
• L (last - stop processing rules)
• N (next - continue processing rules)
• NC (case insensitive)
• NE (do not escape special URL characters in output)
• NS (ignore this rule if the request is a subrequest)
• P (proxy - i.e., apache should grab the remote content specified in the substitution section and return it)
• PT (pass through - use when processing URLs with additional handlers, e.g., mod_alias)
• R (temporary redirect to new URL)
• R=301 (permanent redirect to new URL)
• QSA (append query string from request to substituted URL)
• S=x (skip next x rules)
• T=mime-type (force specified mime type)

Moving Content

RewriteRule ^article/?$ http://www.new-domain.com/article/ [R,NC,L] # Temporary Move

Adding an "R" flag to the flags section changes how a RewriteRule works. Instead of rewriting the URL internally, Apache will send a message back to the browser (an HTTP header) to tell it that the document has moved temporarily to the URL given in the "substitution" section. Either an absolute or a relative URL can be given in the substitution section. The header sent back includea a code - 302 - that indicates the move is temporary.

RewriteRule ^article/?$ http://www.new-domain.com/article/ [R=301,NC,L] # Permanent Move

If the move is permanent, append "=301" to the "R" flag to have Apache tell the browser the move is considered permanent. Unlike the default "R", "R=301" will also tell the browser to display the new address in the address bar.

This is one of the most common methods of rewriting URLs of items that have moved to a new URL (for example, it is in use extensively on this site to forward users to new post URLs whenever they are changed).

Conditions

Rewrite rules can be preceded by one or more rewrite conditions, and these can be strung together. This can allow you to only apply certain rules to a subset of requests. Personally, I use this most often when applying rules to a subdomain or alternative domain as rewrite conditions can be run against a variety of criteria, not just the URL. Here's an example:

RewriteCond %{HTTP_HOST} ^addedbytes\.com [NC] RewriteRule ^(.*)$ http://www.addedbytes.com/$1 [L,R=301]

The rewrite rule above redirects all requests, no matter what for, to the same URL at "www.addedbytes.com". Without the condition, this rule would create a loop, with every request matching that rule and being sent back to itself. The rule is intended to only redirect requests missing the "www" URL portion, though, and the condition preceding the rule ensures that this happens.

The condition operates in a similar way to the rule. It starts with "RewriteCond" to tell mod_rewrite this line refers to a condition. Following that is what should actually be tested, and then the pattern to test. Finally, the flags in square brackets, the same as with a RewriteRule.

The string to test (the second part of the condition) can be a variety of different things. You can test the domain being requested, as with the above example, or you could test the browser being used, the referring URL (commonly used to prevent hotlinking), the user's IP address, or a variety of other things (see the "server variables" section for an outline of how these work).

The pattern is almost exactly the same as that used in a RewriteRule, with a couple of small exceptions. The pattern may not be interpreted as a pattern if it starts with specific characters as described in the following "exceptions" section. This means that if you wish to use a regular expression pattern starting with <, >, or a hyphen, you should escape them with the backslash.

Rewrite conditions can, like rewrite rules, be followed by flags, and there are only two. "NC", as with rules, tells Apache to treat the condition as case-insensitive. The other available flag is "OR". If you only want to apply a rule if one of two conditions match, rather than repeat the rule, add the "OR" flag to the first condition, and if either match then the following rule will be applied. The default behaviour, if a rule is preceded by multiple conditions, is that it is only applied if all rules match.

Exceptions and Special Cases

Rewrite conditions can be tested in a few different ways - they do not need to be treated as regular expression patterns, although this is the most common way they are used. Here are the various ways rewrite conditons can be processed:

• <Pattern (is test string lower than pattern)
• >Pattern (is test string greater than pattern)
• =Pattern (is test string equal to pattern)
• -d (is test string a valid directory)
• -f (is test string a valid file)
• -s (is test string a valid file with size greater than zero)
• -l (is test string a symbolic link)
• -F (is test string a valid file, and accessible (via subrequest))
• -U (is test string a valid URL, and accessible (via subrequest))

Server Variables

Server variables are a selection of items you can test when writing rewrite conditions. This allows you to apply rules based on all sorts of request parameters, including browser identifiers, referring URL or a multitude of other strings. Variables are of the following format:

%{VARIABLE_NAME}

And "VARIABLE_NAME" can be replaced with any one of the following items:

• HTTP Headers
  • HTTP_USER_AGENT
  • HTTP_REFERER
  • HTTP_COOKIE
  • HTTP_FORWARDED
  • HTTP_HOST
  • HTTP_PROXY_CONNECTION
  • HTTP_ACCEPT
• Connection Variables
  • REMOTE_ADDR
  • REMOTE_HOST
  • REMOTE_USER
  • REMOTE_IDENT
  • REQUEST_METHOD
  • SCRIPT_FILENAME
  • PATH_INFO
  • QUERY_STRING
  • AUTH_TYPE
• Server Variables
  • DOCUMENT_ROOT
  • SERVER_ADMIN
  • SERVER_NAME
  • SERVER_ADDR
  • SERVER_PORT
  • SERVER_PROTOCOL
  • SERVER_SOFTWARE
• Dates and Times
  • TIME_YEAR
  • TIME_MON
  • TIME_DAY
  • TIME_HOUR
  • TIME_MIN
  • TIME_SEC
  • TIME_WDAY
  • TIME
• Special Items
  • API_VERSION
  • THE_REQUEST
  • REQUEST_URI
  • REQUEST_FILENAME
  • IS_SUBREQ

Working With Multiple Rules

The more complicated a site, the more complicated the set of rules governing it can be. This can be problematic when it comes to resolving conflicts between rules. You will find this issue rears its ugly head most often when you add a new rule to a file, and it doesn't work. What you may find, if the rule itself is not at fault, is that an earlier rule in the file is matching the URL and so the URL is not being tested against the new rule you've just added.

RewriteRule ^([A-Za-z0-9-]+)/([A-Za-z0-9-]+)/?$ get_product_by_name.php?category_name=$1&product_name=$2 [NC,L] # Process product requests RewriteRule ^([A-Za-z0-9-]+)/([A-Za-z0-9-]+)/?$ get_blog_post_by_title.php?category_name=$1&post_title=$2 [NC,L] # Process blog posts

In the example above, the product pages of a site and the blog post pages have identical patterns. The second rule will never match a URL, because anything that would match that pattern will have already been matched by the first rule.

There are a few ways to work around this. Several CMSes (including wordpress) handle this by adding an extra portion to the URL to denote the type of request, like so:

RewriteRule ^products/([A-Za-z0-9-]+)/([A-Za-z0-9-]+)/?$ get_product_by_name.php?category_name=$1&product_name=$2 [NC,L] # Process product requests RewriteRule ^blog/([A-Za-z0-9-]+)/([A-Za-z0-9-]+)/?$ get_blog_post_by_title.php?category_name=$1&post_title=$2 [NC,L] # Process blog posts

You could also write a single PHP script to process all requests, which checked to see if the second part of the URL matched a blog post or a product. I usually go for this option, as while it may increase the load on the server slightly, it gives much cleaner URLs.

RewriteRule ^([A-Za-z0-9-]+)/([A-Za-z0-9-]+)/?$ get_product_or_blog_post.php?category_name=$1&item_name=$2 [NC,L] # Process product and blog requests

There are certain situations where you can work around this issue by writing more precise rules and ordering your rules intelligently. Imagine a blog where there were two archives - one by topic and one by year.

RewriteRule ^([A-Za-z0-9-]+)/?$ get_archives_by_topic.php?topic_name=$1 [NC,L] # Get archive by topic RewriteRule ^([A-Za-z0-9-]+)/?$ get_archives_by_year.php?year=$1 [NC,L] # Get archive by year

The above rules will conflict. Of course, years are numeric and only 4 digits, so you can make that rule more precise, and by running it first the only type of conflict you cound encounter would be if you had a topic with a 4-digit number for a name.

RewriteRule ^([0-9]{4})/?$ get_archives_by_year.php?year=$1 [NC,L] # Get archive by year RewriteRule ^([A-Za-z0-9-]+)/?$ get_archives_by_topic.php?topic_name=$1 [NC,L] # Get archive by topic

mod_rewrite

Apache's mod_rewrite comes as standard with most Apache hosting accounts, so if you're on shared hosting, you are unlikely to have to do anything. If you're managing your own box, then you most likely just have to turn on mod_rewrite. If you are using Apache1, you will need to edit your httpd.conf file and remove the leading '#' from the following lines:

#LoadModule rewrite_module modules/mod_rewrite.so #AddModule mod_rewrite.c

If you are using Apache2 on a Debian-based distribution, you need to run the following command and then restart Apache:

sudo a2enmod rewrite

Other distubutions and platforms differ. If the above instructions are not suitable for your system, then Google is your friend. You may need to edit your apache2 configuration file and add "rewrite" to the "APACHE_MODULES" list, or edit httpd.conf, or even download and compile mod_rewrite yourself. For the majority, however, installation should be simple.

ISAPI_Rewrite

ISAPI_Rewrite is a URL rewriting plugin for IIS based on mod_rewrite and is not free. It performs most of the same functionality as mod_rewrite, and there is a good quality ISAPI_Rewrite forum where most common questions are answered. As ISAPI_Rewrite works with IIS, installation is relatively simple - there are installation instructions available.

ISAPI_Rewrite rules go into a file named httpd.ini. Errors will go into a file named httpd.parse.errors by default.

Leading Slashes

I have found myself tripped up numerous times by leading slashes in URL rewriting systems. Whether they should be used in the pattern or in the substitution section of a RewriteRule or used in a RewriteCond statement is a constant source of frustration to me. This may be in part because I work with different URL rewriting engines, but I would advise being careful of leading slashes - if a rule is not working, that's often a good place to start looking. I never include leading slashes in mod_rewrite rules and always include them in ISAPI_Rewrite.

Sample Rules

To redirect an old domain to a new domain:

RewriteCond %{HTTP_HOST} old_domain\.com [NC] RewriteRule ^(.*)$ http://www.new_domain.com/$1 [L,R=301]

To redirect all requests missing "www" (yes www):

RewriteCond %{HTTP_HOST} ^domain\.com [NC] RewriteRule ^(.*)$ http://www.domain.com/$1 [L,R=301]

To redirect all requests with "www" (no www):

RewriteCond %{HTTP_HOST} ^www\.domain\.com [NC] RewriteRule ^(.*)$ http://domain.com/$1 [L,R=301]

Redirect old page to new page:

RewriteRule ^old-url\.htm$ http://www.domain.com/new-url.htm [NC,R=301,L]

Original Source: https://www.addedbytes.com/articles/for-beginners/url-rewriting-for-beginners/

URL Rewriting for CodeIgniter

What is URL Rewriting?

URL rewriting provides shorter and more relevant-looking links to web pages on your site. This improves the readability and the search rankings of your URLs. For example, URL "a" can be rewritten as URL "b".

a) http://example.com/index.php?section=casting
b) http://example.com/casting

There are many articles on the web discussing the benefits of shorter and more relevant URLs. Let me add to them that you get to hide the used technology from both search engines and users. You'll also have a better time migrating to a different platform in case you need to. For example, if you build your web application on top of ASP.NET and use URL Rewriting, you can easily migrate to PHP (Recommended, of course) without changing your links and hence without losing all the search-ranking score for those links.

CodeIgniter Default URLs

By default, CodeIgniter uses a segment-based approach to represent URLs. Unfortunately, CodeIgniter includes the annoying "index.php" file name in the URL. For example:

http://example.com/index.php/products/view/shoes

Now, the CodeIgniter manual mentions that it's very simple to remove the "index.php" part from the URL using the following .htaccess file:

RewriteEngine on
RewriteCond $1 !^(index\.php|images|robots\.txt)
RewriteRule ^(.*)$ /index.php/$1 [L]

Problem solved? Not really. This didn't work on my local machine nor on my online hosting account (Dreamhost). Why? I don't know. I don't care. I don't currently have the time to find out why.

Having already installed WordPress, I remembered that their .htaccess file works offline and online (at least in my case). I started playing around with it, checked some online resources, and devised two solutions. The local solution works on my local machine with the XAMPP server installed on it and the online solution works on my Dreamhost account.

The Local Solution

RewriteEngine On
RewriteBase /ci/
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ /ci/index.php/$1 [L]

The only change you need to make is to "ci" (on lines 2 and 5) which is the folder where you have your CodeIgniter application installed. In brief, this rewrite file tells your web server to apply the rewrite rule whenever a file or a directory is not found on the server. For example, if you invoke URL "c", the "contact" folder is not found on your server (Since CodeIgniter files are in the "system" folder), and accordingly the URL is rewritten to "d". This rewrite allows CodeIgniter to execute successfully (By using URL "d") while giving you the benefits of shorter URLs (URL "c").

c) http://localhost/ci/contact
d) http://localhost/ci/index.php/contact

The Online Solution

RewriteEngine On
RewriteBase /
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ /index.php?/$1 [L]

There are no changes that you need to make for this .htaccess file. However, there's one important note to mention. The question mark after "index.php" on line 5 is needed on my online hosting account at Dreamhost. You might want to remove it if it doesn't work on yours. Again, I didn't have the time to investigate why this is the case. Please check the additional resources for more details.

How to Create Triggers in MySQL

Our Database Plan

We’ll create a small example database for a blogging application. Two tables are required:

• `blog`: stores a unique post ID, the title, content, and a deleted flag.
• `audit`: stores a basic set of historical changes with a record ID, the blog post ID, the change type (NEW, EDIT or DELETE) and the date/time of that change.

The following SQL creates the `blog` and indexes the deleted column:

CREATE TABLE `blog` (
 `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `title` text,
 `content` text,
 `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `ix_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Blog posts';

The following SQL creates the `audit` table. All columns are indexed and a foreign key is defined for audit.blog_id which references blog.id. Therefore, when we physically DELETE a blog entry, it’s full audit history is also removed.

CREATE TABLE `audit` (
 `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `blog_id` mediumint(8) unsigned NOT NULL,
 `changetype` enum('NEW','EDIT','DELETE') NOT NULL,
 `changetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `ix_blog_id` (`blog_id`),
 KEY `ix_changetype` (`changetype`),
 KEY `ix_changetime` (`changetime`),
 CONSTRAINT `FK_audit_blog_id` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Creating a Trigger

We now require two triggers:

• When a record is INSERTed into the blog table, we want to add a new entry into the audit table containing the blog ID and a type of ‘NEW’ (or ‘DELETE’ if it was deleted immediately).
• When a record is UPDATEd in the blog table, we want to add a new entry into the audit table containing the blog ID and a type of ‘EDIT’ or ‘DELETE’ if the deleted flag is set.

Note that the changetime field will automatically be set to the current time.

Each trigger requires:

1. A unique name. I prefer to use a name which describes the table and action, e.g. blog_before_insert or blog_after_update.
2. The table which triggers the event. A single trigger can only monitor a single table.
3. When the trigger occurs. This can either be BEFORE or AFTER an INSERT, UPDATE or DELETE. A BEFORE trigger must be used if you need to modify incoming data. An AFTER trigger must be used if you want to reference the new/changed record as a foreign key for a record in another table.
4. The trigger body; a set of SQL commands to run. Note that you can refer to columns in the subject table using OLD.col_name (the previous value) or NEW.col_name (the new value). The value for NEW.col_name can be changed in BEFORE INSERT and UPDATE triggers.

The basic trigger syntax is:

CREATE
    TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `database`.`table`
    FOR EACH ROW BEGIN
  -- trigger body
  -- this code is applied to every 
  -- inserted/updated/deleted row
    END;

We require two triggers — AFTER INSERT and AFTER UPDATE on the blog table. It’s not necessary to define a DELETE trigger since a post is marked as deleted by setting it’s deleted field to true.

The first MySQL command we’ll issue is a little unusual:

DELIMITER $$

Our trigger body requires a number of SQL commands separated by a semi-colon (;). To create the full trigger code we must change delimiter to something else — such as $$.

Our AFTER INSERT trigger can now be defined. It determines whether the deleted flag is set, sets the @changetype variable accordingly, and inserts a new record into the audit table:

CREATE
 TRIGGER `blog_after_insert` AFTER INSERT 
 ON `blog` 
 FOR EACH ROW BEGIN
 
  IF NEW.deleted THEN
   SET @changetype = 'DELETE';
  ELSE
   SET @changetype = 'NEW';
  END IF;
    
  INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);
  
    END$$

Finally, we set the delimiter back to a semi-colon:

DELIMITER ;

The AFTER UPDATE trigger is almost identical:

DELIMITER $$

CREATE
 TRIGGER `blog_after_update` AFTER UPDATE 
 ON `blog` 
 FOR EACH ROW BEGIN
 
  IF NEW.deleted THEN
   SET @changetype = 'DELETE';
  ELSE
   SET @changetype = 'EDIT';
  END IF;
    
  INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);
  
    END$$

DELIMITER ;

It’s beyond the scope of this article, but you could consider calling a single stored procedure which handles both triggers.

Trigger Happy?

Let’s see what happens when we insert a new post into our blog table:

INSERT INTO blog (title, content) VALUES ('Article One', 'Initial text.');

A new entry appears in the `blog` table as you’d expect:

id	title	content	deleted
1	Article One	Initial text	0

In addition, a new entry appears in our `audit` table:

id	blog_id	changetype	changetime
1	1	NEW	2011-05-20 09:00:00

Let’s update our blog text:

UPDATE blog SET content = 'Edited text' WHERE id = 1;

As well as changing the post, a new entry appears in the `audit` table:

id	blog_id	changetype	changetime
1	1	NEW	2011-05-20 09:00:00
2	1	EDIT	2011-05-20 09:01:00

Finally, let’s mark the post as deleted:

UPDATE blog SET deleted = 1 WHERE id = 1;

The `audit` table is updated accordingly and we have a record of when changes occurred:

id	blog_id	changetype	changetime
1	1	NEW	2011-05-20 09:00:00
2	1	EDIT	2011-05-20 09:01:00
3	1	DELETE	2011-05-20 09:03:00

This is a simple example but I hope it’s provided some insight into the power of MySQL triggers.

Computer Science MCQ's

Computer Science MCQ's

http://www.indiabix.com/computer-science/computer-fundamentals/

http://www.indiabix.com/computer-science/networking/

Submit ajax request on same page

if (isset($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) === 'xmlhttprequest')
{
        //fun goes here.
}

Create a CSV File for a user in PHP

An improved version of the function from php.net

function download_csv_results($results, $name = NULL)
{
    if( ! $name)
    {
        $name = md5(uniqid() . microtime(TRUE) . mt_rand()). '.csv';
    }
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename='. $name);
    header('Pragma: no-cache');
    header("Expires: 0");
    $outstream = fopen("php://output", "w");
    fputcsv($outstream, array_keys($results[0])); //This will  include column headers
    foreach($results as $result)
    {
        fputcsv($outstream, $result);
    }
    fclose($outstream);
}

    require_once("yourdatabaseconnection.php");  //Include your database connection here
    $sql = mysql_query("SELECT * FROM your_table'") or die(mysql_error()); 

    if(mysql_num_rows($sql) > 0)
    {
        $results = array();
        while($rows = mysql_fetch_assoc($sql)){
            $results[] = $rows;   
        }
      //call your function here
       download_csv_results($results, 'your_file_name_here.csv');    }

Free jQuery/Javascript Data Grid Plugins

Sigma Grid

Sigma Grid Written in pure javascript, Sigma grid is an open source AJAX data grid for displaying and inline editing data in a scrollable and sortable table. It is very powerful yet  easy to use &  integrate with php, asp.net, jsp and RoR.

Sigma Ajax Grid -- Ajax editable data grid

jQuery Grid

jQuery Grid

jqGrid is a grid component for ASP.NET & PHP based on the world’s most popular and flexible jQuery grid plugin jqGrid.

lexible jQuery grid plugin jqGrid

jqGridView

jqGridView is new, client-rich, XML-based, ajax grid plugin for jQuery library. jqGridView provides professional solution for representing and editing tabular data on the web. Carefully designed, with powerful script API, this editable DHTML grid is easy configurable with XML, and shows convincing results working with large amounts of data. jqGridView allows easy implementation of nice looking(managed through css). jqGridView is not platform-depending plug-in, it can be used with different web -programming platforms like: ASP .NET/ASP, PHP, JAVA, CGI scripts etc. jqGridView has hight bowser compatibility.

Ingrid

Ingrid is an unobtrusive jQuery component that adds datagrid behaviors (column resizing, paging, sorting, row and column styling, and more) to your tables. It’s easy to get started – read on below, or check out theExample Pages.

Ingrid is an unobtrusive jQuery component

DataTable

DataTable is a plug-in for the jQuery Javascript library. It is a highly flexible tool, based upon the foundations of progressive enhancement, which will add advanced interaction controls to any HTML table.

DataTables is a plug-in for the jQuery Javascript library

Slickgrid

Slickgrid Quite simply, SlickGrid is a JavaScript grid/spreadsheet component.
It is an advanced component and is going to be a bit more difficult to learn and configure, but once you realize its full potential, it will blow your mind!

Some highlights:

• Adaptive virtual scrolling (handle hundreds of thousands of rows with extreme responsiveness)
• Extremely fast rendering speed
• Supports jQuery UI Themes
• Background post-rendering for richer cells
• Configurable & customizable
• Full keyboard navigation
• Column resize/reorder/show/hide
• Column autosizing & force-fit
• Pluggable cell formatters & editors
• Support for editing and creating new rows.
• Grouping, filtering, custom aggregators, and more!
• Advanced detached & multi-field editors with undo/redo support.
• “GlobalEditorLock” to manage concurrent edits in cases where multiple Views on a page can edit the same data.

TableSorter

Tablesorter is a jQuery plugin for turning a standard HTML table with THEAD and TBODY tags into a sortable table without page refreshes. tablesorter can successfully parse and sort many types of data including linked data in a cell. It has many useful features including:

• Multi-column sorting
• Parsers for sorting text, URIs, integers, currency, floats, IP addresses, dates (ISO, long and short formats), time. Add your own easily
• Support secondary “hidden” sorting (e.g., maintain alphabetical sort when sorting on other criteria)
• Extensibility via widget system
• Cross-browser: IE 6.0+, FF 2+, Safari 2.0+, Opera 9.0+
• Small code size