Category: Tips

Extra tips not found in the book.

Search Speed: Isset vs In_array vs MySQL Query

As databases and visitors grow, sometimes ‘old code’ doesn’t work like it use to: it takes too long or times out for exceeding the maximum time allowed by your server for PHP execution. On a MySQL table with 30,000 rows (not too many), the query was checking one column for <3 and then matching a long list (10,000) of values in a second column using a long WHERE column2=’value’ clause. Here is an example of the table:

|--column1--|--column2--|
|     0     |    1234   |
|     3     |    5678   |
-------------------------

A MySQL query took a while, but didn’t time out when the list of WHERE column2=’value’ was less than 7000, but it was just overwhelmed at 10,000. Okay, fine, time to go to phase two: in_array().
There was an existing array of values for column2, so I could simply pull a list of <3 from MySQL (quick) and then loop through (foreach) each of the results and check whether they are in_array() for the original source. If they were, just save them to a brand new array that I’ll use to work further through the problem. For clarification, that means that the system should go through 10,000+ iterations and check if each value is in_array(). If you had not already guessed, this was disastrous. In_Array() is very inefficient, and if you are dealing with an array that is more than 100 or so values, you’ll quickly learn this fact. So what to do? Use isset().
Isset(), as a function, is incredibly fast. It only checks whether something exists, and is a great way to check for whether something exists in an array. However, the trick is using isset() will look at the KEYS, while in_array() will look at the VALUES. Is that problem? Not if you build the original array as [value]=>value (set the KEY and VALUE the same). Now, as long as your values are valid keys and unique, this won’t be a problem. Then, you can use a foreach loop to check whether it is found in the original array. Here is some sample code:

$array_source = array('abc'=>'abc','def'=>'def','ghi'=>'ghi');
$array_results = array('xyz','ghi');
foreach ($array_results as $v){
  if (isset($array_source[$v])){ //fast
    $result[] = $v; //if found, save it to a fresh array
  }
}

What was the final result? The query, run 6 different times for 6 different data sets, went from a total execution time exceeding 2 minutes (MySQL), to not working at all (in_array), to 0.18 seconds. Yes, less than one second! That is the power for checking whether a value is set when comparing arrays. Isset() is even faster by a notable amount when compared to array_diff().

Check for Common Mistakes in PHP Code

frustrated manA while ago I posted an article on the 10 common PHP errors and mistakes. While this helped decipher some of the cryptic errors you get from PHP when executing your code, it doesn’t help you when the mistakes either are not caught by the PHP compiler ($variable == ‘hello world’; anyone?), or are tricky to track down. Well, I finally got some spare time and decided to build something for that.

It is still a work in progress, but the new PHP Code Checker will not execute your code, simply scan it as a string and run a battery of tests to find these mistakes. It is only a few weeks and less than 10 hours of work in it so far, but it is already at version 0.2 and is ready to help you. There is more coming soon, so keep tabs on the site and I hope it helps you deal with those tough code problems!

Photo courtesy of Zach Klein

MySQL Find Fields in Table not Found in First Table

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!

Quick Tip: getcwd() for Contents of Current Directory

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.