It took a ton of googling, and it was really hard to find the answer. I had two tables, and wanted to do a MySQL Query that seems like a NOT IN between two tables. There are two tables with one unique field that is the same between the tables. The goal is to find the rows in the second table that are not found in the first table based upon the similar field.

I could attempt to explain this, but someone has already done a very good job of this, so I’d rather just provide a link to their content. The post is from the author of “High Performance MySQL”, Baron Schwartz, and covers writing an SQL Exclusion Join. The key area of interest is the section on LEFT OUTER joins. He offers this example query:

SELECT apples.Variety
FROM apples
    LEFT OUTER JOIN oranges
        ON apples.Price = oranges.Price
WHERE oranges.Price IS NULL

When in doubt, find someone smarter to answer the question for you. Thanks Baron!

The getcwd() function is short for ‘GET Current Working Directory’. This can easily be combined with the scandir() function which returns an array of all the files and directories inside the specified directory. This tip was excluded from the book as an oversight.
 
A quick way to get a list of all the contents of the current directory is to use the following code:

function preprint($arr){
  echo '< pre>'.print_r($arr).'< /pre>';
}
$array = scandir(getcwd());
preprint($array); // nicely formatted display of the array

 
Of course, you can skip the print/echo portion if you don’t wish to display the contents and just use the array to perform other checks, but you get the idea.

There is a bug for strtotime() when you are on the last day of a month that has 31 days in it. The function, strotime(‘-1 month’) will return the beginning of the current month, or put another way, 30 days prior. Needless to say, this is annoying. However, it doesn’t come up very often, and there is a way to fix things: by rolling back the clock 1 extra day. I ran into this problem on a different personal project, and wrote the following function to address the issue. Whenever using strtotime() and negative months, switch in strtotimefix() instead:

function strtotimefix($val,$timestamp=0){
	if ($timestamp==0){ $timestamp = time(); }
	if (date('m') == date('m',strtotime('-1 month'))){
		$timestamp = strtotime('-1 day',$timestamp);
	}else{
		$timestamp = time();
	}
	$strtotime = strtotime($val,$timestamp);
	return $strtotime;
}

So what’s happening? Basically, the function will check if the numeric month is the same between the current month and the month through ‘-1 month’. If so, it subtracts 1 day from the current timestamp, then runs through the strtotime function using the new timestamp. If everything is fine, nothing is altered, so you don’t have to worry that using the strtotimefix() function will break a perfectly normal strtotime(‘-1 month’) call. Be advised that if you are not doing a call with ‘-x month’, the function will return an incorrect timestamp by 1 day (I hope to update it to be self-aware and only ‘fix’ when the bug would be introduced).

I’ve gotten better and more comfortable with regular expressions as time has passed, and sometimes I spend timing wading through google for some common regular expressions I want to put into use, because I’m sure someone has already created it. Well, this isn’t always true (or easy to find), so I decided to collect some common Regular Expressions that may benefit readers. It’s a good idea to keep two things handy if you want to play with Regular Expressions yourself:

• A copy of the PHP book with a tab on pages 149 and 150 for common RegEx syntax
• A link to RegExPal, a color coding Regular Expression tester.

Common Regular Expressions:

Date and Time RegEx

Time format (no seconds):
HH:MM am/pm

^([1-9]|1[012]):(0[0-9]|[1-5][0-9])\s?(am|AM|pm|PM)$

Date in mm/dd/yyyy format, with an option for m/d/yyyy (exclude zero’s)

^(0?[1-9]|1[012])[ \/.-](0?[1-9]|[12][0-9]|3[01])[ \/.-](19|20)\d\d$

Date in dd/mm/yyyy format, with an option for d/m/yyyy (exclude zero’s)

^(0?[1-9]|[12][0-9]|3[01])[ \/.-](0?[1-9]|1[012])[ \/.-](19|20)\d\d$

Demographics RegEx

Age in years – max 122

^([0-9]|[1-9][0-9]|[1-9][0-1][0-9]|[1-9]2[0-2])$

Height in Feet and Inches:
6′3″

^([1-8]')?\s?([1-9]|1[01])$

Contact Information RegEx

U.S. Phone Number – parenthesis, periods, dashes, and spaces are allowed:
(123) 456.7890

^((\(\d{3}\)\s?|\d{3}\.)|(\d{3}[-\s\.]))?\d{3}[-\s\.]\d{4}$

U.S. Zip Code – 5 or 9 digit with dash

^\d{5}([\-]\d{4}){0,1}$

Email Address – (use preg_match) credit goes to fightingforalostcause.net

/^[-_a-z0-9\'+*$^&%=~!?{}]++(?:\.[-_a-z0-9\'+*$^&%=~!?{}]+)*+@(?:(?![-.])[-a-z0-9.]+(?<![-.])\.[a-z]{2,6}|\d{1,3}(?:\.\d{1,3}){3})(?::\d++)?$/iD

Currency RegEx

Currency – U.S. Dollars and Cents with commas for multiple’s of 1000 and a period for the decimal:
$12,000.23

^\$?([1-9]{1}[0-9]{0,2}(\,[0-9]{3})*(\.[0-9]{0,2})?|[1-9]{1}[0-9]{0,} (\.[0-9]{0,2})?|0(\.[0-9]{0,2})?|(\.[0-9]{1,2})?)$

Currency – British Pounds with commas for multiple’s of 1000 and a period for the decimal:
£12,000.23

^\u00A3?([1-9]{1}[0-9]{0,2}(\,[0-9]{3})*(\.[0-9]{0,2})?|[1-9]{1}[0-9]{0,} (\.[0-9]{0,2})?|0(\.[0-9]{0,2})?|(\.[0-9]{1,2})?)$

Currency – Euros with periods for multiple’s of 1000 and a comma for the decimal:
€12.000,23

^\u20AC?([1-9]{1}[0-9]{0,2}(\.[0-9]{3})*(\,[0-9]{0,2})?|[1-9]{1}[0-9]{0,} (\,[0-9]{0,2})?|0(\,[0-9]{0,2})?|(\,[0-9]{1,2})?)$

Currency – Euros, French style, with spaces for multiple’s of 1000 and a comma for the decimal:
€12 000.23

^\u20AC?([1-9]{1}[0-9]{0,2}(\s[0-9]{3})*(\,[0-9]{0,2})?|[1-9]{1}[0-9]{0,} (\,[0-9]{0,2})?|0(\,[0-9]{0,2})?|(\,[0-9]{1,2})?)$

Discovered a great website today while working on a complex Regular Expression: RegExPal.com. The site provides color code highlighting for RegEx syntax, including real-time evaluation of test data. If you, for instance, forget to include a closing or opening parenthesis for a group inside the regular expression, the orphaned parenthesis will be highlighted in red for easy identification of the error.

Here is a quick screenshot of some of the highlighting in action:

Be advised that this is based on JavaScript regular expressions, which are comparable to the PERL compatible RegEx functions, such as preg_replace() and preg_match(). It’s a good idea to get familiar with this syntax, as ereg() and eregi() style functions will be removed from PHP6 when it is released, sometime in the future. Make sure to also checkout the Regular Expressions Cookbook contributed to by the author of RegExPal.com.