Snippets / MySQL

Delete Duplicate Rows in MySQL

Over time tables can contain duplicate entries, one of the main reasons is the system inserting the data is either not preparing it correctly, or not checking against existing records.

The Statement

Before you run the query make sure you backup the table as it will remove data, an extra check you could always do a SELECT query instead of DELETE.

DELETE FROM table1
USING table1, table1 AS vtable
WHERE vtable.id > table1.id
AND table1.field_name = vtable.field_name

13 years ago / Read More

Snippets / HTML

Use PHP to round up to a nearest multiple

With this handy little function you can easily round up a given number to the nearest multiple of your choice.

The function

/**
 * Round up to the nearest multiple of your choice.
 *
 * @param int $number 
 * @param int $near
 * @return int
 */
function get_nearest_multiple( $number, $near ) 
{ 
     $nearest = round($number/$near)*$near;
      
     return $nearest;
}

An example

echo get_nearest_multiple(4.7,0.5); // prints 4.5
 
echo get_nearest_multiple(4.2,0.5); // prints 4

echo get_nearest_multiple(3.9,0.5); // prints 4

13 years ago / Read More

Snippets / Linux

Copy files from one linux machine to another

It's fairly easy to transfer files from one Linux server to another using the scp command.

An example

scp -pv user@123.456.78:source-file.tar /home/user/source-file.tar

Options

-p Preserves the modification and access times, as well as the permissions of the source-file in the destination-file

-q Do not display the progress bar

-r Recursive, so it copies the contents of the source-file (directory in this case) recursively

-v Displays debugging messages

Note

To use this command you need to have open-ssh installed in the hosts.

13 years ago / Read More

Snippets / CSS

@font-face Firefox Cross Domain Problem

The other day I encountered a problem when using custom web fonts for use on a project.

I had my project content on mydomain.co.uk, I also had my images on a seperate subdomain - images.mydomain.co.uk and finally I had my fonts on another subdomain fonts.mydomain.co.uk.

My Problem with Firefox

I then tried to use these web fonts I had uploaded. I found out that this worked in all browsers (Chrome, IE, Opera) apart from Firefox & Safari.

After some research I found out that the Firefox browser restricts cross domain lookups for these files.

The .htaccess Fix

By allowing these cross domain lookups it will allow Firefox to correctly render my selected text with my preferred custom font. So inside mydomain.co.uk's root folder I update my .htaccess file.

# .htaccess file - allow access
header add Access-Control-Allow-Origin "mydomain.co.uk"

13 years ago / Read More

Snippets / Linux

Colour highlighting - grep search results

I always find it difficult scanning through hundreds of lines of search results trying to match the keyword you specified.

By default linux doesn't colour code grep search results, but there's an option to change this.

By saving some settings inside your .bash_profile (file that get's called upon your login, usually resides within /home/username/.bash_profile) the linux terminal will remember your settings.

# create red colour for grep search results
export GREP_OPTIONS='--color=auto' 

# or use a green highlight?
export GREP_COLOR='1;32'

13 years ago / Read More

Snippets / Linux

Securing Image Upload directories (777)

It is considered risky to leave directories in your website with the access permission 777, which allows anybody to upload files to this folder. However many PHP projects such as Wordpress require these folder permissions in order to function correctly.

I find it helpful to create a .htaccess file to go inside the image upload directory. This file will do a couple of things:
1.) Only allow certain file types to be published by the server
2.) Disable PHP from rendering pages inside that directory
3.) Disable CGI from executing any scripts inside that directory

.htaccess file

# only allow certain file types

order deny,allow
deny from all


# stop PHP from rendering anthing
RemoveHandler .php
RemoveType .php
php_flag engine off
Options -ExecCGI

How can I easily find directories with 777 permissions?

# find
find httpdocs/ -type d -perm 777

# results
httpdocs/images_folder/news
httpdocs/images_folder/avatars
httpdocs/images_folder/events

find httpdocs/ -type d -perm 777 -exec find {} -name "*.php" \;

13 years ago / Read More

Snippets / Linux

Send Email from Linux Command Line

Sending emails from the linux command line can be very useful when creating shell scripts to inform people of the completion of a scheduled crontab.

Using Linux's "mail" command

You can send emails by the following;

mail -s "Subject of Message" your@emailaddress.co.uk < /dev/null

This simplys sends a blank message to the email address of your choice.

Sending Emails with message body

In order to send emails with content in the body you can echo some output in order to pass it to the mail command

# setup the variables
$MSG="This is the body of the email address"
$SUBJET="Test Email Subject"
$ADDRESS="your@emailaddress.co.uk"

echo $MSG | mail -s $SUBJECT $ADDRESS

Alternatively if you wish to send an email, with the body text from a file you can use the following command

mail -s $SUBJECT $ADDRESS < /path/to/file.txt

13 years ago / Read More

Snippets / MySQL

Backup all MySQL databases into seperate files with crontab

It's highly recommended to keep a backup of all your data and with the following script you can setup an automated task to create a weekly backup of your data without having to manually run it; with linux!

The Shell Script "db-backup.sh"

Next Step Add to Crontab

In order for the script that we have created to be executed automatically we need to add this to the crontab task scheduler.

View existing crontab scheduled tasks

crontab -l

Edit the crontabs

crontab -e

Normally you specify the exact minute, hour, day, week, month with the either with an asteric character * (which means every) or a numeric character (which represents moments in time

The crontab also has special keywords which are predfined values

string         meaning
------         -------
@reboot        Run once, at startup.
@yearly        Run once a year, "0 0 1 1 *".
@annually      (same as @yearly)
@monthly       Run once a month, "0 0 1 * *".
@weekly        Run once a week, "0 0 * * 0".
@daily         Run once a day, "0 0 * * *".
@midnight      (same as @daily)
@hourly        Run once an hour, "0 * * * *".
@daily

Add the backup script to the crontab

@weekly /path/to/backup-script.sh

13 years ago / Read More