10-things-in-mysql-that-wont-work-as-expected

http://explainextended.com/2010/11/03/10-things-in-mysql-that-wont-work-as-expected/ Reference from this guys, it seems useful 10. Searching for a NULL view sourceprint? 1. SELECT * 2. FROM a 3. WHERE a.column = NULL In SQL, a NULL is never equal to anything, even another NULL. This query won’t return anything and in fact will be thrown out by the optimizer when building the plan. When searching for NULL values, use this instead: view sourceprint? 1. SELECT * 2. FROM a 3. WHERE a.column IS NULL #9. LEFT JOIN with additional conditions view sourceprint? 1. SELECT * 2. FROM a 3. LEFT JOIN 4. b 5. ON b.a = a.id 6. WHERE b.column = 'something' A LEFT JOIN is like INNER JOIN except that it will return each record from a at least once, substituting missing fields from b with NULL values, if there are no actual matching records. The WHERE condition, however, is evaluated after the LEFT JOIN so the query above checks column after it had been joined. And as we learned earlier, no NULL value can satisfy an equality condition, so the records from a without corresponding record from b will unavoidably be filtered out. Essentially, this query is an INNER JOIN, only less efficient. To match only the records with b.column = 'something' (while still returning all records from a), this condition should be moved into ON clause: view sourceprint? 1. SELECT * 2. FROM a 3. LEFT JOIN 4. b 5. ON b.a = a.id 6. AND b.column = 'something' #8. Less than a value but not a NULL Quite often I see the queries like this: view sourceprint? 1. SELECT * 2. FROM b 3. WHERE b.column < 'something' 4. AND b.column IS NOT NULL This is actually not an error: this query is valid and will do what’s intended. However, IS NOT NULL here is redundant. If b.column is a NULL, then b.column < 'something' will never be satisfied, since any comparison to NULL evaluates to a boolean NULL and does not pass the filter. It is interesting that this additional NULL check is never used for greater than queries (like in b.column > 'something'). This is because NULL go first in ORDER BY in MySQL and hence are incorrectly considered less than any other value by some people. This query can be simplified: view sourceprint? 1. SELECT * 2. FROM b 3. WHERE b.column < 'something' and will still never return a NULL in b.column. #7. Joining on NULL view sourceprint? 1. SELECT * 2. FROM a 3. JOIN b 4. ON a.column = b.column When column is nullable in both tables, this query won't return a match of two NULLs for the reasons described above: no NULLs are equal. Here's a query to do that: view sourceprint? 1. SELECT * 2. FROM a 3. JOIN b 4. ON a.column = b.column 5. OR (a.column IS NULL AND b.column IS NULL) MySQL's optimizer treats this as an equijoin and provides a special join condition, ref_or_null. #6. NOT IN with NULL values view sourceprint? 1. SELECT a.* 2. FROM a 3. WHERE a.column NOT IN 4. ( 5. SELECT column 6. FROM b 7. ) This query will never return anything if there is but a single NULL in b.column. As with other predicates, both IN and NOT IN against NULL evaluate to NULL. This should be rewritten using a NOT EXISTS: view sourceprint? 1. SELECT a.* 2. FROM a 3. WHERE NOT EXISTS 4. ( 5. SELECT NULL 6. FROM b 7. WHERE b.column = a.column 8. ) Unlike IN, EXISTS always evaluates to either true or false. #5. Ordering random samples view sourceprint? 1. SELECT * 2. FROM a 3. ORDER BY 4. RAND(), column 5. LIMIT 10 This query attempts to select 10 random records ordered by column. ORDER BY orders the output lexicographically: that is, the records are only ordered on the second expression when the values of the first expression are equal. However, the results of RAND() are, well, random. It's infeasible that the values of RAND() will match, so ordering on column after RAND() is quite useless. To order the randomly sampled records, use this query: view sourceprint? 01. SELECT * 02. FROM ( 03. SELECT * 04. FROM mytable 05. ORDER BY 06. RAND() 07. LIMIT 10 08. ) q 09. ORDER BY 10. column #4. Sampling arbitrary record from a group This query intends to select one column from each group (defined by grouper) view sourceprint? 1. SELECT DISTINCT(grouper), a.* 2. FROM a DISTINCT is not a function, it's a part of SELECT clause. It applies to all columns in the SELECT list, and the parentheses here may just be omitted. This query may and will select the duplicates on grouper (if the values in at least one of the other columns differ). Sometimes, it's worked around using this query (which relies on MySQL's extensions to GROUP BY): view sourceprint? 1. SELECT a.* 2. FROM a 3. GROUP BY 4. grouper Unaggregated columns returned within each group are arbitrarily taken. At first, this appears to be a nice solution, but it has quite a serious drawback. It relies on the assumption that all values returned, though taken arbitrarily from the group, will still belong to one record. Though with current implementation is seems to be so, it's not documented and can be changed in any moment (especially if MySQL will ever learn to apply index_union after GROUP BY). So it's not safe to rely on this behavior. This query would be easy to rewrite in a cleaner way if MySQL supported analytic functions. However, it's still possible to make do without them, if the table has a PRIMARY KEY defined: view sourceprint? 01. SELECT a.* 02. FROM ( 03. SELECT DISTINCT grouper 04. FROM a 05. ) ao 06. JOIN a 07. ON a.id = 08. ( 09. SELECT id 10. FROM a ai 11. WHERE ai.grouper = ao.grouper 12. LIMIT 1 13. ) #3. Sampling first record from a group This is a variation of the previous query: view sourceprint? 1. SELECT a.* 2. FROM a 3. GROUP BY 4. grouper 5. ORDER BY 6. MIN(id) DESC Unlike the previous query, this one attempts to select the record holding the minimal id. Again: it is not guaranteed that the unaggregated values returned by a.* will belong to a record holding MIN(id) (or even to a single record at all). Here's how to do it in a clean way: view sourceprint? 01. SELECT a.* 02. FROM ( 03. SELECT DISTINCT grouper 04. FROM a 05. ) ao 06. JOIN a 07. ON a.id = 08. ( 09. SELECT id 10. FROM a ai 11. WHERE ai.grouper = ao.grouper 12. ORDER BY 13. ai.grouper, ai.id 14. LIMIT 1 15. ) This query is just like the previous one but with ORDER BY added to ensure that the first record in id order will be returned. #2. IN and comma-separated list of values This query attempts to match the value of column against any of those provided in a comma-separated string: view sourceprint? 1. SELECT * 2. FROM a 3. WHERE column IN ('1, 2, 3') This does not work because the string is not expanded in the IN list. Instead, if column column is a VARCHAR, it is compared (as a string) to the whole list (also as a string), and of course will never match. If column is of a numeric type, the list is cast into the numeric type as well (and only the first item will match, at best). The correct way to deal with this query would be rewriting it as a proper IN list view sourceprint? 1. SELECT * 2. FROM a 3. WHERE column IN (1, 2, 3) , or as an inline view: view sourceprint? 01. SELECT * 02. FROM ( 03. SELECT 1 AS id 04. UNION ALL 05. SELECT 2 AS id 06. UNION ALL 07. SELECT 3 AS id 08. ) q 09. JOIN a 10. ON a.column = q.id , but this is not always possible. To work around this without changing the query parameters, one can use FIND_IN_SET: view sourceprint? 1. SELECT * 2. FROM a 3. WHERE FIND_IN_SET(column, '1,2,3') This function, however, is not sargable and a full table scan will be performed on a. #1. LEFT JOIN with COUNT(*) view sourceprint? 1. SELECT a.id, COUNT(*) 2. FROM a 3. LEFT JOIN 4. b 5. ON b.a = a.id 6. GROUP BY 7. a.id This query intends to count number of matches in b for each record in a. The problem is that COUNT(*) will never return a 0 in such a query. If there is no match for a certain record in a, the record will be still returned and counted. COUNT should be made to count only the actual records in b. Since COUNT(*), when called with an argument, ignores NULLs, we can pass b.a to it. As a join key, it can never be a null in an actual match, but will be if there were no match: view sourceprint? 1. SELECT a.id, COUNT(b.a) 2. FROM a 3. LEFT JOIN 4. b 5. ON b.a = a.id 6. GROUP BY 7. a.id P.S. In case you were wondering: no, the pictures don't have any special meaning. I just liked them.

Which File Format Should You Choose?

Which File Format Should You Choose? The help manual of a popular screen capture program offers the following suggestions GIF format is limited to 256 colors and is a lossless compression file format, a common choice for use on the Web. GIF is a good choice for storing line drawings, text, and iconic graphics at a small file size. PNG format is a lossless compression file format, which makes it a common choice for use on the Web. PNG is a good choice for storing line drawings, text, and iconic graphics at a small file size. JPG format is a lossy compressed file format. This makes it useful for storing photographs at a smaller size than a BMP. JPG is a common choice for use on the Web because it is compressed. For storing line drawings, text, and iconic graphics at a smaller file size, GIF or PNG are better choices because they are lossless. George adds – “JPEGs are for photographs and realistic images. PNGs are for line art, text-heavy images, and images with few colors. GIFs are just fail.”

PHP regular expression

http://www.noupe.com/php/php-regular-expressions.html Getting Started with PHP Regular Expressions By Noupe Editorial TeamPosted in PHP53 comments Advertisement 1. What are Regular Expressions The main purpose of regular expressions, also called regex or regexp, is to efficiently search for patterns in a given text. These search patterns are written using a special format which a regular expression parser understands. Regular expressions are originating from Unix systems, where a program was designed, called grep, to help users work with strings and manipulate text. By following a few basic rules, one can create very complex search patterns. As an example, let’s say you’re given the task to check wether an e-mail or a telephone number has the correct form. Using a few simple commands these problems can easily be solved thanks to regular expressions. The syntax doesn’t always seems straightforward at first, but once you learn it, you’ll realize that you can do pretty complex searches easily, just by typing in a few characters and you’ll approach problems from a different perspective. 2. Perl Compatible Regular Expressions PHP has implemented quite a few regex functions which uses different parsing engines. There are two major parser in PHP. One called POSIX and the other PCRE or Perl Compatible Regular Expression. The PHP function prefix for POSIX is ereg_. Since the release of PHP 5.3 this engine is deprecated, but let’s have a look at the more optimal and faster PCRE engine. In PHP every PCRE function starts with preg_ such as preg_match or preg_replace. You can read the full function list in PHP’s documentation. 3. Basic Syntax To use regular expressions first you need to learn the syntax. This syntax consists in a series of letters, numbers, dots, hyphens and special signs, which we can group together using different parentheses. In PHP every regular expression pattern is defined as a string using the Perl format. In Perl, a regular expression pattern is written between forward slashes, such as /hello/. In PHP this will become a string, ‘/hello/’. Now, let’s have a look at some operators, the basic building blocks of regular expressions Operator Description ^ The circumflex symbol marks the beginning of a pattern, although in some cases it can be omitted $ Same as with the circumflex symbol, the dollar sign marks the end of a search pattern . The period matches any single character ? It will match the preceding pattern zero or one times + It will match the preceding pattern one or more times * It will match the preceding pattern zero or more times | Boolean OR - Matches a range of elements () Groups a different pattern elements together [] Matches any single character between the square brackets {min, max} It is used to match exact character counts \d Matches any single digit \D Matches any single non digit caharcter \w Matches any alpha numeric character including underscore (_) \W Matches any non alpha numeric character excluding the underscore character \s Matches whitespace character As an addition in PHP the forward slash character is escaped using the simple slash \. Example: ‘/he\/llo/’ To have a brief understanding how these operators are used, let’s have a look at a few examples: Example Description ‘/hello/’ It will match the word hello ‘/^hello/’ It will match hello at the start of a string. Possible matches are hello or helloworld, but not worldhello ‘/hello$/’ It will match hello at the end of a string. ‘/he.o/’ It will match any character between he and o. Possible matches are helo or heyo, but not hello ‘/he?llo/’ It will match either llo or hello ‘/hello+/’ It will match hello on or more time. E.g. hello or hellohello ‘/he*llo/’ Matches llo, hello or hehello, but not hellooo ‘/hello|world/’ It will either match the word hello or world ‘/(A-Z)/’ Using it with the hyphen character, this pattern will match every uppercase character from A to Z. E.g. A, B, C… ‘/[abc]/’ It will match any single character a, b or c ‘/abc{1}/’ Matches precisely one c character after the characters ab. E.g. matches abc, but not abcc ‘/abc{1,}/’ Matches one or more c character after the characters ab. E.g. matches abc or abcc ‘/abc{2,4}/’ Matches between two and four c character after the characters ab. E.g. matches abcc, abccc or abcccc, but not abc Besides operators, there are regular expression modifiers, which can globally alter the behavior of search patterns. The regex modifiers are placed after the pattern, like this ‘/hello/i’ and they consists of single letters such as i which marks a pattern case insensitive or x which ignores white-space characters. For a full list of modifiers please visit PHP’s online documentation. The real power of regular expressions relies in combining these operators and modifiers, therefore creating rather complex search patterns. 4. Using Regex in PHP In PHP we have a total of nine PCRE functions which we can use. Here’s the list: preg_filter – performs a regular expression search and replace preg_grep – returns array entries that match a pattern preg_last_error – returns the error code of the last PCRE regex execution preg_match – perform a regular expression match preg_match_all – perform a global regular expression match preg_quote – quote regular expression characters preg_replace – perform a regular expression search and replace preg_replace_callback – perform a regular expression search and replace using a callback preg_split – split string by a regular expression The two most commonly used functions are preg_match and preg_replace. Let’s begin by creating a test string on which we will perform our regular expression searches. The classical hello world should do it. $test_string = 'hello world'; If we simply want to search for the word hello or world then the search pattern would look something like this: preg_match('/hello/', $test_string); preg_match('/world/', $test_string); If we wish to see if the string begins with the word hello, we would simply put the ^ character in the beginning of the search pattern like this: preg_match('/^hello/', $test_string); Please note that regular expressions are case sensitive, the above pattern won’t match the word hElLo. If we want our pattern to be case insensitive we should apply the following modifier: preg_match('/^hello/i', $test_string); Notice the character i at the end of the pattern after the forward slash. Now let’s examine a more complex search pattern. What if we want to check that the first five characters in the string are alpha numeric characters. preg_match('/^[A-Za-z0-9]{5}/', $test_string); Let’s dissect this search pattern. First, by using the caret character (^) we specify that the string must begin with an alpha numeric character. This is specified by [A-Za-z0-9]. A-Z means all the characters from A to Z followed by a-z which is the same except for lowercase character, this is important, because regular expressions are case sensitive. I think you’ll figure out by yourself what 0-9 means. {5} simply tells the regex parser to count exactly five characters. If we put six instead of five, the parser wouldn’t match anything, because in our test string the word hello is five characters long, followed by a white-space character which in our case doesn’t count. Also, this regular expression could be optimized to the following form: preg_match('/^\w{5}/', $test_string); \w specifies any alpha numeric characters plus the underscore character (_). 6. Useful Regex Functions Here are a few PHP functions using regular expressions which you could use on a daily basis. Validate e-mail. This function will validate a given e-mail address string to see if it has the correct form. function validate_email($email_address) { if( !preg_match("/^([a-zA-Z0-9])+([a-zA-Z0-9\._-])*@([a-zA-Z0-9_-])+ ([a-zA-Z0-9\._-]+)+$/", $email_address)) { return false; } return true; } Validate a URL function validate_url($url) { return preg_match('|^http(s)?://[a-z0-9-]+(.[a-z0-9-]+)*(:[0-9]+)? (/.*)?$|i', $url); } Remove repeated words. I often found repeated words in a text, such as this this. This handy function will remove such duplicate words. function remove_duplicate_word($text) { return preg_replace("/s(w+s)1/i", "$1", $text); } Validate alpha numeric, dashes, underscores and spaces function validate_alpha($text) { return preg_match("/^[A-Za-z0-9_- ]+$/", $text); } Validate US ZIP codes function validate_zip($zip_code) { return preg_match("/^([0-9]{5})(-[0-9]{4})?$/i",$zip_code); } 7. Regex Cheat Sheet Because cheat sheets are cool nowadays, below you can find a PCRE cheat sheet that you can run through quickly anytime you forget something. Meta Characters Description ^ Marks the start of a string $ Marks the end of a string . Matches any single character | Boolean OR () Group elements [abc] Item in range (a,b or c) [^abc] NOT in range (every character except a,b or c) \s White-space character a? Zero or one b characters. Equals to a{0,1} a* Zero or more of a a+ One or more of a a{2} Exactly two of a a{,5} Up to five of a a{5,10} Between five to ten of a \w Any alpha numeric character plus underscore. Equals to [A-Za-z0-9_] \W Any non alpha numeric characters \s Any white-space character \S Any non white-space character \d Any digits. Equals to [0-9] \D Any non digits. Equals to [^0-9] Pattern Modifiers Description i Ignore case m Multiline mode S Extra analysis of pattern u Pattern is treated as UTF-8 8. Useful Readings 15 PHP Regular Expression for Web Developers Mastering Regular Expressions in PHP Introduction to PHP Regex

Eway Developer

Eway Developer link https://eway.secure.force.com/PartnerPortal