http://www.dind.com/blog/mysql/ en-us Fri, 18 May 2012 19:51:45 PDT Fri, 18 May 2012 19:51:45 PDT SiteCrafting.com GearBox 1.1 (beta) http://www.sitecrafting.com/blog/piece-query-cake/ If you've ever created SQL queries with PHP, you probably know what a pain it can be to create insert and update statements. I really, really (really) don't like it. As I was working on my personal site, and exploring possible frameworks to use, I came across CodeIgniter. They have a great database interaction library, especially the function for creating the insert queries.Today, armed with only the descriptions of CodeIgniter's query helper functions, I spent 20 minutes trying to duplicate some the effect of the insert and update functions. I've never seen the code, or even used it, but I didn't have to see the code to write a similar function. Both functions take a table name and an associative array of column names and values. The update function also requires a WHERE statement, and it can't be blank. This is different from CodeIgniter, and that's so you don't accidentally reset all of the passwords in the mysql users table, or any table for that matter. And then, *poof*, the function gives you a nice sql statement.I'll never have to write another "INSERT blah blah blah" again. Yay!<?php/** * Generates an insert sql query from the parameters * * @param string $table The name of the table * @param array $array An associative array with the values similar to column=>value * @return string The sql query */function getInsertSQL($table, $array) {    $sql = 'INSERT INTO '.$table;    $columns = '';    $values = '';    foreach($array as $key => $value) {        $columns .= $key.', ';        if($value !=  "") {            $values .= "'".addslashes($value)."', ";        }        else {            $values .= "'NULL', ";        }    }    $columns = substr($columns, 0, -2);    $values = substr($values, 0, -2);    $sql .= "($columns) VALUES ($values)";    return $sql;}/** * Generates an update sql query from the parameters * * @param string $table The name of the table * @param array $array An associative array with the values similar to column=>value * @param string $where What to limit the update to. Cannot be blank. * @return string The sql query */function getUpdateSQL($table, $array, $where) {    if(trim($where) == "") {        return;    }    $sql = 'UPDATE '.$table.' SET ';    foreach($array as $key => $value) {        if($value !=  "") {            $sql .= $key."='".addslashes($value)."', ";        }        else {            $sql .= $key."='NULL', ";        }    }    $sql = substr($sql, 0, -2);    $sql .= " WHERE $where";    return $sql;}?> Fri, 13 Oct 2006 10:59:00 PDT http://www.sitecrafting.com/blog/cross-table-content-search-1/ One of the more interesting adventures working with data storage is trying to aggregate information meaningfully from multiple very different data structures. Imagine you've got a website filled with content (say, a few hundred pages). All the content is stored and output dynamically - who wants to create and maintain 500 static html pages, anyway? And of course, you need a bit of variety, so all this content is spread across five different page designs, each requiring its own template and data structure.Now you say you want to search your site? All the content? And you want the results all together in one big happy sorted-by-relevancy list? How on earth am I going to do that?Well... like this.BackgroundOur basic data structures are pretty well cemented for dynamic front-end content. Every page has an entry in a core table (we'll call it page). The page entry has all the data that is universal. ID number, meta information, title, as well as parent and order numbers (for navigation hierarchy. In addition, we have a template id and a content id.The template id points to an entry in page_templates. Here you find the template names and, more importantly, the content table names. Remember that each template needs its own table as content block layout varies. With content table name and content id handy, you can leap to the actual page content.The SearchSearching a single table's content is a relatively easy matter. Put a FULLTEXT index on the relevant fields and runSELECT idFROM tableWHERE MATCH(search_columns) AGAINST (search_string)Since you designed the table, you know what fields to search on, right? Heck, this would work even for multiple tables. Just loop through the searches, writing a custom set of search indices for each straight into your queries.This is all well and good when you're working on a search for a single website. But working for a fairly prolific development company, who conveniently use the same basic table layout for the vast majority of their content managed front-end sites, the smart move is to write a function that discovers these indices on its own. This can be achieved using SHOW INDEX FROM content_table. From there, parse the relevant indices like so:while($row = mysql_fetch_assoc($res)){if($row['Index_type'] == 'FULLTEXT'){$search_cols .= $row['Column_name'].', ';}}Stick $search_cols up there in your single-table search, and suddenly you've got a dynamic FULLTEXT search that you can use on any table.Aggregating the DataUnfortunately, on a seven template site you've still got seven different sets of search results. The FULLTEXT search will return each result set in weighted order, but the user doesn't care what's using what template. They want the whole list to be in weighted order. To handle this, I tossed in a bit of array processing. First, restructure the query a bit to actually return the weight value itself:    SELECT id, MATCH(search_columns) AGAINST('search_string') AS weight    FROM content_table    WHERE MATCH(search_columns) AGAINST('search_string')I ran this for each content table, and appended all the results onto a single array. From there I just wrote a simple php usort comparison function to sort on the table weight (and obviously the queries were expanded a bit to draw some actual useful information, beyond just the page id).Of course, with anything that works, the big question is always "How can I make it better?" So... how can I make it better? Right now our production servers are running MySQL 4.0 (to paraphrase Peter Zaistev, as long as it works, why put a whole lot of data in danger for an upgrade that isn't necessary?). I've got some notions regarding putting more of the load on the database side, but those will take stored routines (5.0) and/or subqueries (4.1). But with where we're at now, does anyone else have a notion of how to improve this solution? Or perhaps a completely different solution to the same problem? Fri, 27 Oct 2006 11:34:00 PDT http://www.sitecrafting.com/blog/group-by-queries-ms-sql/ Back in the day, we worked on developing a specialty application that was basically a lead generation system with a database that stored over 200,000 records, with a potential for a lot more. The web application displayed numerous reports that calculated totals from disparate sources. We discovered that once our client began adding all their data that those reports were running slower and slower and slower. The problem was that we had one primary query to pull the records out, then, as the code looped through each record, several other queries were needed to calculate the disparate totals. That resulted in numerous database calls that slowed the entire web application. That's when Mike discovered MySQL Wizardry, that used the SUM(IF()) and the GROUP BY clause, problem solved.One way to improve the speed and efficiency of a dynamic page is to pull all the needed information that is to be displayed in one query, that's where the MySQL Wizardry comes into play. This wizardry performs cross tabulations, which are great for statistical analysis. If you're planning on creating reports, I strongly suggest you read this article.Ever since we discovered this methodology, I've used it on numerous specialty applications, from calculating amounts and costs for a shopping list to determining totals for outstanding invoices.Below is an example of the MySQL Wizardry query.MySQL Query SELECT SUM( IF( company_size = "large", 1, 0) ) as vLargeCount, SUM( IF(company_size = "medium", 1, 0) ) as vMediumCount, SUM( IF(company_size = "small", 1, 0) ) as vSmallCount, SUM( IF( company_age < = 4, 1, 0) ) as vYoungCompaniesCount, SUM( IF( company_age > 4, 1, 0) ) as vOlderComaniesCount, COUNT(companies.id) AS vTotalCompanyCountFROM companiesThe key here is the SUM(IF()) statements, this simple little piece of query magic aggregated a number of different queries into one query and turned out to be extremely efficient. As a side note, it's also important to index fields that are are used for the LEFT JOIN, in this case campaign.id and contacts_mail.campaign.Now, the Pierce County Library was developed in ASP.NET 2.0 and MS SQL Server 2005, but I wanted to apply the MySQL Wizardry magic to MS SQL server. The problem was that MS SQL Server does not have an IF statement, but this was easily remedied by using the CASE statement. Below is an example of the MySQL Wizardry for MS SQL Server.MS SQL Server QuerySELECT movies.title, movies.date,COUNT(movie_reviews.review_id) AS vReviewCount, SUM(CASE movie_reviews.thumbs WHEN "UP" THEN 1 ELSE 0 END) AS vThumbsUp, SUM(CASE movie_reviews.thumbs WHEN "DOWN" THEN 1 ELSE 0 END) AS vThumbsDown, FROM  movies LEFT OUTER JOIN movie_reviews  ON movies.code = movie_reviews.movie_codeGROUP BY movies.title, movies.dateORDER BY movies.date DESCVoil�, SUM(IF()) is replaced by SUM(CASE) and we now have MS SQL Server Wizardry. There are two things that are important to note about this query. First is that MS SQL Server GROUP BY queries are much more restrictive than MySQL's GROUP BY functionality. Each data field (NOT expressions) in the SELECT statement MUST BE in the GROUP BY clause. The second item is that MS SQL Server does not allow ntext, text, and xml data types in the SELECT statement when performing a GROUP BY. There may be a way to include these data types, but since I was short on time and didn't really need those fields and decided to tackle this problem at a later date.Tweet Tue, 24 Oct 2006 09:41:00 PDT http://www.sitecrafting.com/blog/query-conservationist/ I always enjoy trying to do my work creating web applications using the fewest number of database queries possible. It's kind of a pride thing, I guess. That and I suppose it makes to improve an app's performance and reduce the load on a server. If you're into that kind of thing. ;)A technique I use subtitutes the temptation of using queries whose results call queries and instead uses only two. Something that commonly comes up involves a needing to learn something specific about every item of a query result. While looping through the first set of results it's easy to just run a query each time to get what I need. Problem comes when your apps scale up from result sets in the double digits to those in the thousands or more. Running thousands of queries just to get what you want is a little selfish, don't you think?When I create dynamic reports for clients that show row upon row and column upon column of totals pulled from the four corners of a vast datascape the number of queries needed can skyrocket. One technique I'm rather fond of employing does everything needed in two queries. Let's play pretend here and say we're working with a grocer who wants to see a report of how many of different kinds of fruit have been sold everyday for the last month.The first query might look like this:SELECT name AS fruit_nameFROM produceWHERE type = 'fruit'...And might return results looking like this:FRUIT_NAMEapplebananaorangeraspberriesFor each of the items in that result set, you could run a query to get the total sold for each day of a month. For the shortest month of the year with 28 days, this would mean 112 queries! You can see how this could easily get out of hand.Instead, try a second query that gathers up the dates for every fruit sale during the month, en masse. You can then dump these results into an array who's indexes match the name for each type of fruit. For each fruit you can put another array with a slot for each day of the given month that stores the total number each was sold on a given day. It may end up looking like this:Array()    [apple] => Array()        [2006-02-03] => 4        [2006-02-07] => 12         [2006-02-10] => 9    [orange] => Array()         [2006-02-01] => 8         [2006-02-03] => 19         [2006-02-04] => 13From here it's pretty easy to loop through the first set of data and match on the second set using those spiffy associative indexes.This is a pretty rudimentary example but it's important for me to be mindful of such techniques as I build bigger and bigger apps. Like I mentioned, when creating reports sometimes the data is being mined from many different areas of the database all with different calculations that would make a single query nearly impossible. By finding a common bit of data like an ID and leveraging some arrays you can keep the number of queries down. Fri, 02 Feb 2007 10:53:00 PST http://www.sitecrafting.com/blog/optimizing-group-by-multiple-left/ At SiteCrafting, I enjoy working with a large number of different projects, each with their own requirements, technology, and problems to be solved, unfortunately, I sometimes forget about past solutions, until after I have finished writing a piece of code. Such is the case with a query that was eating up some serious processing time.The problem was with a GROUP BY query with LEFT JOINs to several other tables and summing up totals from those joined tables. This query was taking about 4 minutes 45 seconds to run, and worse yet, it was affecting searches which had nothing to do with that query, and probably also eating up precious memory and cpu resources.In order to optimze the table I used the EXPLAIN statement to figure out where the bottle necks were and then restructured the primary keys and indexing for the tables I was trying to left join to. This reduced the time of the query to about 35 seconds, this was still too slow and was still eating up too many resources. I decided to put the problem to the side and continued working another project. That's when I remembered using temporary tables to solve a similar problem for a past project. The temporary tables solution reduced the query time to about .5 seconds.Below is an example of the temporary table solution that I implemented for MySQL 4.1 and PHP 4.0. PLEASE NOTE: The database user must have the CREATE TEMPORARY TABLE privelege to the database.Let's start with three sample tables that a hotel uses.CREATE TABLE `rooms` (  `room_id` int(11) NOT NULL auto_increment,  `floor_number` int(10) NOT NULL,   `room_number` varchar(10) default NULL,  `size` varchar(10) default NULL,  `beds` int(11) default NULL,  `towel_allotment` int(11) default NULL,  PRIMARY KEY  (`room_id`)) TYPE=MyISAMCREATE TABLE `room_services` (  `service_id` int(11) NOT NULL auto_increment,  `room_id` int(11) default NULL,  `date_serviced` datetime default NULL,  `serviced_by` varchar(50) default NULL,  `order_amount` float default NULL,  PRIMARY KEY  (`service_id`),  KEY `room_id` (`room_id`)) TYPE=MyISAMCREATE TABLE `room_cleanings` (  `cleaning_id` int(11) NOT NULL auto_increment,  `room_id` int(11) default NULL,  `date_cleaned` datetime default NULL,  `cleaned_by` varchar(50) default NULL,  PRIMARY KEY  (`cleaning_id`),  KEY `room_id` (`room_id`)) TYPE=MyISAMFor some bizarre reason, the management wants a single report that lists the  number of times a room received room service and cleaning, even for the new rooms that were added, which have not received any services or cleaning. We start with this query.SELECT rooms.room_id, rooms.floor_number, rooms.room_number,COUNT( DISTINCT room_services.service_id) AS vTotalServices, COUNT( DISTINCT room_cleanings.cleaning_id) AS vTotalCleaningsFROM rooms LEFT JOIN room_services ON room_services.room_id = rooms.room_idLEFT JOIN room_cleanings ON room_cleanings.room_id = rooms.room_idGROUP BY rooms.room_id ORDER BY rooms.floor_number, rooms.room_number This looks like a good query. All three tables use the room_id as an index, which is numeric, another plus that queries like. However, this query is too slow, but why should that be.In order to figure that out, we need to take a look at the total number of records that is being generated by this query. Let's say that there are 100 records for the rooms table, 40,000 for room_services, and 10,000 for room_cleanings. When using left joins, to get the total number of records, where there is a one-to-many relationship, we multiple all the records together.100 rooms * 40,000 services * 10,000 cleanings = 3,990,000 totalThe sample query has almost 4 million records to count up.This is where a single temporary table can be of help. The room_services table has the most records, a temporary table can be used to store the total number of services for that room. PLEASE NOTE: In PHP, a temporary table will exist as long as a connection remains open.Here's a step-by-step example in PHP and MySQLSTEP 1 - create the temporary table// $connection is a mysql_connection$sql = <<<EODCREATE TEMPORARY TABLE `tmp_service_totals` (  `room_id` int(11) NOT NULL default '0',  `total_services` int(11) default NULL,  PRIMARY KEY  (`room_id`)) TYPE=MyISAMEOD;$res = mysql_query($sql, $connection);STEP 2 - Populate the temporary table// the temporary table stores the total number of services for each room$sql = <<<EODINSERT INTO `tmp_service_totals` (room_id, total_services )SELECT room_services.room_id, COUNT(DISTINCT room_services.service_id )FROM room_services GROUP BY rooms_services.room_idEOD;$res = mysql_query($sql, $connection);STEP 3 - Update our original query using the temporary table// query to get the totals for each room$sql = <<<EODSELECT rooms.room_id, rooms.floor_number, rooms.room_number,tmp_service_totals.total_services AS vTotalServices, COUNT( DISTINCT room_cleanings.cleaning_id) AS vTotalCleaningsFROM rooms LEFT JOIN tmp_service_totals ON tmp_service_totals.room_id = rooms.room_idLEFT JOIN room_cleanings ON room_cleanings.room_id = rooms.room_idGROUP BY rooms.room_id ORDER BY rooms.floor_number, rooms.room_number EOD;$res = mysql_query($sql, $connection);STEP 4 - Store the records into an arraywhile( $record = mysql_fetch_assoc($res) ){    $records[] = $record;} STEP 5 - Destroy the temporary table$sql = <<<EODDROP TABLE tmp_service_totalsEOD;SummaryLet's take another look at the query in step 3. Since the left join between rooms and tmp_service_totals is one-to-one, we will not need to factor that in to determine the total number of records that was generated. 100 rooms * 10,000 cleaning = 100,000 total recordsSo, we went from having four million records TO one hundred thousand records.For my real world query, after optimizing tables structures and indexes the time was averaging 34 seconds. After implementing the temporary table, the final average time was about .5 seconds, including the creation of the temporary table and inserting data into that table. Tue, 20 Feb 2007 14:53:00 PST http://www.sitecrafting.com/blog/adventures-in-database-migration-pt/ SiteCrafting is in the process of phasing out some of our older servers, and as an added bonus, the clients hosted on those servers are getting a MySQL jumpstart, leapfrogging over 4.1 to go straight from 4.0.24 to 5.0.32. Tragically, it's not quite as simple as dump | import. This is what I get for bothering my bosses for a few weeks not long after coming aboard about how nice stored procedures, updatable views, and triggers could be."The wonderful thing about standards," a wiser person than me once said, "is that there are so many of them." That's not the whole of it, though. One good thing about standards is that there are certain features one can generally rely upon to work, translate, port, etc. Assuming one works within them, rather than taking advantage of loopholes allowed by their not-entirely-compliant-but-we're-getting-there-and-anyway-isn't-this-way- easier-and-faster software. When people don't (and I'm not entirely innocent here), you run the risk of turning your simple upgrade into a serious project when your favorite software decides it's time to comply a bit more.The list of incompatible changes between these versions is actually pleasantly short. Many are in realms that don't affect the migration. Others, such as the clipping of oversized numbers (nowadays when you specify that a NUMERIC or similar column is to have no more than two digits left of the decimal, MySQL will actually enforce this and clip 100 down to the nearest 99), can be reviewed with a quick glance at column definitions. However, one has stuck us with a bit more trouble:The precedence of the comma operator also now is lower compared to JOIN, LEFT JOIN, and so forth.Pretty innocuous, eh? Not so much. One of the key aspects of the ON clause in a JOIN statement is that it must compare to a column that is already a part of the output table. This is perfectly reasonable. Consider: SELECT * FROM a INNER JOIN b ON b.key = c.key. Where did c come from? Before you JOIN to b, a constitutes the entirety of the output table. The column c.key is nowhere to be found, and as such has no place in the ON clause.This is all well and good; that query is patently ridiculous anyway. You JOIN tables based on the columns in the tables you've referenced already. Order is of minimal importance: SELECT * FROM a INNER JOIN b ON b.key = a.key INNER JOIN c ON c.key = b.key returns the same information as SELECT * FROM a INNER JOIN c ON c.key = b.key INNER JOIN b ON b.key = a.key. Column order is mixed up, but as a skilled query author, you're no doubt avoiding * and only specifying the columns you need. And as a writer of readable code I bet you're using an associative array rather than numerical indices to refer to your columns anyway (if not reading from the result directly into stand-alone variables).This is all grand as long as all your operators retain the same precedence. And thus we come to the dreaded comma join. I've been avoiding commas in my FROM clauses for as long as I've understood how to use INNER and LEFT JOIN effectively. Standards aside, I think it makes a query more readable. Take these two examples: SELECT * FROM a, b, c, dWHERE a.key = c.key1 AND c.key2 = b.keyAND d.key = a.keyvs.SELECT * FROM a INNER JOIN c ON c.key1 = a.keyINNER JOIN b ON b.key = c.key2INNER JOIN d ON d.key = a.keyBoth convey the same data, and in either it's easy enough to which tables are being referenced: either all in a row or down a column (assuming you put line breaks in your queries). In the second, however, it is much easier to see just how a given table is included: the joining criteria is right there on the same line. Using commas, you're stuck digging through a WHERE clause to figure out just how that piece of info got in there (it may not seem so bad looking at these queries, but as they get bigger, and as query design gets sloppier, things just get unpleasant).However, now the problems are much bigger. With the comma precedence lowering, nothing linked in via this method is recognized as even existing while the parser sorts out the JOIN statements. So you'd better not be using commas and LEFT JOINs in the same WHERE clause, or you're in for an adventure.Where does this leave me? Searching through the code of two dozen clients for the word "JOIN", and madly wiping out the comma scourge with a massive influx of INNER JOIN. Exciting stuff, really. Personally, I'd recommend avoiding this altogether. Stick with the JOINs, people. The JOINs. Thu, 06 Sep 2007 16:45:00 PDT http://www.sitecrafting.com/blog/gui-mess-or-productivity-booster/ While writing up a review on a database tool I discovered today, I was inspired to spark a discussion about database GUIs in general. The value of GUI tools for administering database systems like MySQL has been a topic of much debate.The purist/traditional camp argues that a GUI imposes limitations on the user when working with the database. They will claim that it presents the user with a subset of the available features for a given database. They also suggest that the addition of another layer (a "presentation layer" perhaps?) increases security risks and can degrade performance.In the other camp we have supporters that argue a GUI can increase productivity tremendously. Repetitive tasks can be automated, complex commands can be assembled in seconds rather than minutes, and for new users the learning curve can become quite a bit more forgiving.I tend to lean towards the pro-GUI group, as it is hard to deny the productivity gains when using an admin tool that is well designed. However, I must acknowledge the importance of having command line experience. There are a number of reasons for this. For one, there will always be those complex queries and processes that a GUI designer will not have anticipated that you will have to crunch out manually. Perhaps more importantly, you will have a greater understanding of the inner-workings of whatever database you are working with. This knowledge can be essential when it comes to debugging and performing complex admin tasks (even from within a GUI).The nutshell version: I think GUIs are great, but use them and don't abuse them. Make sure you know how to perform a task without an admin GUI before you use one. It may not be fun or easy, but it will definitely pay off in the end.So now I pose the question to you - on which side of the line do you stand? Are thare any pluses/minuses to each side that haven't been mentioned yet? Wed, 03 Oct 2007 15:59:00 PDT http://www.sitecrafting.com/blog/php-patterns-part-ii/ In this installment, we will be looking at two patterns that have been 'borrrowed' from Java. If you've had any development experience with J2EE, you are probably well aware of how handy Data Access Objects and Value Objects can be. If you haven't, don't fret! This article was written especially for you!If you've never heard these terms before, you may be wondering why I have chosen to group them together within one article. The simple explanation is ... well you'll see. For now just accept that they go hand-in-hand, much like salt and pepper or peanut butter and jelly or .Excited? Let's dig deeper...I. Data Access Objects (DAOs) and Value Objects (VOs), an IntroductionThese two classes comprise what is often called the "model" layer of the MVC (Model View Control) model. If you aren't familiar with this concept do yourself a huge favor and read up on it - it will save you and your peers quite a bit of time further down the road on any project.The DAO is basically the object you use to query your data source within your application. Rather than sprinkling SQL queries throughout your code, it allows you to encapsulate everything related to accessing your database within a single class. That way, if you ever change databases or need to tweak a query, you can do it in one place that's easy to find. Think of it like a filing cabinet that keeps all your papers from floating all over your desk.The VO does what you might expect a "value object" to do - it holds values. It provides you with a nifty object to pass around your application and typically contains a single row of information from your database. Again the goal here is to simplify the process of handling data retrieved from the database.II. The VO, A Closer LookCreating a VO is probably one of the simplest classes you will ever create. It basically mirrors a table in your database, with setter and getter methods for each field in that table. First, let's pretend you have a simple database table called "users" that has the following columns:iduserpassUsing this information, we can create our very first Value Object:class UserVO {    protected $id;    protected $username;    protected $password;        public function setId($id) {        $this->id = $id;    }        public function getId() {        return $this->id;    }        public function setUsername($username) {        $this->username = $username;    }        public function getUsername() {        return $this->username;    }        public function setPassword($password) {        $this->password = $password;    }        public function getPassword() {        return $this->password;    }}Pretty simple and to the point, huh? You may be wondering why I have gone through and written explicit setters and getters for each value, rather than just making two generic functions (or simply making the class variables public). The primary reason for this has more to do with coding standards than anything else. It is usually good practice to prevent class variables from being accessed directly. This way you don't have to anticipate what might happen ouside the class. By declaring functions for each specific value, you have more control over the use of each value as well. For instance, where appropriate, you could use the addslashes() and stripslashes()  functions to automatically massage data before it is inserted into the database.II. The DAO, A Closer LookNow that we have created our first VO, let's take a look at its partner in crime, the DAO. Let's take a look at some code before we start picking it apart:class UserDAO {    protected var $connect;    protected var $db;        // Attempts to initialize the database connection using the supplied info.    public function UserDAO($host, $username, $password, $database) {        $this->connect = mysql_connect($host, $username, $password);        $this->db = mysql_select_db($database);    }        // Executes the specified query and returns an associative array of reseults.    protected function execute($sql) {        $res = mysql_query($sql, $this->connect) or die(mysql_error());                if(mysql_num_rows($res) > 0) {            for($i = 0; $i < mysql_num_rows($res); $i++) {                $row = mysql_fetch_assoc($res);                $userVO[$i] = new UserVO();                                $userVO[$i]->setId($row[id]);                $userVO[$i]->setUsername($row[username]);                $userVO[$i]->setPassword($row[password]);            }        }        return $userVO;    }        // Retrieves the corresponding row for the specified user ID.    public function getByUserId($userId) {        $sql = "SELECT * FROM users WHERE id=".$userId;        return $this->execute($sql);    }        // Retrieves all users currently in the database.    public function getUsers() {        $sql = "SELECT * FROM users";        return $this->execute($sql);    }        //Saves the supplied user to the database.    public function save($userVO) {        $affectedRows = 0;                if($userVO->getId() != "") {            $currUserVO = $this->getByUserId($userVO->getId());        }                // If the query returned a row then update,        // otherwise insert a new user.        if(sizeof($currUserVO) > 0) {            $sql = "UPDATE users SET ".                "username='".$userVO->getUsername()."', ".                "password='".$userVO->getPassword()."' ".                "WHERE id=".$userVO->getId();                        mysql_query($sql, $this->connect) or die(mysql_error());            $affectedRows = mysql_affected_rows();        }        else {            $sql = "INSERT INTO users (username, password) VALUES('".                $userVO->getUsername()."', ".                $userVO->getPassword()."')".                        mysql_query($sql, $this->connect) or die(mysql_error());            $affectedRows = mysql_affected_rows();        }                return $affectedRows;    }        // Deletes the supplied user from the database.    public function delete($userVO) {        $affectedRows = 0;                // Check for a user ID.        if($userVO->getId() != "") {            $currUserVO = $this->getByUserId($userVO->getId());        }                // Otherwise delete a user.        if(sizeof($currUserVO) > 0) {            $sql = "DELETE FROM users WHERE id=".$userVO->getId();                        mysql_query($sql, $this->connect) or die(mysql_error());            $affectedRows = mysql_affected_rows();                return $affectedRows;    }}The Data Access Object consists a handful of basic methods. First is the constructor, which basically initiates the connection to the database. You pass it the database connection info and credentials, and it takes care of the rest. Next comes the getter functions, which basically represent a collection of the queries used to access the database. For example, you might have a getByUserId() method that returns the UserVO we created above filled with the corresponding data for a particular user ID.Following the getters is the save() function, which essentially does what you would imagine it does. You pass a VO to it and it will either update an existing record if it finds one that matches the VO or will insert a new row. I'll bet you're thinking what I thought the first time I learned of this function: "holy cow! that just reduced 8+ lines of code down to one!"Finally we have the big scary delete() function that, you guessed it, deletes a record from the database. To make it slightly less scary, it requires that you pass it a VO that matches a database row exactly. This will force you to have first retrieved the row you want to delete before calling this method. This helps prevent the accidental deletion of a record.And there you have it! Together these two classes can save you a tremendous amount of effort and time, makes it much easier to maintain say 6-12 months from now when you have completely forgotten how you wrote that file manager script.A side note: If you live in fear of having to write these classes for databases with 100 tables that have 200 columns each, don't despair! With the help of Google you can find countless PHP DAO and VO generators that will build the code for you (take a look here for example). Some will do it based on existing database tables, and others will do it based on what you tell them. They might still require some tweaking afterwards, but at least that way the hard part would be done and over with. Wed, 14 Nov 2007 15:48:00 PST http://www.sitecrafting.com/blog/apache-mysql-php-on-leopard/ I just finished installing Leopard on my computer, and my first impressions are that it's very slick and well thought out. One of my necessities as a developer is that I must have a webserver running on my personal computer, so I was dismayed when it wasn't functional after the upgrade. The main reason is that Leopard uses a different version of Apache than 10.4 did, and so some things get wonky. But it's easy to fix. Apache and PHP are included in Leopard, so the only thing missing is MySQL. To install that, go to MySQL's site, and download the latest copy for OS X. It's incredibly simple to install.After that comes setting up Apache. Open up a terminal window, and type in "sudo pico /etc/apache2/httpd.conf". (Note that you must be a computer administrator to access the files in etc/) OS X 10.4 had Apache in /etc/httpd/, and that's part of why it didn't work after the upgrade. Find the line in httpd.conf that looks like "#LoadModule php5_module        libexec/apache2/libphp5.so", and remove the # sign at the beginning. Then search for AddType, and put the following somewhere around it.AddType application/x-httpd-php .phpAddType application/x-httpd-php-source .phpsSave the file, and exit. Then open up System Preferences in Finder, and click on Sharing. Then turn on Web Sharing. That's all you need do to to setup a webserver on Leopard, or upgrade from OS 10.4. You should be able to open up a browser, and enter http://localhost/YOUR_USERNAME/ and see that the server is running. Fri, 26 Oct 2007 14:05:00 PDT http://www.sitecrafting.com/blog/got-api/ gotAPI.com is one of the most useful online resources I've come across, primarily because it places resources spread all over the internet into one simple site. I've been using this for quite some time, and have for the most part I have taken its usefulness for granted. Then it occurred to me that I might not be the only one that could find this tool useful (I know, it was a big 'DUH!' moment). So now I will share this gem with others...gotAPI acts as a portal to almost any API/language reference available on the internet. Wondering what that PHP string function is that splits a string into an array? Pour yourself a nice tall glass of gotAPI and find it in seconds. Or if JavaScript is more your thing, browse the DOM to find the function or property you need. The advantage is that it can all be accessed from here, no need to remember countless URLs or stumble through a poorly designed site to find the documentation page. Simply hit up the site and you're set.Customization is another advantage, with the tabbed interface allowing you to pull up multiple reference pages (all searchable with an expandable tree view to your left). Load up all the API's related to your current project finding an obscure function is now seconds away. Fantastic!The fact that I am still excited about this site after months of using it should tell you just how nifty it is. So stop wasting time reading blogs and go check it out!Link: gotAPI.com Mon, 31 Mar 2008 11:40:00 PDT http://www.sitecrafting.com/blog/dumping-duplicates/ One of the more recent additions to the SiteCrafting CMS arsenal is a comprehensive error logger, tracking all PHP and MySQL errors (by default... other error types can be created on a case by case basis) that occur in new sites we build. Errors are stored in our own intranet system with a timestamp, error body and a site ID (assigned to each client at a different stage of our project workflow). The table looks something like this:+----+---------------------+---------------+---------+| id | logTime             | text          | project |+----+---------------------+---------------+---------+|  2 | 2008-05-14 14:42:15 | A PHP Error   |       1 ||  3 | 2008-05-14 14:42:26 | A PHP Error   |       1 ||  4 | 2008-05-14 14:42:34 | A PHP Error   |       1 ||  5 | 2008-05-14 14:42:47 | A MySQL Error |       1 ||  6 | 2008-05-14 14:42:56 | A MySQL Error |       1 ||  7 | 2008-05-14 14:43:05 | A PHP Error   |       2 ||  8 | 2008-05-14 14:43:10 | A PHP Error   |       2 ||  9 | 2008-05-14 14:43:21 | A MySQL Error |       2 |+----+---------------------+---------------+---------+8 rows in set (0.00 sec)Obviously sometimes we get duplicate errors coming through. Aside from being mere mortals who aren't always fast enough to correct an error before it recurs, one of the first stages of debugging is to try and replicate the error. These are often pretty easy to manage. It's pretty trivial to search for all matching errors, check them all, and delete them. Sometimes, though, this just doesn't cut it. Like when there are a few different errors with 10,000 occurrences apiece. First off: how does an error show up 10,000 times before we catch it? Infinite loops.  Recently this bit of code gave us some trouble:while(!feof($handle)) {    $line = fread($handle);}This is great until your $handle file fails to open, or closes unexpectedly, at which point each execution of feof throws an error AND returns false, allowing fread to execute and throw a slightly different error, and so on.There are at least two ways to handle this. We can just update the timestamp of an error every time it is repeated rather than inserting. This turns out to be impractical because it is sometimes important to see the breadth of timing for a given error. Did it happen all at once? Was it clustered at a few moments of downtime? Or was it an ongoing problem?So instead we need a way to, at our discretion, delete all but one copy of a given (or all) errors. I say all but one because it can be worthwhile to keep a copy for the records, or if you want to clear the logger of duplicates of an as yet unresolved query (for example, if a sudden batch of 20,000 errors starts clogging the system and slowing down your queries). My first inclination was to use a subquery, like so:DELETE FROM errorsWHERE (id, text) NOT IN (    SELECT id, text    FROM errors    GROUP BY text)However, as I was quickly reminded, MySQL does not allow you to delete rows in a table from which you are currently selecting in a subquery. Which is fine, because I'm sure this is far from the quickest solution.My next try had much more luck:DELETE e2.*FROM errors e    INNER JOIN errors e2 ON         e2.text = e.text AND         e2.project = e.project AND        e2.id < e.idThis works best with an auto-increment errors.id field, because that guarantees that we are saving the most recent instance of the error. The timestamp is unreliable for this purpose, since in examples like the infinite loop problem, errors are rolling in far too quickly to have unique stamps.From an administrative standpoint, there are faster and more robust possibilities. You can populate a new table with the results of:SELECT * FROM errorsGROUP BY text, projectand then delete the original table and rename them, for example. However, this doesn't work from an application user standpoint, as the account coming in from PHP should never, at least in this context, have CREATE TABLE privileges.As long as order of entries doesn't matter to you, this method should work with any non-incrementing primary key as well. Wed, 14 May 2008 16:15:00 PDT http://www.sitecrafting.com/blog/mysql-tidbits-xor-toggle/ A web app full of data is often going to be full of tables (at least on the administration end). A listing of users here, all the recently created events there. Often for readability we will alternate rows between two slightly different background colors.This can be handled on the PHP end with a bit of math and a counter, like so:$count = 0;while( $row = mysql_fetch_assoc($res) ) {    if( $count % 2 == 0 )         $background_color = 'white';    else        $background_color = 'gray';    /* OUTPUT TABLE CONTENTS WITH GIVEN BACKGROUND */    $count++;}However, it can also be done entirely in-database, via creative use of the XOR operator:mysql> SET @toggle = 0;Query OK, 0 rows affected (0.00 sec)mysql> SELECT id,    ->        IF( (@t := @t XOR 1),    ->            'gray',    ->            'white'    ->        ) AS background    -> FROM pg;+----+------------+| id | background |+----+------------+|  1 | gray       | |  2 | white      | |  3 | gray       | |  4 | white      | |  5 | gray       | |  6 | white      | |  7 | gray       | |  8 | white      | | 11 | gray       | +----+------------+9 rows in set (0.00 sec)Since XOR (the boolean exclusive or operator) means that an expression evaluates as true when one and only one side of the operation is true, 0 XOR 1 will evaluate to 1 and 1 XOR 1 will evaluate to 0. Repeatedly XORing the same value (the @t variable) creates a toggle, flipping back and forth between zero and 1. The IF statement then translates those numbers into the appropriate colors, and presto! The color is now ready and waiting as you retrieve each row.Other UsesThis is a pretty trivial difference, at least on such small listing tables. However, the XOR toggle construct can also be useful for updates. Say you have a field that you need toggled between true and false with an update statement.mysql> SELECT * FROM toggler;+----+--------+| id | toggle |+----+--------+|  1 |      0 | |  2 |      1 | +----+--------+2 rows in set (0.00 sec)mysql> UPDATE toggler SET toggle = toggle XOR 1;Query OK, 2 rows affected (0.00 sec)Rows matched: 2  Changed: 2  Warnings: 0mysql> SELECT * FROM toggler;+----+--------+| id | toggle |+----+--------+|  1 |      1 | |  2 |      0 | +----+--------+2 rows in set (0.00 sec)By updating the toggle field to an XOR 1'ed version of itself, all 1's become 0's and 0's become 1's. Fri, 11 Jul 2008 16:33:00 PDT http://www.sitecrafting.com/blog/finding-needle-in-mysql-haystack-1/ (First in what will probably be a series of blogs as we move all our projects to a replicated, MySQL 5.0 environment, and I finally get to start playing with all the useful features that come with it)Say you've got a client, Mystery Client A. Mystery Client A has hired a marketing consultant. As a part of their rebranding efforts, they have decided to refactor their company spelling convention to MysteryClientA!, for whatever reason. That's fine for replacing a few logos, but MCA has a database-driven content management system, and their name is riddled throughout the database in page content, event description, news headlines and so forth. Your job is now to sift through the entire system and apply the newly crafted spelling to the entire database. So what do you do?Solution 1 - Crawl the SiteBrowse the site (or delegate an intern to browse the site) from front to back and left to right, looking for any page with the offending text. Fine if you've got a small site or someone with a lot of extra time on their hands, but becomes very quickly impractical.Solution 2 - Search the contentSlightly more efficient is to go to each table that serves public content and search on the appropriate fields. Eliminates the hassle of dealing with large numbers of rows, but if your site is distributed across too many tables, or if you're stuck in a system that you don't know well enough, it is still tedious work.Solution 3 - Stored ProcedureRun a procedure that checks every string-type field in the database, regardless of table structure, for matching content.One line of code (after development, of course) to show you exactly what needs changing? Sounds like a winner. Before I get into the details, here is the final procedure code (first draft), from which I will be snippeting later.(Disclaimer: I am still very much in the learning stage of MySQL stored routine programming, so I am quite open to any efficiency, speed, or just general intelligence improvements anyone wants throw at me. I will post improved iterations as I write them.)The goal of this version is to return a result set in which each row contains enough information to find one relevant field: this breaks down to the table name, column name, table primary key fields and the values for those fields (remember, we want this to be structure-independent, so we can't assume an id number, or even a single-field key).The problem can be broken down into two steps: find the columns and search the columns. But first, the stub:DROP PROCEDURE IF EXISTS `fieldSearch`;DELIMITER $$CREATE PROCEDURE `fieldSearch`(IN search VARCHAR(255))    READS SQL DATABEGIN    -- utility variable for the exiting of loops    DECLARE done TINYINT DEFAULT 0;    -- to hold the table and column name of each relevant row    DECLARE tab, col VARCHAR(64);    -- to hold the list of columns making up the primary key of the table in tab    DECLARE keyColumnList TEXT;    ... the processing code goes here ...END $$Step 1: Find the columnsAs long as we're in MySQL 5 mode, we've got more in our corner than just stored procedures. The metadata (I like saying metadata) in information_schema has just what we need to grab our list of string columns, using the following query:SELECT c.table_name, c.column_name,     GROUP_CONCAT(kcu.column_name ORDER BY kcu.ordinal_position) AS key_columnsFROM information_schema.columns c    INNER JOIN information_schema.key_column_usage kcu        ON kcu.table_schema = c.table_schema AND kcu.table_name = c.table_nameWHERE c.table_schema = 'ccf'    AND c.data_type IN('varchar', 'char', 'text')    AND kcu.constraint_name = 'PRIMARY'GROUP BY c.table_name, c.column_name;Grabbing table_name and column_name from information_schema.columns gets us our core data, filtered against the three main string data types: varchar, char and text. Joining to information_schema.key_column_usage and filtering against the key name 'PRIMARY' retrieves the key information needed to grab a particular unique row out of the given table. Since we only want one row per relevant column, and since we'll have a use for a comma-separated list of the information later, we use the GROUP_CONCAT aggregate function to get the primary key info all together, grouped by table_name and column_name.Step 2: Search the columnsBefore we can iterate through the column results, we need to prepare for output. The final result will make use of the following temporary table:CREATE TEMPORARY TABLE IF NOT EXISTS MatchedColumns (    matchTable VARCHAR(64),    matchColumn VARCHAR(64),    keyColumns TEXT,    keyValues TEXT);(If you are going to be running this procedure more than once on a given connection, you'll need to make sure MatchedColumns is cleared out, either by dropping the table and then recreating it or by truncating or deleting the contents)Now we iterate through the results with a cursor, populating the tab, col and keyColumnList variables, which can then be used to generate the dynamic INSERT statement which will, in turn, populate the MatchedColumns table. Since there is not currently support for dynamic table and column population in a query, we resort to a prepared statement, built out of a concatenated string.SET @insertQuery =  CONCAT("    INSERT INTO MatchedColumns    SELECT '", tab, "' AS matchTable,        '", col, "' AS matchColumn,        '", keyColumnList, "' AS keyFields,        CONCAT_WS(',', ", keyColumnList, ") AS keyValues    FROM ", tab, "    WHERE ", col, " LIKE '%", search, "%'");-- execute queryPREPARE insertStmt FROM @insertQuery;EXECUTE insertStmt;This is where that comma-delimited listing of primary key columns comes in handy. If the initial query produces the following result:+--------------------+-----------------+-------------------------+| table_name         | column_name     | key_columns             |+--------------------+-----------------+-------------------------+| item_categories    | description     | item_id,category_id     |+--------------------+-----------------+-------------------------+the constructed query will be as follows:INSERT INTO MatchedColumnsSELECT 'item_categories' AS matchTable,     'description' AS matchColumn,    'item_id,category_id' AS keyFields,    CONCAT_WS(',', item_id,category_id) AS keyValuesFROM item_categoriesWHERE description LIKE '%search_term%'By virtue of already being comma-separated, the primary key field listing fits nicely into the CONCAT_WS function, providing a similarly comma-separated listing of the equivalent value set. Once the result set is exhausted, all that remains is to push the contents of MatchedColumns to the caller, using:SELECT * FROM MatchedColumns;This will produce something like:+--------------------+-----------------+-------------------------+-------------+| matchTable         | matchColumn     | keyColumns              | keyValues   |+--------------------+-----------------+-------------------------+-------------+| item_categories    | description     | item_id,category_id     | 238,123     |+--------------------+-----------------+-------------------------+-------------+| item_categories    | description     | item_id,category_id     | 114,22      |+--------------------+-----------------+-------------------------+-------------+| item_categories    | description     | item_id,category_id     | 7,93        |+--------------------+-----------------+-------------------------+-------------+Step 3: Handling the resultsI know, I said two steps. And there were two steps in the procedure itself. What it gives you is all the information you need to find any given matching content block. It is not particularly useful like this, however. From there, it all depends on what you want to do. For a pure find and replace, as in the rebranding example, a lot can be stripped out. You can jump straight from finding the string columns to an update using REPLACE, which with these results will look like:UPDATE item_categoriesSET description = REPLACE(description, 'Mystery Client A', 'MysteryClientA!')Finding each row individually becomes necessary when a more hands-on, case-by-case treatment is needed. The information in the table returned can be manipulated into a select statement one at a time on the outside, or the select statements can be generated instead of the table, depending on your needs.More information:As linked to earlier, the official documentation on stored procedures can be found here. I have been using the excellently written O'Reilly book MySQL Stored Procedure Programming, by Guy Harrison and Stephen Feuerstein to further my own knowledge, though it may be apparent to a stored procedure expert that I'm only a few chapters in so far. Tue, 19 Aug 2008 17:00:00 PDT http://www.sitecrafting.com/blog/mysql-login-truncation/ Stefan over at Suspekt brought up some interesting security vulnerabilities based on MySQL's column truncation tendencies (when not in strict mode), so I thought I'd add my own to the pile, this one right in the grant tables.MySQL's user table restricts user names to 16 characters (and hosts to 60). Any attempt to create a user with a longer login results in an error. However, unlike Stefan's example where a field is compared, then truncated and then inserted, MySQL actually truncates a login attempt before processing it.First, just to confirm that longer usernames are not permitted:mysql> GRANT ALL PRIVILEGES ON test.* TO 'toomanycharacters'@'localhost' -> IDENTIFIED BY 'pass';ERROR 1145 (42000): The host or user argument to GRANT is too longNext we create a user with the maximum length:mysql> GRANT ALL PRIVILEGES ON test.* TO 'sixteencharacter'@'localhost' -> IDENTIFIED BY 'pass';Query OK, 0 rows affected (0.00 sec)Test the login the way it ought to go:$ mysql -u sixteencharacter -ppassWelcome to the MySQL monitor.  Commands end with ; or g.Your MySQL connection id is 34510 to server version: 5.0.22-standardAnd finally, log in with a too-long username, matching on the first sixteen characters:$ mysql -u sixteencharacterASDF -ppassWelcome to the MySQL monitor.  Commands end with ; or g.Your MySQL connection id is 34517 to server version: 5.0.22-standardWait, what?There is no user in the system called 'sixteencharacterASDF'...mysql> SELECT User, Host FROM user WHERE User = 'sixteencharacterASDF';Empty set (0.00 sec)...which means that the login process, instead of rejecting what is fundamentally an invalid account, says "well, that's too long, so we'll assume they meant just the first sixteen characters."Now, granted you have to get the first sixteen characters right in the first places, so it doesn't make it any easier for a hacker to guess. But I am still of the opinion that if the username is wrong, the user shouldn't be allowed in.It also brings up an interesting question (which I don't have the resources to test at the moment): does the same truncation happen on a host name? could 'test'@'subdomain-that-is-way-too-long-too-be-useful.sitecrafting.com' access a database restricted to 'test'@'subdomain-that-is-way-too-long-too-be-useful.sitecrafting.co'? Another argument for restricting your user access by IP rather than domain, I suppose. Wed, 20 Aug 2008 11:20:00 PDT http://www.sitecrafting.com/blog/stats-in-mysql-pt-outliers/ With most large sets of data, especially numerical data, statistical analysis plays a key role. You can't be bothered to look at every record yourself; that's what computers are for.One useful tool in any statistical analysis is the identification of outliers. Assuming you have a normally distributed set of data, outliers can help to identify user error in the data entry process, or genuine spikes in the data. Once found, these numbers can be set aside for closer analysis or eliminated to normalize the data set.There are many different methods for identifying outliers, with varying levels of rigor. Here I'll just demonstrate one of the simplest definitions: an outlier is any value greater than three standard deviations away from the mean.First we'll need some data. To demonstrate, I've created the simplest of tables:CREATE TABLE randomNumbers (    value INT(11));And populated it with something vaguely resembling normal data, about 10,000 rows of:INSERT INTO randomNumbers(value) VALUES(    ROUND(RAND() * 1000) +    ROUND(RAND() * 1000) +    ROUND(RAND() * 1000) +    ROUND(RAND() * 1000) +    ROUND(RAND() * 1000) +    ROUND(RAND() * 1000) +    ROUND(RAND() * 1000) +    ROUND(RAND() * 1000) +    ROUND(RAND() * 1000) +    ROUND(RAND() * 1000))The formula for finding the number of standard deviations a point is from the sample's mean (in terms of MySQL aggregate functions) is (value - AVG(value) ) / STDDEV(value). Unfortunately, due to the way aggregate functions work, this doesn't quite fly:mysql> SELECT value, (value - AVG(value)) / STDDEV(value) -> FROM randomNumbers;ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clauseSince we need to work with the full data set, we can't reasonably use GROUP BY, so we need to get the mean and standard deviation by some other method. Assuming that the data set is still growing, we don't want to risk grabbing this information separately, so instead we will join it in via a derived table (a.k.a. a subquery in the FROM clause):mysql> SELECT num.value, agro.mean, agro.dev    -> FROM randomNumbers num    -> CROSS JOIN ( -> SELECT AVG(value) AS mean, STDDEV(value) AS dev -> FROM randomNumbers -> ) agro    -> ORDER BY num.value;+-------+-----------+----------+| value | mean      | dev      |+-------+-----------+----------+|  1462 | 5006.7004 | 908.8870 | |  1827 | 5006.7004 | 908.8870 | |  1980 | 5006.7004 | 908.8870 | ...|  8087 | 5006.7004 | 908.8870 | |  8094 | 5006.7004 | 908.8870 | |  8212 | 5006.7004 | 908.8870 | +-------+-----------+----------+10166 rows in set (0.04 sec)Now that this aggregate information is available to each row in the result set, we can calculate the number of deviations for each value:mysql> SELECT num.value, (value - mean) / dev AS num_devs    -> FROM randomNumbers num    -> CROSS JOIN ( -> SELECT AVG(value) AS mean, STDDEV(value) AS dev -> FROM randomNumbers -> ) agro    -> ORDER BY num.value;+-------+-------------+| value | num_devs    |+-------+-------------+|  1462 | -3.90004522 | |  1827 | -3.49845514 | |  1980 | -3.33011739 | ...|  5006 | -0.00077061 | |  5007 |  0.00032963 | ...|  8094 |  3.39679146 | |  8212 |  3.52662058 | +-------+-------------+10166 rows in set (0.07 sec)Since we've defined an outlier as any value that is more than three standard deviations away from the mean, we filter on the absolute value of num_devs to get all outliers in both directions:mysql> SELECT num.value, (num.value - agro.mean) / agro.dev AS num_devs     -> FROM randomNumbers num    -> CROSS JOIN ( -> SELECT AVG(value) AS mean, STDDEV(value) AS dev -> FROM randomNumbers -> ) agro    -> WHERE ABS( num.value - agro.mean ) / agro.dev > 3    -> ORDER BY num.value;+-------+-------------+| value | num_devs    |+-------+-------------+|  1462 | -3.90004522 | |  1827 | -3.49845514 | |  1980 | -3.33011739 | |  2071 | -3.22999493 | |  2142 | -3.15187741 | |  2187 | -3.10236630 | |  2254 | -3.02864977 | |  2254 | -3.02864977 | |  7816 |  3.09092285 | |  7888 |  3.17014062 | |  7958 |  3.24715790 | |  7962 |  3.25155888 | |  7997 |  3.29006752 | |  8076 |  3.37698702 | |  8077 |  3.37808726 | |  8087 |  3.38908973 | |  8094 |  3.39679146 | |  8212 |  3.52662058 | +-------+-------------+18 rows in set (0.05 sec)If you're just looking for spikes on one extreme or the other, you can ditch the ABS in the WHERE clause (and change it to < -3 to find the low end). And of course you can adjust the starting point (currently 3) if you want a larger or smaller definition of an outlier.Other methodsThere are certain weaknesses to this method. If memory serves me, use of aggregate functions does not work with the query cache, so the AVG and STDDEV are calculated at every row, despite being the same all the way through. If you are working with a transactional storage engine using the REPEATABLE READ isolation level, you can start a transaction, assign the mean and deviation to user variables, and work with the saved values rather than relying on a subquery.There are also other mathematical methods. If you calculate the interquartile range of the data (the difference between the quartiles, or the 75th and the 25th percentiles), you can treat any data more than 1.5*IQR outside of the aforementioned quartiles as an outlier. I won't get too deeply into that, as I'd really just be copying and pasting Roland Bouman's article on finding the nth percentile.Anybody have any other interesting techniques to share? Tue, 26 Aug 2008 08:45:00 PDT http://www.sitecrafting.com/blog/stats-in-mysql-pt-ii/ (Author's note: not necessarily actually a practical idea. But fun!)So pictured here is a histogram of a moderately large set of random integers. Each vertical line represents the total number of entries at each particular integer. Since each number is made up of multiple random factors (10 different random numbers, each between 0 and 100, added together), the distribution tends toward a bell curve.So how did I build the graph? Excel? PHP? Nope. Just a MySQL query.Admittedly I cheated a bit. That image is zoomed out and rotated 90 degrees. Here's a snippet of the actual query output:Each "bucket" has one asterisk for each entry that falls inside it. Using the same single-column database as in pt. 1, and populating the data in a similar fashion with 30,000 rows ranging from 0 to 1000, it actually takes a remarkably simple query to generate a histogram like this:mysql> SELECT `value`, RPAD('', COUNT(`value`), '*') AS bar    -> FROM `randomNumbers`    -> GROUP BY `value`;The RPAD statement starts with an empty string and adds as many '*' characters as there are matches of the given value in the database.In practical terms, would you ever really want to use your database to build graphical presentation like this? Probably not. Logically, you really just want to run the following query and handle your display on the application side.mysql> SELECT `value`, COUNT(`value`) AS bar    -> FROM `randomNumbers`    -> GROUP BY `value`;But it can be a quick and dirty way to get an overview of your data's distribution. There are a couple kinks to iron out: this query doesn't show buckets with zero entries. This shouldn't be a huge concern, since again, this method of graphical output should only be used for a glancing overview, and not a precision statistical analysis. With enough data, these empty buckets should only occur out at the very fringe anyway (surrounding the outliers mentioned in the last entry). Wed, 27 Aug 2008 09:51:00 PDT http://www.sitecrafting.com/blog/stats-in-mysql-pt-iii/ Not as snazzy-looking as my last two, but a useful query nonetheless. One the applications we built over the years, among other things, takes a list of names and addresses (currently a little over 2600), and selects around 200 of them at random. The client has been keeping track of the alphabetical distribution of last names with each random batch, and was concerned that the apparent weighting of the list toward the beginning of the alphabet was evidence that the lists were not truly random.Fortunately I was able to get a quick statistical breakdown of the overall list, using the following: SELECT SUBSTRING(ln.last_name, 1, 1) AS lInitial,     COUNT(ln.id) AS lTotal,    100*COUNT(ln.id)/ncount.total AS lPercentFROM names ln    CROSS JOIN (        SELECT COUNT(*) AS total         FROM names    ) AS ncountGROUP BY lInitialSUBSTRING(ln.last_name, 1, 1) gets the first letter of the last name. Grouping on this expression via the alias initial ensures that subsequent COUNT(ln.id) result is divided up on a by-initial basis.If you just want totals this is enough, but for statistical information like percentage you need the total COUNT, which you can't grab directly because of the GROUP BY that was needed for the earlier part. In comes the derived table, via CROSS JOIN (SELECT COUNT(*) AS total FROM names) AS ncount. In this case, the table is MyISAM, so COUNT(*) is a pre-stored constant, preventing any significant performance hit from calculating the count repeatedly. From here it is a simple step to make a readable percentage, using ROUND(100*COUNT(ln.id)/ncount.total, 2), which produces numbers like 25.34, 1.20, etc, which is generally nicer than 0.015823 and the like.Just because that was a little boring, here's an added problem: statistical distribution of each letter for both first and last initial in one shot.This one gets a bit more complex, as you need another set of COUNT statements, this time grouping on the first initial of the first name. This means another subqueryLEFT JOIN (    SELECT SUBSTRING(n.first_name, 1, 1) AS fInitial,         COUNT(p.id) AS fTotal    FROM names n    GROUP BY fInitial) fn ON fn.fInitial = SUBSTRING(ln.last_name, 1, 1)I had hoped to use the lInitial alias in the ON clause, but that didn't fly, unfortunately, so I was forced to recreate the SUBSTRING expression.Now that we have the numerical distribution of first initials, we can perform the same mathematical operations as we did on the last initials, and come out with this final query:SELECT SUBSTRING(ln.last_name, 1, 1) AS lInitial,     COUNT(ln.id) AS lTotal,     ROUND((COUNT(on.id)/ncount.total)*100, 2) AS lPercent,     fn.fTotal,     ROUND((fn.fTotal/pcount.total)*100, 2) AS fPercentFROM names fn    LEFT JOIN (            SELECT SUBSTRING(n.first_name, 1, 1) AS fInitial,                COUNT(n.id) AS ftotal            FROM names n            GROUP BY fInitial        ) fn ON fn.fInitial = SUBSTRING(ln.last_name, 1, 1)    CROSS JOIN (            SELECT COUNT(*) total FROM names        ) AS ncountGROUP BY lInitialIt's not terribly pretty, but it does the job. Since none of the subqueries are correlated, they shouldn't impact performance significantly. Feel free to contribute thoughts on cleaning it up a bit.(Oh, in case you were wondering, J was the most common first initial, and S generally dominated last names. Guess we've got a strong John Smith population here in western Washington.) Mon, 06 Oct 2008 16:43:00 PDT http://www.sitecrafting.com/blog/variable-resolution-in-server-side/ I was working on a content retrieval procedure recently, and hit a snag. I needed to retrieve the content of one field, do some other processing, then compare that content to a field in another table. That middle processing step was preventing me from just doing a JOIN between the two.Logically, what I wanted to do was this:BEGIN    -- set up variable to store name of table    DECLARE storedValue VARCHAR(64);        -- query to grab the value used later    SELECT firstValue    INTO storedValue    FROM FirstTable    WHERE id = 1;    -- other stuff happens here    DECLARE tableCur CURSOR FOR    SELECT *    FROM SecondTable    WHERE secondValue = storedValue;    OPEN tableCur;    tableLoop: LOOP        -- process results    END LOOP tableLoop;    CLOSE tableCur;ENDUnfortunately this doesn't fly. Because the creation of the cursor is a DECLARE statement, it has to go before any actual processing in the block. I thought I was stuck: the cursor needed to come before other queries for syntax, but couldn't build its own query without data from another.However, on a lark I decided to reverse the two, like so:BEGIN    -- set up variable to store name of table    DECLARE storedValue VARCHAR(64);    DECLARE tableCur CURSOR FOR    SELECT *    FROM SecondTable    WHERE secondValue = storedValue;        -- query to grab the value used later    SELECT firstValue    INTO storedValue    FROM FirstTable    WHERE id = 1;    -- other stuff happens here    OPEN tableCur;    tableLoop: LOOP        -- process results    END LOOP tableLoop;    CLOSE tableCur;ENDI was more than a bit surprised to discover that it worked perfectly. Apparently (this was news to me), when you use a local variable as part of a cursor definition, that variable is resolved not at declaration but at execution, when the cursor is open. Essentially the variable is passed into the cursor by reference, rather than value. Interesting stuff.(I was eventually able to compose a more involved query that let me use a join, and cut out that middle step, but I suspect that the ability to use variables in cursors as placeholders to be populated later is one that will come in handy in the future.) Mon, 13 Oct 2008 20:45:00 PDT http://www.sitecrafting.com/blog/goto-code-in-php/ Sometimes, you just need a way to GOTO. Here is a little trick that will duck out of a section of code using break The GOTO style of coding, common in languages like BASIC, have been marked by developers as generally a bad idea. It makes code very unreadable by forcing the writer to remember where everything is pointing. It turns your code into a big mess. Well, some times you need that functionality. Case in point, I found myself creating an SQL query that joined two many-to-many related tables using IDs. The problem was that I was rewriting the query over and over, when I could have just pulled it out into a single query statements that just used variables.Before:function addManyToMany($vars) { $sql = " SELECT id FROM table_a WHERE desc = '{$vars['desc']}' "; $res = mysql_query($sql); if(mysql_num_rows($res) == 1) { $a_id = mysql_results($res, 'id', 0); $sql = " INSERT INTO join_table SET table_a_id = $a_id, table_b_id = {$vars['b_id']} "; return mysql_query($sql); } $sql = " SELECT id FROM table_a WHERE desc LIKE '%{$vars['desc']}%' "; $res = mysql_query($sql); if(mysql_num_rows($res) == 1) { $a_id = mysql_results($res, 'id', 0); $sql = " INSERT INTO join_table SET table_a_id = $a_id, table_b_id = {$vars['b_id']} "; return mysql_query($sql); } $sql = " INSERT INTO table_a SET {$vars['sql']} "; mysql_query($sql); $a_id = mysql_insert_id(); $sql = " INSERT INTO join_table SET table_a_id = $a_id, table_b_id = {$vars['b_id']} "; return mysql_query($sql);}As you can see, the query to insert the many-to-many table entry is the same for each case that I have. The variables are of the same type, and do roughly the same thing. This list of cases could easily grow as time progresses. And the query to insert into the many-to-many table could be changed, which would mean editing many lines of code for just one update. There is a better way. After:function addManyToMany($vars) { do { $sql = " SELECT id FROM table_a WHERE desc = '{$vars['desc']}' "; $res = mysql_query($sql); if(mysql_num_rows($res) == 1) { $a_id = mysql_results($res, 'id', 0); break; } $sql = " SELECT id FROM table_a WHERE desc LIKE '%{$vars['desc']}%' "; $res = mysql_query($sql); if(mysql_num_rows($res) == 1) { $a_id = mysql_results($res, 'id', 0); break; } $sql = " INSERT INTO table_a SET {$vars['sql']} "; mysql_query($sql); $a_id = mysql_insert_id(); } while(false); $sql = " INSERT INTO join_table SET table_a_id = $a_id, table_b_id = {$vars['b_id']} "; return mysql_query($sql);}Now all the cases are wrapped in a do while clause. This allows for arbitrary breaks in code which will drop out right above the insert into the many-to-many table. Now there is only one insert query which will greatly improve manageability. The other way to solve this would have been to create a function (maybe a lambda?) that would take in the id's and insert them. But this is just an example of what can be done. Fri, 10 Oct 2008 10:43:00 PDT http://www.sitecrafting.com/blog/mysql-tidbits-shot-ordering/ One of the common needs for a content management system (hey, that's what we make!) is some form of page ordering. Clients need to be able to manually order pages to suit their fancy, rather than relying on something arbitrary like update time or alphabetical order. For this we use a simple numeric field. On any given given page in the admin center, the user can shuffle around all that page's siblings, which are then posted and have their ordernum fields reset. Typically this is done with a query apiece, as one would expect when trying to update multiple rows with multiple different values on multiple keys. Wouldn't it be nice, though, if there were an easy way to perform this same entire action with a single query?We'll start with the simplest of simple tables: an auto-increment ID and an ordernum field, currently set to match the ID:mysql> SELECT * FROM pages;+----+----------+| id | ordernum |+----+----------+|  1 |        1 | |  2 |        2 | |  3 |        3 | |  4 |        4 | |  5 |        5 | +----+----------+5 rows in set (0.00 sec)Now, say the user submits an update, choosing to order the pages as follows: 5, 2, 3, 1, 4 (the page with id=5 first, id=2 second, etc.) We want to achieve the same result as this series of statements:mysql> UPDATE pages SET ordernum = 4 WHERE id = 1;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> UPDATE pages SET ordernum = 2 WHERE id = 2;Query OK, 0 rows affected (0.00 sec)Rows matched: 1  Changed: 0  Warnings: 0mysql> UPDATE pages SET ordernum = 3 WHERE id = 3;Query OK, 0 rows affected (0.00 sec)Rows matched: 1  Changed: 0  Warnings: 0mysql> UPDATE pages SET ordernum = 5 WHERE id = 4;Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> UPDATE pages SET ordernum = 1 WHERE id = 5;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0The key to this is use of MySQL's FIELD() function. FIELD accomplishes a fairly basic task: finding a value's location in a list, where the first term is the needle, and the remaining terms the haystack. For example, FIELD("a", "b", "c", "a") will return 3, because the search term, "a", appears third on the following list.Using this function, we still loop through all the newly submitted ordernum values, but rather than running an UPDATE each time, we build a quick comma-separated list: 5,2,3,4,1. Or better still, just use the implode function in php (assuming these values are coming through as an array). We can then use this list as the haystack, and the id field as the needle, like so:mysql> SELECT id, FIELD(id,5,2,3,1,4)    -> FROM pages;+----+---------------------+| id | FIELD(id,5,2,3,1,4) |+----+---------------------+|  1 |                   4 | |  2 |                   2 | |  3 |                   3 | |  4 |                   5 | |  5 |                   1 | +----+---------------------+5 rows in set (0.00 sec)This shows us a direct correlation between each row in the table and the new ordernum, because FIELD is returning each id's location in the newly submitted order. This result can easily be translated into an UPDATE statement instead of a SELECT:mysql> UPDATE pages      -> SET ordernum = FIELD(id,5,2,3,1,4);Query OK, 5 rows affected (0.00 sec)Rows matched: 5  Changed: 3  Warnings: 0mysql> SELECT * FROM pages;+----+----------+| id | ordernum |+----+----------+|  1 |        4 | |  2 |        2 | |  3 |        3 | |  4 |        5 | |  5 |        1 | +----+----------+5 rows in set (0.00 sec)And voila! All the ordering updated in a single shot.So what if you only want to update a small set of rows (say, only one branch of a page tree)? Well, if your tree is based on a parent_id field in each row, you can add that in a WHERE clause:mysql> UPDATE pages      -> SET ordernum = FIELD(id,5,2,3,1,4);    -> WHERE parent_id = 9Query OK, 5 rows affected (0.00 sec)Rows matched: 5  Changed: 3  Warnings: 0If you don't have such a field, you can use the very same comma-separated listed from your FIELD function, like so:mysql> UPDATE pages    -> SET ordernum = FIELD(id,5,2,3,1,4)    -> WHERE id IN(5,2,3,1,4);Query OK, 5 rows affected (0.00 sec)Rows matched: 5  Changed: 3  Warnings: 0In addition to the mass update, this function can be used for on-the-fly output ordering by shifting it to the ORDER BY clause:mysql> SELECT id        -> FROM pages    -> ORDER BY FIELD(id,5,2,3,1,4);+----+| id |+----+|  5 | |  2 | |  3 | |  1 | |  4 | +----+5 rows in set (0.00 sec)And don't forget that this ORDER BY trick is true of any function that will return a numeric value for each row in the result set. Mon, 27 Apr 2009 10:00:00 PDT