One of the most frequent things any web programing does is work with the MySQL database.  While this can be straight forward, there are some things that can only be learned by experience.  Now, there are hundreds of articles out there about php and MySQL, I feel it's important to cover a few key points that are easily overlooked, but can make for a very bad day.  Several caveats exist in MySQL, php, and phpMyAdmin that just don't come up often.  And some of those caveats can be far worse than it may seem.  I often hear developers say "Never post process, let the database do the work for you", and while that may be good advice in the general terms, it is by no means a law.

Never Post Process

    What post processing means, is to process data after it's been returned from MySQL.  Let MySQL sort your records, and determine which ones belong in your results, and which don't.  That is good advice.  But don't get caught in the trap for trying to take all your processing out of php.  On a recent project, I spent about 3 hours trying to get a query to work right so that I could avoid a nest for loop in php.  After those 3 hours, I decided to test that for loop, and found out that it excecuted in about 0.0001 seconds.  Now, why would I need to move that into MySQL?  Sure, it may work 10% faster in MySQL, but when is good enough, good enough?  Don't forget, that all of those nice sorting functions and grouping functions use CPU time and memory.  Most production servers don't have enough of either to be wasting on that slight speed increase.  So my point on Post Processing is this, if it's simple, do it in SQL.  Otherwise, if it's reasonably simple in php, try it there first and time its excecution.  If it's fast enough for you, leave it as is.  Only work with SQL when php is too slow (like simple sorting).  Now, most Database Administrators will tell you that SQL is always faster than php.  This is true for perfectly optimized queries and databases.  But we are not dealing in perfect worlds, and we are not DBAs.  Basically, unless the project warrants hiring a DBA to come in and optimize everything, just do what works for you.

 PHP's Typeless Variables

    For programmers who come from C, or other "typed" languages, this is the weirdest behavior in PHP.  For others, who do not come from such a background, this can be a confusing problem.   When I say "declaration" languages, I am referring to languages which require you to define, or declare, a variable and its type (integer, string, array, etc) prior to using it.  PHP does not require you to declare a variable, or even stick to a type while using it.  PHP is happy converting between most variable types on the fly (treating a float as an integer, an integer as a string, etc) This makes life easier as a programmer.  Most of the time.  The problem comes when it does this when you don't want it to.  Take an array for instance.  $array[$x] (Where $x is an integer) will give the $xth value of the array.  However, $string[$x] will give you the $xth character of the string.  Sometimes PHP will confuse the types and try to treat the array as a string.  When this happens, you will get a Notice error, that $x is an undefined offset of $array.  As far as my experience, there really isn't much in the terms of a workaround of this, just disable Notices in a production environment.  You always need to keep in mind that PHP is a typeless language, and while sometimes that makes your life easy, sometimes it will make it exceedingly difficult.

 Becareful With Primary Key of 0

    More than likely, you will have no idea this is a problem.  Under normal conditions, MySQL will not let you INSERT a value of 0 into a primary key field (with autoincrement).   MySQL think you just want the next available key, and increments it.  The problem is that it will let you UPDATE a primary key to 0 (provided none other have a value of 0).  So you can wind up with a "0" primary key and not even know it.  If found this bit of information out after struggling with a database synchronizer for work.  I needed to write a script that would check if the tables were the same between a local server and a remote server, and if not, drop the table and reinstall it (On the remote system).  Using MySQL's TABLE CHECKSUM function, the checking of tables was easy.  I ran into the problem with one table, which never would sync (every time the script ran, the CHECKSUMs were different).  I finally chased the problem to that funky key 0.  So then I tried using phpMyAdmin to export the table... Guess what... Same problem, but worse.  Since phpMyAdmin's export function does not sort the inserts by the primary key prior to exporting, a very bad thing happened.  Since the 0 occurred in the middle of the table, half of the tables IDs were incremented.  And it never through an error.  If you didn't know the problem existed, you would leave thinking your data was restored.  Luckily, there is an easy solution (which came from about 6 hours of research on my team's part).  Before importing the data, you need to add 2 lines to the SQL file.  At the top (the very first query) add "SET @OLD_SQL_MODE = @@SQL_MODE, SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';", and at the bottom (after the last query), add "SET SQL_MODE = @OLD_SQL_MODE;".  That's it.  An easy solution to a HUGE problem that you may never have known about, until it was too late...

 PHP Is Fast...

    But C is faster.  MUCH faster in fact.  The point here, is that if you have a piece of code that needs to run fast, and you don't need to worry about portability, write the code in C.   You can call that code from php using the "exec()" function.  Suppose you needed to parse a 10 Mb text file every minute (or hour, or day for that matter), you could do two things.  You could program the parser in php, and wait... And wait... And wait...  Or, you could program the parser in C.  It would still be slow, but it would be MUCH faster than PHP.  You can do something like this: "$return = exec("mycprogram file.txt");".  That will let you communicate between PHP and C.  Remember, PHP is a GREAT language for what it was designed for.  Don't try to use it for something it wasn't designed unless you have to.  And finally.

Multi-threading PHP?

     Let me start off by saying this.  There is no way to truly and EFFICIENTLY multi-thread in PHP.  With that, there are some tricks that will let you have one script do several things at the same time.  I wrote a program a while back that needed to parse an RSS feed every minute (via cron).  Then, if a certain set of events matched, I needed to update a database, download about 20 images send a customized XML file to 10 other servers, and process and send out about 5000 e-mails.  All at the same time.  To tell you the truth, it was easy.  Much easier than I though.  I created a series of scripts, the master that parsed the feed and updated the database, a script to download the images, one to send the XML files, and one to send the e-mails.  Then, at the point I needed to do the information, I simply used this line.  "$dump_me = shell_exec('nohup wget http://mysite.com/myscript.php >/dev/null & echo $!');"  Basically, that executes the script myscript.php in a new process, redirects the scripts output to nothing (/dev/null), and IMEDIATELY return a value to PHP so that we can go on with what we have to do.  Sure, I could have written the program in C, and made it much faster, but I need portability.  

TrackBack URI for this entry

Name

Email

Website

Title

Comment

Write the displayed characters

Add Comment