Hack 63. Generate Rows Without Tables

You can use single-value SELECT statements to generate tables. You can use them when you need a small table for your query but you don't have permission to create tables in the database itself.

You can create a table by stacking together a bunch of one-row tables [Hack #62]. Here's a centigrade-to-Fahrenheit conversion that operates on a set of values summoned out of thin air:

mysql> SELECT x centigrade, x*9/5+32 fahrenheit
 -> FROM (SELECT 0 x UNION SELECT 10 UNION SELECT 20
 -> UNION SELECT 30 UNION SELECT 40) t;
+------------+------------+
| centigrade | fahrenheit |
+------------+------------+
| 0 | 32.00 |
| 10 | 50.00 |
| 20 | 68.00 |
| 30 | 86.00 |
| 40 | 104.00 |
+------------+------------+
5 rows in set (0.00 sec)

Notice that you have to alias the x column in the first table of the UNION. The UNION will pick up the column headings given in the first table. Also notice that you must name the whole UNION t or your database will complain.

The equivalent works in Oracle but you must include FROM dual for each SELECT in the UNION:

SELECT x centigrade, x*9/5+32 fahrenheit
FROM ( SELECT 0 x FROM dual
 UNION SELECT 10 FROM dual
 UNION SELECT 20 FROM dual
 UNION SELECT 30 FROM dual
 UNION SELECT 40 FROM dual) t

 

8.6.1. Hacking the Hack

You can populate some user interface controls, such as drop-down menus, from a table or query. You could use this, for example, to produce default menu entries in Access or to support a Perl script to generate a popup menu in HTML.

8.6.1.1. Combo box from a table in Access

In Access, you can specify the Row Source of a combo box as a fixed list of values or as a database query (see Figure 8-7). But sometimes you might want a bit of both of these. You might want the first option to be "No preference," for example, and the rest of them to be taken from a table. You can get exactly this by forming a UNION between the single fixed value and the table that contains the other options:

CREATE TABLE ColorTable
 (colorName CHAR(32))

INSERT INTO ColorTable VALUES ('Red')
INSERT INTO ColorTable VALUES ('Blue')
INSERT INTO ColorTable VALUES ('Yellow') 

Figure 8-7. The ColorPicker combo box

In this query, you get one occurrence of the string 'No Preference' plus one of each of the colors in colorTable. The phrase FROM colorTable is redundant in the first SELECT, but the Access parser insists on a FROM clause in a UNION or a nested query. In the first select of the UNION you could use any table that has at least one row in it:

SELECT DISTINCT 'No preference' FROM ColorTable
UNION
SELECT colorName FROM ColorTable

But how do you make sure that No Preference shows up first? You can't guarantee the order of the options, and you are not allowed to put an ORDER BY in the definition of a view. But you can make up another column specifically to dictate the ordering; having a NULL value in a constant row will ensure that it shows up as the first item. Here's an alternative that forces the ordering:

SELECT txt, ordr FROM(
 SELECT DISTINCT 'No preference' AS txt, NULL AS ordr FROM ColorTable
 UNION
 SELECT colorName, colorName FROM ColorTable)
 ORDER BY ordr;

Here is the output of the preceding statement:

+---------------+-------------+
| No preference | NULL |
| Blue | Blue |
| Red | Red |
| Yellow | Yellow |
+---------------+-------------+

You can go on to use the value that the user selects in a subsequent query. If the value selected from the drop down shows up as choice, you could use the phrase WHERE color LIKE COALESCE(choice,'%'), which gives the actual color (if one has been selected), or the wildcard otherwise. In Access you need to use the variation WHERE color LIKE Nz(choice, '*').

8.6.1.2. Pop-up list from a table in Perl

The SQL code generates a two-column result set. The second column is simply to force the order; you don't need it for the HTML. The selectcol_arrayref method from the DBI package is ideal for returning small lists of values. In this example, you can drop it directly into the CGI package function, popup_menu:

#!/usr/bin/perl
use CGI qw(:standard);
use DBI;
use strict;

print header;
my $dbh=DBI->connect('DBI:mysql:dbname','user','password');
my $sql = <<'xx';
 SELECT DISTINCT 'No preference' AS colorName, NULL
 UNION
 SELECT colorName, colorName FROM colorTable
 ORDER BY 2
xx
print popup_menu('menu_name',
 $dbh->selectcol_arrayref($sql),
 'No Preference');

Here is the same code in PHP:

";
while ($line = mysql_fetch_array($cursor,MYSQL_ASSOC)) {
 echo "

" .$line{'colorName'} . "

"; } echo " "; mysql_close( ); ?>

Figure 8-8 shows what it looks like in Firefox.

Figure 8-8. HTML pop-up from a table