Find the size of all databases on a server

A quick way to create a view to show file size statistics across databases on a server.

DROP VIEW IF EXISTS alldb;
CREATE VIEW AllDatabases AS
SELECT
s.schema_name AS 'Schema',
SUM (t.data_length) AS Data,
SUM ( t.index_length ) AS Indexes,
SUM (t.data_length) + SUM (t.index_length) AS 'Mb Used',
IF (SUM(t.data_free)=0,'',SUM (t.data_free)) As 'Mb Free',
IF (SUM(t.data_free)=0,'', 100 * (SUM (t.data_length) + SUM (t.index_length)) / ((SUM (t.data_length)+SUM (t.index_length) + SUM (IFNULL(t.data_free,0))) ) ) AS 'Pct Used', COUNT (table_name) AS Tables
FROM information_schema.schemata s
LEFT JOIN information_schema.tables t ON s.schema_name = t.table_schema
GROUP BY s.schema_name
WITH ROLLUP

Reducing WordPress comment spam using .htaccess

Wordpress Logo

I haven’t actually tried this, but the thought has occurred to me that a great way to reduce comment spam would be to use the following in your .htaccess file:

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteCond %{REQUEST_METHOD} POST
RewriteCond %{REQUEST_URI} .wp-comments-post\.php*
RewriteCond %{HTTP_REFERER} !.*blogdomainname.* [OR]
RewriteCond %{HTTP_USER_AGENT} ^$
RewriteRule (.*) ^http://%{REMOTE_ADDR}/$ [R=301,L]
</IfModule>

Obviously you’ll need to change blogdomainname to your own domain

Making a CSS div behave like a link

The requirements were that Javascript shouldn’t be necessary (bad SEO and accessibility) and that the HTML had to validate. For some examples have a look at Facebook advertisements. Here is how it was done:

  • Create the div as normal using HTML and CSS. In the div you will need to place the link you are wanting to be accessible.
  • Inside that link you will need an empty span tag.
  • Make sure that the div is styled in CSS with position:relative
  • Apply the following style to the empty span tag:

{ position:absolute;
width:100%;
height:100%;
top:0;
left: 0;
z-index: 1 }

Any other links you may have inside the div will require position:relative and a suitable z-index (greater than 1)

Making a bootable Win7 USB drive

A few quick steps to creating a bootable Windows 7 USB flash drive. Especially useful for those netbooks that do not have a CD/DVD drive, or for when the optical drive is broken.
There are utilities out there to do this, – I think even Microsoft has one, but if you want to get back to basics and use the command prompt this is the method to do so.

Format the Drive

Insert USB Flash Drive
Run Command Prompt as an Administrator (Right click Start -> All Programs -> Accessories -> Command Prompt and select “Run as Administrator”
Find the drive number of the USB Disk by typing into the Command Prompt window:
diskpart
list disk

The number of your USB drive will listed, – I’ll assume that the USB flash drive is labelled disk 1.
Format the drive. Replace the number 1 with the number of your own disk if necessary
select disk 1
clean
create partition primary
select partition 1
active
format fs=NTFS
assign
exit

After this you will ll have a formatted USB flash drive which is now ready to be made bootable.

Make the Drive Bootable

Whilst still in the same Command Prompt:
Insert your Windows Vista / 7 DVD into your drive.
Change directory to the DVD’s boot directory by typing:
cd d:\boot
Use bootsect to set the USB as a bootable NTFS drive prepared for a Vista/7 image. Here I assume that your USB drive has been labeled disk F:\ by the computer:
bootsect /nt60 f:
(Close the Command Prompt)

Copy the installation DVD to the USB drive
Any way you see fit, even using Windows explorer to copy all files on the DVD on to the formatted flash drive. Once you have done this, you can reboot and get installing.

.htaccess Mod-Rewrites for WWW Domain Name Prefixes

Quite some time ago now I wrote about the importance of 301 redirects for SEO purposes.
In a minor addition to that post here is a snippet I often use to ensure search engines do not view http://yourdomain.com and http://www.yourdomain.com as duplicate content.

To perform a redirect from domain.com to www.domain.com, insert the following code into your .htaccess file.

# mod_rewrite in use
Options +FollowSymlinks
RewriteEngine On
RewriteCond %{http_host} ^domain.com [NC]
RewriteRule ^(.*)$ http://www.domain.com/$1 [R=301,L]

To perform a redirect from www site to non-www site, use the following code in .htaccess file.

# mod_rewrite in use
Options +FollowSymLinks
RewriteEngine on
RewriteCond %{HTTP_HOST} .
RewriteCond %{HTTP_HOST} !^domain\.com
RewriteRule (.*) http://domain.com/$1 [R=301, L]

Where is my Firebug icon?

The icon of the wonderful plugin Firebug recently moved from its familiar location at the bottom right corner of Firefox. This was due to a UI change in the status bar of Firebox from version 4 onwards. The Firebug blog does a good explanation of why this was necessary.

What I did to fix however was to type about:config in my Firefox address bar.
I accepted the “I’ll be careful, promise!” warning.
To narrow down the (many) configuration options I applied a filter “firebug.show”
Finally I changed “extensions.firebug.showStatusIcon” to true.

29/01/2012 From the number of pageviews this post receives I see this is common problem. I hope it helps people solve the issue.

IE blocking iFrame Cookies

Facebook recently moving their applications inside an iFrame has turned web development on its head once again. After being deprecated in XHTML and barely hanging on in HTML iFrames looked like they had all but become extinct. Not so!

So, although other browsers do not block and intercept your cookie within an iFrame (and hence Facebook application) Internet Explorer will do. The problem lies with the W3C standard called Platform for Privacy Preferences (P3P) and you can read about it here if you wish.

In PHP all you will need to ensure Internet Explorer accepts third party cookies is to send the below header:

header('P3P:CP="IDC DSP COR ADM DEVi TAIi PSA PSD IVAi IVDi CONi HIS OUR IND CNT"');

Note: by using such a P3P statement you are making a legal statement about how your application handles cookies and if you are later found in violation of the statement you make, you may find yourself in criminal or civil hot water.