MySQL snippet to group records by week.

SELECT COUNT(*), DATE_ADD(DATE(`orders`.`date`), INTERVAL (7 - DAYOFWEEK(`orders`.`date`)) DAY) AS `week_end` FROM `orders` GROUP BY `week_end`;

Get the index of an element in an unordered set

JQUERY snippet to find the index of an item.

In this case if a list item is clicked, we get the index of the element in relation to it's parent container.

$("ul > li").click(function () { 
    var index = $(this).prevAll().length; 
}); 

Duplicating a MySQL Table

To duplicate an existing table you can use the following command:

CREATE TABLE `NEW_TABLE` LIKE `OLD_TABLE`

To duplicate an existing table and it's contents you can use:

CREATE TABLE `NEW_TABLE` SELECT * FROM `OLD_TABLE`

Compress PHP file

Use PHP itself to compress a PHP source file.

This command line option will strip all comments and white space.

php –w input.php > output.php

Check request is via AJAX in PHP

A simple function to indicate whether a request was made via AJAX or not (note: this is easily spoofed so it's not a secure method).

function isAjax() 

{
   return (isset($_SERVER['HTTP_X_REQUESTED_WITH']) 

   && ($_SERVER['HTTP_X_REQUESTED_WITH'] == 'XMLHttpRequest'));
}

How to find duplicate rows with SQL

This article shows how to find duplicated rows in a database table. This is a very common beginner question. The basic technique is straightforward. I’ll also show some variations, such as how to find “duplicates in two columns”

http://www.xaprb.com/blog/2006/10/09/how-to-find-duplicate-rows-with-sql/

Retrieving MySQL enum values in PHP

Retrieve an array of all the available enum values.

if ($result = mysql_query("SHOW COLUMNS FROM {$table} LIKE '{$column}'))
{
  $enum = mysql_fetch_object($result);
  preg_match_all("/'([\w ]*)'/", $enum->Type, $values);
}

Find all large files on Linux

Finds all files over 10MB in size under /path and presents their names and size in a human readable format:

find /path -size +10M -exec du -h {} \;

MySQL Field Types

Type	Use for	Size
TINYINT	A very small integer	The signed range is –128 to 127. The unsigned range is 0 to 255.
SMALLINT	A small integer	The signed range is –32768 to 32767. The unsigned range is 0 to 65535
MEDIUMINT	A medium-size integer	The signed range is –8388608 to 8388607. The unsigned range is 0 to 16777215
INT or INTEGER	A normal-size integer	The signed range is –2147483648 to 2147483647. The unsigned range is 0 to 4294967295
BIGINT	A large integer	The signed range is –9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615
FLOAT	A small (single-precision) floating-point number. Cannot be unsigned	Ranges are –3.402823466E+38 to –1.175494351E-38, 0 and 1.175494351E-38 to 3.402823466E+38. If the number of Decimals is not set or <= 24 it is a single-precision floating point number
DOUBLE, DOUBLE PRECISION, REAL	A normal-size (double-precision) floating-point number. Cannot be unsigned	Ranges are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0 and 2.2250738585072014E-308 to 1.7976931348623157E+308. If the number of Decimals is not set or 25 <= Decimals <= 53 stands for a double-precision floating point number
DECIMAL, NUMERIC	An unpacked floating-point number. Cannot be unsigned	Behaves like a CHAR column: “unpacked” means the number is stored as a string, using one character for each digit of the value. The decimal point, and, for negative numbers, the ‘-‘ sign is not counted in Length. If Decimals is 0, values will have no decimal point or fractional part. The maximum range of DECIMAL values is the same as for DOUBLE, but the actual range for a given DECIMAL column may be constrained by the choice of Length and Decimals. If Decimals is left out it’s set to 0. If Length is left out it’s set to 10. Note that in MySQL 3.22 the Length includes the sign and the decimal point
DATE	A date	The supported range is ‘1000-01-01’ to ‘9999-12-31’. MySQL displays DATE values in ‘YYYY-MM-DD’ format
DATETIME	A date and time combination	The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. MySQL displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format
TIMESTAMP	A timestamp	The range is ‘1970-01-01 00:00:00’ to sometime in the year 2037. MySQL displays TIMESTAMP values in YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD or YYMMDD format, depending on whether M is 14 (or missing), 12, 8 or 6, but allows you to assign values to TIMESTAMP columns using either strings or numbers. A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation because it is automatically set to the date and time of the most recent operation if you don’t give it a value yourself
TIME	A time	The range is ‘-838:59:59’ to ‘838:59:59’. MySQL displays TIME values in ‘HH:MM:SS’ format, but allows you to assign values to TIME columns using either strings or numbers
YEAR	A year in 2- or 4- digit formats (default is 4-digit)	The allowable values are 1901 to 2155, and 0000 in the 4 year format and 1970-2069 if you use the 2 digit format (70-69). MySQL displays YEAR values in YYYY format, but allows you to assign values to YEAR columns using either strings or numbers. (The YEAR type is new in MySQL 3.22.)
CHAR	A fixed-length string that is always right-padded with spaces to the specified length when stored	The range of Length is 1 to 255 characters. Trailing spaces are removed when the value is retrieved. CHAR values are sorted and compared in case-insensitive fashion according to the default character set unless the BINARY keyword is given
VARCHAR	A variable-length string. Note: Trailing spaces are removed when the value is stored (this differs from the ANSI SQL specification)	The range of Length is 1 to 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given
TINYBLOB, TINYTEXT		A BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters
BLOB, TEXT		A BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters
MEDIUMBLOB, MEDIUMTEXT		A BLOB or TEXT column with a maximum length of 16777215 (2^24 - 1) characters
LONGBLOB, LONGTEXT		A BLOB or TEXT column with a maximum length of 4294967295 (2^32 - 1) characters
ENUM	An enumeration	A string object that can have only one value, chosen from the list of values ‘value1’, ‘value2’, ..., or NULL. An ENUM can have a maximum of 65535 distinct values.
SET	A set	A string object that can have zero or more values, each of which must be chosen from the list of values ‘value1’, ‘value2’, ... A SET can have a maximum of 64 members

Top 20+ MySQL Best Practices

Database operations often tend to be the main bottleneck for most web applications today. It’s not only the DBA’s (database administrators) that have to worry about these performance issues. We as programmers need to do our part by structuring tables properly, writing optimized queries and better code. Here are some MySQL optimization techniques for programmers.

http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/

How to use locks in PHP cron jobs to avoid cron overlaps

Cron jobs are hidden building blocks for most of the websites. They are generally used to process/aggregate data in the background. However as a website starts to grow and there is gigabytes of data to be processed by every cron job, chances are that our cron jobs might overlap and possibly corrupt our data. In this blog post, I will demonstrate how can we avoid such overlaps by using simple locking techniques. I will also discuss a few edge cases we need to consider while using locks to avoid overlap.

http://abhinavsingh.com/blog/2009/12/how-to-use-locks-in-php-cron-jobs-to-avoid-cron-overlaps/

Develop PHP applications with Picasa Web Albums

Integrate data from Picasa Web Albums into a custom PHP application

Picasa Web Albums offers Web application developers a REST-based Data API to manipulate the photos and albums stored on its servers. PHP's SimpleXML extension and Zend's GData Library are ideal to process the XML feeds generated by this API so you can customize PHP photo management and photo sharing applications. In this article, meet the Picasa Web Albums Data API and see how you can use it to retrieve photos and photo metadata; add, modify and delete photos; and perform keyword searches of Picasa's user-generated content.

http://www.ibm.com/developerworks/library/x-picasalbum/

Search and update Google Base with PHP

Use PHP to process and integrate data from Google Base with a custom Web application

Google Base allows users to store any type of content online in Google's version of a massive online database. Web application developers are able to access and search this content through the Google Base Data API. This article introduces the Google Base Data API and demonstrates it in the context of a PHP application, explaining how to use SimpleXML and the Zend_Gdata module to search, retrieve, add, and edit different types of data on Google Base.

http://www.ibm.com/developerworks/library/x-gbase/

Delete directories older than a set number of days

Find subdirectories under a path and delete any older than a set number of days.

find /path/* -type d -ctime +6 | xargs rm -r

Bash script lftp with lock file

Bash script to upload a directory to a remote ftp site.

Uses the built in mirror function of the lftp ftp client.

A lock file is created so script so two instances of the script cannot conflict with each other.

#!/usr/bin/bash
LOCKFILE=/tmp/ftp_mirror.lock

if [ -e ${LOCKFILE} ] && kill -0 `cat ${LOCKFILE}`; then
   exit # already running
fi

HOST="ftphost"
USER="username"
PASS="password"

LCD="localpath"
RCD="remotepath"

# make sure the lockfile is removed when we exit and then claim it
trap "rm -f ${LOCKFILE}; exit" INT TERM EXIT
echo $$ > ${LOCKFILE}

# upload files
lftp -c "set ftp:list-options -a;
open ftp://$USER:$PASS@$HOST;
lcd $LCD;
cd $RCD;
mirror --reverse"

rm -f ${LOCKFILE}