11 Jul
MySQL Tidbits: The 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:
However, it can also be done entirely in-database, via creative use of the XOR operator:
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.
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 Uses
This 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: 0
mysql> 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.
Coding Techniques, MySQL
by Joe Izenman | 7/11/2008 4:33pm
by Joe Izenman | 7/11/2008 4:33pm
I'd go for the php method you suggested first. The sql method is a bit unholy since it mixes presentation logic with data access logic. I think that's something that almost everyone can agree about?
Left by Emil | Jul 11, 2008
@ Emil
I disagree with your comment on presentation logic vs data access logic. Presentation logic is the implementation of the choice of background colour in the presentation layer. The act of choosing a colour is not the same as implementing that choice, therefore the choice can be made in the data access layer justas it can in the business logic layer.
The only change that I would make would be to set the choice to either "odd" or even", then have the presentation layer implement that choice, such as a class in a CSS file.
Left by Tony Marston | Jul 12, 2008
I just wanted to point out that this can also be handled on the presentation side via the nth-child pseudo-selector in CSS. Unfortunately, it looks like while the latest official releases of Safari (3.1.2) and Opera (9.51) obey the selector, the latest releases of Firefox (3) and Internet Explorer (7) do not.
http://reference.sitepoint.com/css/pseudoclass-nthchild
Left by Bernie Zimmermann | Jul 12, 2008
I would agree that the direct specification of color is a little much. I put that in tho make the intent of the example a bit more clear. However, I feel like whether a row is odd or even is well within the scope of the data layer. That being the case, I see it as up to the developer how they express that odd vs. even relationship, whether by 1 and 0, 'odd' and 'even' or a particular class name. It is, as Tony mentions, the front-end developer's decision what to do with that data.
Left by Joe Izenman | Jul 14, 2008