tag:blogger.com,1999:blog-80553539424022315132024-03-13T02:14:49.289-07:00PHP/MySQL tip of the dayEd Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.comBlogger33125tag:blogger.com,1999:blog-8055353942402231513.post-24331879079566803822009-04-30T07:04:00.000-07:002009-04-30T07:10:22.389-07:00MySQL multiple insert statementsIf you need to insert multiple records into a MySQL database at once, you can perform each insert as a separate query or combine them into one query as follows. Suppose you want to insert 3 records into a users table. Here's the standard separate query method:<br /><pre>INSERT INTO users (name, email) VALUES ('Ed','ed@internet.com');<br />INSERT INTO users (name, email) VALUES ('Jon','jon@intertubes.net');<br />INSERT INTO users (name, email) VALUES ('Jamey','jameyd@web.org');</pre><br />Or you could do this alternatively as one INSERT statement:<br /><pre>INSERT INTO users (name, email) VALUES <br />('Ed','ed@internet.com'), <br />('Jon','jon@intertubes.net'), <br />('Jamey','jameyd@web.org');</pre>Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-74939242697863619372009-04-27T09:00:00.000-07:002009-04-27T09:34:33.641-07:00PHP: wordwrapPHP has so many functions available that it's very easy to overlook some of them. In my case one of these functions is <a href="http://us2.php.net/manual/en/function.wordwrap.php">wordwrap</a> which I'd never heard of until just recently. This function will wrap a string to a given number of characters using a user-defined line-break string (or just \n by default) at natural word breaks (spaces). For example:<br /><pre>$text = "supercalafragilisticexpealadocious is a very long word";<br />$newtext = wordwrap($text, 18, "<br />\n");<br /><br />echo $newtext;</pre><br />This returns the following.<br /><pre>supercalafragilisticexpealadocious<br /><br />is a very long<br /><br />word</pre><br />Notice how the first word is longer than the specified maximum of 18 characters, but is not broken. This default behavior can be turned off by specifying an optional 4th parameter as follows:<br /><pre>$text = "supercalafragilisticexpealadocious is a very long word";<br />$newtext = wordwrap($text, 18, "<br />\n",true);<br /><br />echo $newtext;</pre><br />This forces any word longer than the 18-character maximum to break at 18 characters:<br /><pre>supercalafragilist<br /><br />icexpealadocious<br /><br />is a very long<br /><br />word</pre>Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-72030165096705270412009-04-23T11:19:00.000-07:002009-04-23T11:36:52.007-07:00MySQL: WITH ROLLUPMySQL's <a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html">WITH ROLLUP</a> modifier when used in conjunction with GROUP BY allows users to produce multiple levels of summary values. For instance, suppose you need to generate a listing of the average and total salary by department for a company but also the average and total for the entire company. You could accomplish this using 2 separate queries as follows:<br /><pre>->SELECT department, avg(salary) AS salary_average, sum(salary) AS salary_total<br />->FROM employees<br />->GROUP BY department<br />+------------+----------------+--------------+<br />| department | salary_average | salary_total |<br />+------------+----------------+--------------+<br />| Accounting | 234567 | 2000001 |<br />| Creative | 12000 | 100000 |<br />| Tech | 2000 | 50002 |<br />+------------+----------------+--------------+<br />->SELECT avg(salary) AS salary_average, sum(salary) AS salary_total<br />->FROM employees<br />+----------------+--------------+<br />| salary_average | salary_total |<br />+----------------+--------------+<br />| 123456 | 2150003 |<br />+----------------+--------------+</pre><br />Using WITH ROLLUP you could perform the preceding operation with just one query:<br /><pre>->SELECT department, avg(salary) AS salary_average, sum(salary) AS salary_total<br />->FROM employees<br />->GROUP BY department WITH ROLLUP<br />+------------+----------------+--------------+<br />| department | salary_average | salary_total |<br />+------------+----------------+--------------+<br />| Accounting | 234567 | 2000001 |<br />| Creative | 12000 | 100000 |<br />| Tech | 2000 | 50002 |<br />| NULL | 123456 | 2150003 |<br />+------------+----------------+--------------+</pre><br />The difference is in the last line where for department a NULL value is given along with a <i>super-aggregate</i> value for both the average and sum. The WITH ROLLUP modifier basically adds another row to the query results with the values obtained from the original query but without the GROUP BY clause. This method is more efficient than using 2 queries since the data need only be scanned once.Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com1tag:blogger.com,1999:blog-8055353942402231513.post-73556613941174199162009-04-21T08:22:00.000-07:002009-04-21T08:30:00.271-07:00PHP parse_urlPHP's <a href="http://us3.php.net/parse_url">parse_url</a> is a handy little function that will parse a URL and return the various components in an associative array. For instance, consider the following code:<br /><pre>$url = "http://www.google.com/search?hl=en&q=php+rules&btnG=Search";<br />$parts = parse_url($url);<br />print_r($parts);</pre><br />This would return:<br /><pre>Array<br />(<br /> [scheme] => http<br /> [host] => www.google.com<br /> [path] => /search<br /> [query] => hl=en&q=php+rules&btnG=Search<br />)</pre>Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-29869509409929695482009-04-20T12:45:00.000-07:002009-04-20T13:07:04.583-07:00MySQL GROUP_CONCATThe <a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat">GROUP_CONCAT</a> function is used in MySQL to concatenate column data from a particular grouping into a string, rather than performing this action inside a client side script. For instance, if you have a table of large cities along with their state, you could do this:<br /><pre>SELECT state, city<br />FROM bigcities</pre><br />which would return<br /><pre>+-------+--------------+<br />| state | city |<br />+-------+--------------+<br />| OH | Cleveland |<br />| OH | Columbus |<br />| OH | Cincinnati |<br />| PA | Pittsburgh |<br />| PA | Philadelphia |<br />...</pre><br />and then process each row and group them by state within your PHP script. Or you could use GROUP_CONCAT as follows:<br /><pre>SELECT state, GROUP_CONCAT(city) AS cities<br />FROM bigcities<br />GROUP BY state</pre><br />which would return:<br /><pre>+-------+--------------------------------+<br />| state | cities |<br />+-------+--------------------------------+<br />| OH | Cleveland,Columbus,Cincinnati |<br />| PA | Pittsburgh,Philadelphia |<br />...</pre><br />The default separator is a comma, but you can define an alternative separator as follows:<br /><pre>SELECT state, GROUP_CONCAT(city SEPARATOR ' -- ') AS cities<br />FROM bigcities<br />GROUP BY state</pre><br />which would return<br /><pre>+-------+--------------------------------------+<br />| state | cities |<br />+-------+--------------------------------------+<br />| OH | Cleveland -- Columbus -- Cincinnati |<br />| PA | Pittsburgh -- Philadelphia |<br />...</pre>Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com4tag:blogger.com,1999:blog-8055353942402231513.post-50832583656548461802009-04-16T11:51:00.000-07:002009-04-16T11:55:43.576-07:00PHP: Singleton design patternThe <a href="http://en.wikipedia.org/wiki/Singleton_pattern">Singleton design pattern</a> is useful for limiting the number of instances of an object. Here's a short and easy-to-understand tutorial on how to implement this pattern using PHP. <br /><br /><a href="http://www.talkphp.com/advanced-php-programming/1304-how-use-singleton-design-pattern.html">How to use the Singleton design pattern</a>Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-90723609073326214182009-04-15T09:53:00.000-07:002009-04-15T10:01:21.624-07:00MySQL: BETWEENIt's often necessary when constructing queries to limit a result set based on a particular field falling within a specified range. For example, if you want to select records from a table that fall within a certain date range you may write a query as follows:<br /><pre>SELECT * FROM sometable<br />WHERE date <= '2009-04-15'<br />AND date >= '2009-04-01'</pre><br />An alternative way to construct the above query is by using the <a href="http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#operator_between">BETWEEN</a> statement:<br /><pre>SELECT * FROM sometable<br />WHERE date BETWEEN '2009-04-01' AND '2009-04-15'</pre>Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com2tag:blogger.com,1999:blog-8055353942402231513.post-30942758738894664502009-04-14T07:23:00.000-07:002009-04-14T07:51:25.692-07:00PHP: avoid short tagsPHP code blocks are most commonly opened and closed as follows:<br /><br /><blockquote><?php //code here ?></blockquote><br /><br />You can also use:<br /><br /><blockquote><script language="php"> //code here </script></blockquote><br /><br />Both of the preceding methods are always available for php scripts to use. You can also optionally use php short tags or ASP-style short tags although both of these methods can be turned on and off using the php directives <span style="font-style:italic;">short_open_tag</span> (on by default) and <span style="font-style:italic;">asp_tags</span> (off by default) respectively:<br /><br /><blockquote>//short tags<br /><? //stuff here ?><br /><?= $somevar; ?> // will emulate <?php echo $somevar; ?><br /><br />//asp tags<br /><% //stuff here %></blockquote><br /><br />Since short tags are not always available depending upon a server's configuration, they are less portable and therefore it is generally considered best practice to avoid their usage. PHP short tags can also potentially conflict with XML open tags.Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-69558408202256197242009-04-13T10:48:00.000-07:002009-04-13T11:12:51.114-07:00MySQL table and index metadataMySQL has many ways to obtain table metadata. Use <span style="font-style:italic;">SHOW TABLES FROM database</span> to obtain a list of the tables in a database (omitting the FROM will use the default database). SHOW TABLES can also take a LIKE 'pattern' clause as follows:<br /><br /><pre>mysql> SHOW TABLES FROM mydatabase LIKE '%users%';<br />+---------------------------------+<br />| Tables_in_mydatabase (%users%) |<br />+---------------------------------+<br />| admin_users | <br />| registered_users | <br />| registered_users_groups | <br />+---------------------------------+<br />3 rows in set (0.00 sec)</pre><br />Another statement that comes in handy is SHOW CREATE TABLE, which will display the CREATE TABLE statement associated with a given table:<br /><pre>mysql> SHOW CREATE TABLE mydatabase.admin_users\G<br />*************************** 1. row ***************************<br /> Table: admin_users<br />Create Table: CREATE TABLE `admin_users` (<br /> `id` smallint(5) unsigned NOT NULL auto_increment,<br /> `email` varchar(64) NOT NULL default '',<br /> `password` varchar(40) NOT NULL default '',<br /> `first_name` varchar(32) default NULL,<br /> PRIMARY KEY (`id`),<br /> UNIQUE KEY `AU_email_idx` (`email`)<br />) ENGINE=MyISAM AUTO_INCREMENT=32 DEFAULT CHARSET=latin1<br />1 row in set (0.00 sec)</pre><br />SHOW INDEX will display index information:<br /><pre>mysql> SHOW INDEX FROM mydatabase.admin_users\G<br />*************************** 1. row ***************************<br /> Table: admin_users<br /> Non_unique: 0<br /> Key_name: PRIMARY<br />Seq_in_index: 1<br /> Column_name: id<br /> Collation: A<br /> Cardinality: 18<br /> Sub_part: NULL<br /> Packed: NULL<br /> Null: <br /> Index_type: BTREE<br /> Comment: <br />*************************** 2. row ***************************<br /> Table: admin_users<br /> Non_unique: 0<br /> Key_name: AU_email_idx<br />Seq_in_index: 1<br /> Column_name: email<br /> Collation: A<br /> Cardinality: 18<br /> Sub_part: NULL<br /> Packed: NULL<br /> Null: <br /> Index_type: BTREE<br /> Comment: <br />2 rows in set (0.00 sec)</pre><br />To see column information from a table use one of the following:<br /><pre>DESCRIBE mydatabase.admin_users<br />SHOW COLUMNS FROM mydatabase.admin_users <br />SHOW FIELDS FROM mydatabase.admin_users</pre><br />Any of these statements would yield output similar to this:<br /><pre>+--------------+----------------------+------+-----+--------+----------------+<br />| Field | Type | Null | Key | Default| Extra |<br />+--------------+----------------------+------+-----+--------+----------------+<br />| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | <br />| email | varchar(64) | NO | UNI | | | <br />| password | varchar(40) | NO | | | |<br />| first_name | varchar(32) | YES | | NULL | | <br />| last_name | varchar(64) | YES | | NULL | |<br />+--------------+----------------------+------+-----+--------+----------------+</pre><br />The <span style="font-style:italic;">information_shema</span> database also includes a TABLES table with table metadata, a STATISTICS table for table index metadata and other metadata tables.Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-79383092223936648862009-04-09T06:48:00.000-07:002009-04-09T07:04:58.728-07:00PHP for loop optimizationThe <a href="http://us2.php.net/for">for</a> loop is a staple of just about every programming language including php. Here is the basic structure:<br /><pre>for (expr1; expr2; expr3)<br /> statement(s)</pre><br /><span style="font-style:italic;">expr1</span> is evaluated at the beginning of the loop. <span style="font-style:italic;">expr2</span> is evaluated at the beginning of each iteration and if true the loop continues and statement(s) is executed. <span style="font-style:italic;">expr3</span> is executed at the end of each iteration. The following is a common practice when using a for loop to iterate through an array:<br /><pre>for ($i = 0 ; $i < count($array) ; $i++) {<br /> //statements here<br />}<br /></pre><br />This works but is not optimized because the <span style="font-style:italic;">count($array)</span> function must be executed on every iteration. It would be better to evaluate this and store it in a variable prior to executing the loop as follows:<br /><pre>$count = count($array);<br />for ($i = 0 ; $i < $count ; $i++) {<br /> //statements here<br />}</pre><br />What is often overlooked regarding for loops is that each of the expressions can be empty or contain multiple expressions separated by commas. So we could also write the previous example as:<br /><pre>for ($i = 0, $count = count($array) ; $i < $count ; $i++) {<br /> //statements here<br />}</pre>Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-19020077240357365742009-04-08T06:45:00.000-07:002009-04-08T07:00:47.055-07:00MySQL DELETE verus TRUNCATEThere are two ways to remove all the records from a table in MySQL; <a href="http://dev.mysql.com/doc/refman/5.0/en/delete.html">DELETE</a> and <a href="http://dev.mysql.com/doc/refman/5.0/en/truncate.html">TRUNCATE</a> as follows:<br /><pre>DELETE FROM sometable<br />TRUNCATE TABLE sometable</pre><br />These 2 statements are functionally equivalent except for the following:<br /><br /><ol><li>DELETE usually executes more slowly than TRUNCATE</li><li>DELETE returns a row count indicating the number of records deleted while TRUNCATE always returns a row count of zero. </li><li>TRUNCATE will reset any AUTO_INCREMENT fields to 1 while DELETE will maintain the current AUTO_INCREMENT value (note that there are exceptions to this depending on the type of database engine and version of MySQL).</li></ol>Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-47729571517600496122009-04-07T07:06:00.000-07:002009-04-07T07:26:14.815-07:00PHP: using isset to test string lengthWhen working with strings it is often necessary to check that the string is of a certain minimum or maximum length. The standard practice is to use the <a href="http://us2.php.net/strlen">strlen</a> function to accomplish this as follows:<br /><pre>if (strlen($password) < 6) { echo "Password too short"; }</pre><br />This is fine except there's another way to accomplish this using <a href="http://us2.php.net/isset">isset</a> and php's ability to access the characters of a string as if it were an array. Since isset is a language construct rather than a function, it is faster then using strlen and might increase performance a bit if your code includes a large number of these calculations (in a loop for instance). Here's the above code using the isset trick:<br /><pre>if (!isset($password{5})) { echo "Password too short"; }</pre><br />Basically this tests whether the 6th character in the string is set. If it is not, then the string must be less than 6 characters in length.Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-70319829877354889912009-04-06T08:42:00.000-07:002009-04-06T08:48:10.871-07:00MySQL random sampleIf you need to select a random sample of rows from a MySQL result set use the <a href="http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand">RAND</a> function to randomly order your results, and LIMIT to limit the number of records. For instance, if you need to select 3 winners from a pool of entries stored in a table for a certain time period you could use the following:<br /><pre>SELECT * FROM entries <br />WHERE entry_date < '2009-04-01' <br />AND entry_date >= '2009-01-01'<br />ORDER BY RAND()<br />LIMIT 3</pre><br />This would return 3 random entries from the result set.Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-10885637463847865502009-04-02T07:57:00.000-07:002009-04-02T08:13:26.426-07:00PHP and dynamic class autoloadingObject-oriented PHP applications often are structured such that each class is contained in a separate source file. The trouble with this is that as an application gets larger and larger, it becomes increasing difficult to keep track of which class files you need for a given script. Rather than requiring every single class file in your PHP scripts, the developers of PHP came up with a neat solution in the <span style="font-style:italic;">__autoload</span> magic function. If defined, every time an undefined class is instantiated, the <span style="font-style:italic;">__autoload</span> function gets executed with the name of the instantiated class passed as a parameter. Suppose I store all my class files in the directory <span style="font-style:italic;">php/classes/</span> and name them <span style="font-style:italic;">CLASS_NAME.class.php</span>. I could then write an __autoload function as follows:<br /><pre>function __autoload($class_name) {<br /> require_once $_SERVER['DOCUMENT_ROOT']."/php/classes/".$class_name . ".class.php";<br />}</pre><br />I then simply include the <span style="font-style:italic;">__autoload</span> function on each script rather than requiring every single class file. When an undefined class is instantiated, it gets automatically loaded in by the autoloader. The above example is fairly rudimentary; you can do a lot more with autoloading as demonstrated in <a href="http://gen5.info/q/2009/01/09/an-awesome-autoloader-for-php/">this article at Generation Five</a>. The article talks about several different options for integrating autoloading into your applications as well as all the pitfalls you might run into.Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-11258333845895670982009-04-01T06:49:00.000-07:002009-04-01T06:55:11.739-07:00MySQL temporary tablesCreating temporary tables in MySQL is an easy process; simply use CREATE TEMPORARY TABLE rather than CREATE TABLE. Temporary tables differ from standard tables in that they are only visible/usable by the client who creates them and only exist for the duration of the connection in which they were created. The server will automatically drop a temporary table when the client connection ends. Temporary tables may have the same name as an existing standard table, although the standard table will be hidden from the client until the temporary table with the same name is dropped.Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-10590155540539736312009-03-31T06:54:00.000-07:002009-03-31T07:39:46.804-07:00PHP require versus includePHP has 2 commands available for including files within scripts; namely <a href="http://us2.php.net/require">require</a> and <a href="http://us2.php.net/include">include</a>. The 2 are identical except for one important distinction; error handling. If <span style="font-style:italic;">include</span> cannot locate the file to include it emits a warning while <span style="font-style:italic;">require</span> will emit an error. Consider the following code which attempts to include a non-existent file:<br /><br /><blockquote><html><br /><body><br /><p>BEFORE</p><br /><br /><?php include("file_no_exist.txt");?><br /><br /><p>AFTER</p><br /></body><br /></html></blockquote><br /><br />The output will be:<br /><br /><blockquote>BEFORE<br /><br />AFTER</blockquote><br /><br />But if we replace <span style="font-style:italic;">include</span> with <span style="font-style:italic;">require</span> the output would just be:<br /><br /><blockquote>BEFORE</blockquote><br /><br />When the <span style="font-style:italic;">require</span> statement cannot find the file it throws a fatal error which halts execution of the script; nothing after the <span style="font-style:italic;">require</span> statement will be executed. For this reason it is generally considered best practice to use <span style="font-style:italic;">require</span> over <span style="font-style:italic;">include</span> because scripts should not be executing if necessary files are missing or misnamed.Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-18301921683163723442009-03-30T06:36:00.000-07:002009-03-30T07:07:06.732-07:00MySQL: create table based on existing tableMySQL offers a simple way to create a new table with the same column and index definitions as an existing table. If you have a table named <i>table1</i> defined as follows:<br /><pre>CREATE TABLE table1 (<br />id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,<br />name VARCHAR(30),<br />email VARCHAR(40),<br />created DATETIME,<br />index name_idx (name)<br />)</pre><br />Then executing the following will create an identical table with the name <i>table2</i>:<br /><pre>CREATE TABLE table2 LIKE table1</pre><br />This copy will retain all the column definitions and indexes as <i>table1</i> and will be identical in all respects (with a few exceptions). You can also create a copy of a table from an existing table using <br /><pre>CREATE TABLE table2 SELECT * FROM table1</pre><br />In this case <i>table2</i> will have the same column type definitions and values as <i>table1</i>, but index information is not duplicated. The table would be defined as follows:<br /><pre>CREATE TABLE table2 (<br />id int(11) NOT NULL default '0',<br />name varchar(30) default NULL,<br />email varchar(40) default NULL,<br />created datetime default NULL<br />)</pre><br /><br />You can also restrict the values copied into <i>table2</i> based on the results of the SELECT statement. For instance,<br /><pre>CREATE TABLE table2 SELECT id, name FROM table1 WHERE id > 1000</pre><br />will only copy values from <i>table1</i> where the id is greater than 1000, and only recreate the <i>id</i> and <i>name</i> columns from <i>table1</i>. <br /><br />See the <a href="http://dev.mysql.com/doc/refman/5.1/en/create-table.html">MySQL manual for all the details</a> on this functionality.Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-87251495995483749492009-03-27T09:29:00.000-07:002009-03-27T10:01:59.829-07:00PHP ctype extension for data validationPHP's <a href="http://us.php.net/manual/en/book.ctype.php">ctype</a> extension includes a set of functions which are wrappers around C's is<i>DATATYPE</i> functions (such as <i>isalpha</i>) that check whether a particular character is within a certain range. But unlike the C functions that only work on one character at a time, the PHP functions operate on the entire string. These functions are much faster than PHP's regular expression functions which are often used to validate input. As an example of where you might use these functions, suppose you have a string which is required to consist of all digits. Using regular expressions you might use <br /><br /><i>preg_match("/[0-9]+/", $somevar)</i> <br /><br />But using the ctype functions you could write it as <br /><br /><i>ctype_digit($somevar)</i><br /><br />which is much faster.Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-39686213786533643672009-03-26T06:50:00.000-07:002009-03-26T06:59:05.743-07:00MySQL slow query logProbably the best way to locate and diagnose troublesome MySQL queries is through utilization of MySQL's <a href="http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html">slow query log</a>. If enabled, MySQL will log any query that takes longer than <span style="font-style:italic;">long_query_time</span> seconds (default is 10 seconds) to run to a file. The log file by default is in the MySQL data directory and named <span style="font-style:italic;">SERVER</span>-slow.log. For the details on how to configure and utilize the slow query log, see the <a href="http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html">MySQL documentation</a>.Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-24283252354229858772009-03-25T07:28:00.000-07:002009-03-26T07:01:06.367-07:00PHP alternative control structure syntaxPHP offers an alternative syntax for most of its control structures that is handy for enhancing the readability of PHP code embedded within html. For the control structures <span style="font-style: italic;">if</span>, <span style="font-style: italic;">while</span>, <span style="font-style: italic;">for</span>, <span style="font-style: italic;">foreach</span>, and <span style="font-style: italic;">switch</span> simply replace the opening bracket ({) with a colon (:) and end the statement with <span style="font-style: italic;">endif</span>, <span style="font-style: italic;">endwhile</span>, <span style="font-style: italic;">endfor</span>, <span style="font-style: italic;">endforeach</span>, and <span style="font-style: italic;">endswitch</span> respectively. For instance, here's some embedded PHP code using the standard control structure syntax:<br /><blockquote><?php if ($showthis) { ?><br /><br /><p>some html stuff here</p><br /><i>blah</i><br /><br /><?php } else { ?><br /><br /><p>some other stuff</p><br /><i>fubar</i><br /><br /><?php } ?></blockquote><br />And using the alternative syntax this becomes:<br /><blockquote><?php if ($showthis): ?><br /><br /><p>some html stuff here</p><br /><i>blah</i><br /><br /><?php else: ?><br /><br /><p>some other stuff</p><br /><i>fubar</i><br /><br /><?php endif; ?><br /></blockquote><br />With the alternative syntax it is easier to see where one block ends and another begins, and separates out the code from the content in a way that is more readable. This is especially true for situations where you have multiple nested loops and control statements.Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-16171147492094797672009-03-24T10:21:00.000-07:002009-03-24T10:50:13.277-07:00MySQL: WHERE versus HAVINGUsing the WHERE clause to restrict a result set is familiar to anyone who regularly uses SQL, but the use of the HAVING clause may not be. So what's the difference between the two? The WHERE clause is used to restrict records, and is also used by the query optimizer to determine which indexes and tables to use; only actual fields represented in the query table(s) can be utilized. The HAVING clause is a <i>filter</i> on the final result set, and is applied after ORDER BY and GROUP BY. So MySQL cannot use it to optimize the query, but it can filter the results based on a calculated or aggregate value obtained with a GROUP BY clause or similar means. As a rule, use WHERE when you can in order to utilize a table's indexes and use HAVING to restrict a result set otherwise. For instance, take the following query which demonstrates a common mistake in MySQL:<br /><br /><pre>SELECT sum(salary) AS amount <br />FROM employees <br />GROUP BY department <br />WHERE amount > 500000;<br /></pre><br /><br />The idea with this query is to find departments at a given company where the total salary expenditure is greater than $500,000. However, it won't work because the 'amount' field is a calculated value and cannot be used by the WHERE clause. You'll get an error like this: <i>Unknown column 'amount' in 'where clause'</i>. The correct way to write this query is by using HAVING as follows:<br /><br /><pre>SELECT sum(salary) AS amount <br />FROM employees <br />GROUP BY department <br />HAVING amount > 500000;<br /></pre><br /><br />This time, MySQL will calculate all the 'amount' values then use the HAVING clause to <i>filter out</i> any records where the amount is <= 500000. <br /><br />Here we use WHERE and HAVING in combination:<br /><br /><pre>SELECT sum(salary) AS amount <br />FROM employees <br />WHERE salary < 50000 <br />GROUP BY department <br />HAVING amount > 500000;<br /></pre><br /><br />In this query we find departments at a given company where the total salary expenditure is greater than $500,000 but only including employees who make less than $50,000 a year.Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-89657652997051582932009-03-23T07:10:00.000-07:002009-03-23T07:17:11.175-07:00PHP and XMLPHP has extensive built-in support for simplifying the parsing, altering and creation of XML documents. Here's a <a href="http://www.phpfreaks.com/tutorial/handling-xml-data/">great tutorial at PHP Freaks</a> that walks you through all the basics of using the <a href="http://us2.php.net/SimpleXML">SimpleXML</a> and <a href="http://us2.php.net/DOMDocument">DOMDocument</a> built-in classes. <br /><br /><a href="http://www.phpfreaks.com/tutorial/handling-xml-data/">PHP Freaks - Tutorial: Handling XML data</a>Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-65502453646773959172009-03-20T07:46:00.000-07:002009-03-20T07:59:21.448-07:00MySQL: viewing current processesWhen debugging/troubleshooting issues with your MySQL queries, it's often beneficial to be able to see which processes are running on your MySQL server along with their current state. MySQL has an easy way to do this by using the <i>SHOW processlist</i> command. Typing this at the MySQL prompt will allow you to see all of the current processes running on your server for all the databases for which you have access. You'll see the process id, the user and host running the process, the query itself (a truncated version), the time the query has been running and its current state. To see the full, non-truncated query use <i>SHOW FULL processlist</i>. And if you need to kill off a certain process just type <i>KILL</i> with the id of the process passed as an argument.Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-79642280014018979072009-03-19T06:54:00.000-07:002009-03-19T07:09:01.460-07:00PHP dynamic image generationOne of the coolest things about PHP is its ability to create and manipulate images, and even output image streams directly to a browser using the GD library. You can take advantage of all the PHP image functions assuming your install of PHP was compiled with the GD library. For all the details and requirements just check out the <a href="http://us.php.net/gd">GD section of PHP.net</a>. I found this <a href="http://www.phpfreaks.com/tutorial/php-add-text-to-image/">simple and entertaining little tutorial at PHPFreaks</a> detailing how to dynamically create an image with text on it using a PHP script and outputting it to a browser - essentially what <a href="http://">captcha</a> does. The image functions are also useful for adding watermarks to images, resizing/cropping images, etc.Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0tag:blogger.com,1999:blog-8055353942402231513.post-24660909082922208572009-03-18T07:18:00.000-07:002009-03-18T07:35:00.687-07:00MySQL: avoid functions in WHERE clausesOne of the primary rules of MySQL Performance Optimization is to avoid using functions in a WHERE clause comparison when comparing an indexed column to a constant value. In other words, using <span style="font-style:italic;">indexed_column_name = SOME_CONSTANT</span> is good but <span style="font-style:italic;">function(indexed_column_name) = SOME_CONSTANT</span> is bad. MySQL will be unable to utilize the index on the column even if the function is very simple. The same applies to ORDER BY if you want to utilize an indexed column for sorting. Here's a real-world example. I have a table of user data with over 750K records in it. Take the following query where email is an indexed column.<br /><br /><blockquote>SELECT * FROM users WHERE email = 'estenger@digiknow.com'</blockquote><br /><br />The query takes less than 0.1 seconds to run. Now let's examine the same query where I apply the LOWER function to the <span style="font-style:italic;">email</span> column in the WHERE clause.<br /><br /><blockquote>SELECT * FROM users WHERE LOWER(email) = 'estenger@digiknow.com'</blockquote><br /><br />This query takes a whopping 15+ seconds - over 150 times slower! I should also note that MySQL string comparisons are case-insensitive by default so applying the LOWER function is completely unnecessary here. Only use functions in a WHERE clause when absolutely necessary and if no other alternative is available.Ed Stengerhttp://www.blogger.com/profile/04815997432071661377noreply@blogger.com0