1 | <?php |
---|
2 | /** |
---|
3 | * @version V4.50 6 July 2004 (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved. |
---|
4 | * Released under both BSD license and Lesser GPL library license. |
---|
5 | * Whenever there is any discrepancy between the two licenses, |
---|
6 | * the BSD license will take precedence. |
---|
7 | * |
---|
8 | * Set tabs to 4 for best viewing. |
---|
9 | * |
---|
10 | * Latest version is available at http://php.weblogs.com |
---|
11 | * |
---|
12 | * Requires PHP4.01pl2 or later because it uses include_once |
---|
13 | */ |
---|
14 | |
---|
15 | /* |
---|
16 | * Concept from daniel.lucazeau@ajornet.com. |
---|
17 | * |
---|
18 | * @param db Adodb database connection |
---|
19 | * @param tables List of tables to join |
---|
20 | * @rowfields List of fields to display on each row |
---|
21 | * @colfield Pivot field to slice and display in columns, if we want to calculate |
---|
22 | * ranges, we pass in an array (see example2) |
---|
23 | * @where Where clause. Optional. |
---|
24 | * @aggfield This is the field to sum. Optional. |
---|
25 | * Since 2.3.1, if you can use your own aggregate function |
---|
26 | * instead of SUM, eg. $sumfield = 'AVG(fieldname)'; |
---|
27 | * @sumlabel Prefix to display in sum columns. Optional. |
---|
28 | * @aggfn Aggregate function to use (could be AVG, SUM, COUNT) |
---|
29 | * @showcount Show count of records |
---|
30 | * |
---|
31 | * @returns Sql generated |
---|
32 | */ |
---|
33 | |
---|
34 | function PivotTableSQL($db,$tables,$rowfields,$colfield, $where=false, |
---|
35 | $aggfield = false,$sumlabel='Sum ',$aggfn ='SUM', $showcount = true) |
---|
36 | { |
---|
37 | if ($aggfield) $hidecnt = true; |
---|
38 | else $hidecnt = false; |
---|
39 | |
---|
40 | |
---|
41 | //$hidecnt = false; |
---|
42 | |
---|
43 | if ($where) $where = "\nWHERE $where"; |
---|
44 | if (!is_array($colfield)) $colarr = $db->GetCol("select distinct $colfield from $tables $where order by 1"); |
---|
45 | if (!$aggfield) $hidecnt = false; |
---|
46 | |
---|
47 | $sel = "$rowfields, "; |
---|
48 | if (is_array($colfield)) { |
---|
49 | foreach ($colfield as $k => $v) { |
---|
50 | if (!$hidecnt) $sel .= "\n\t$aggfn(CASE WHEN $v THEN 1 ELSE 0 END) AS \"$k\", "; |
---|
51 | if ($aggfield) |
---|
52 | $sel .= "\n\t$aggfn(CASE WHEN $v THEN $aggfield ELSE 0 END) AS \"$sumlabel$k\", "; |
---|
53 | } |
---|
54 | } else { |
---|
55 | foreach ($colarr as $v) { |
---|
56 | if (!is_numeric($v)) $vq = $db->qstr($v); |
---|
57 | else $vq = $v; |
---|
58 | if (strlen($v) == 0 ) $v = 'null'; |
---|
59 | if (!$hidecnt) $sel .= "\n\t$aggfn(CASE WHEN $colfield=$vq THEN 1 ELSE 0 END) AS \"$v\", "; |
---|
60 | if ($aggfield) { |
---|
61 | if ($hidecnt) $label = $v; |
---|
62 | else $label = "{$v}_$aggfield"; |
---|
63 | $sel .= "\n\t$aggfn(CASE WHEN $colfield=$vq THEN $aggfield ELSE 0 END) AS \"$label\", "; |
---|
64 | } |
---|
65 | } |
---|
66 | } |
---|
67 | if ($aggfield && $aggfield != '1'){ |
---|
68 | $agg = "$aggfn($aggfield)"; |
---|
69 | $sel .= "\n\t$agg as \"$sumlabel$aggfield\", "; |
---|
70 | } |
---|
71 | |
---|
72 | if ($showcount) |
---|
73 | $sel .= "\n\tSUM(1) as Total"; |
---|
74 | else |
---|
75 | $sel = substr($sel,0,strlen($sel)-2); |
---|
76 | |
---|
77 | $sql = "SELECT $sel \nFROM $tables $where \nGROUP BY $rowfields"; |
---|
78 | return $sql; |
---|
79 | } |
---|
80 | |
---|
81 | /* EXAMPLES USING MS NORTHWIND DATABASE */ |
---|
82 | if (0) { |
---|
83 | |
---|
84 | # example1 |
---|
85 | # |
---|
86 | # Query the main "product" table |
---|
87 | # Set the rows to CompanyName and QuantityPerUnit |
---|
88 | # and the columns to the Categories |
---|
89 | # and define the joins to link to lookup tables |
---|
90 | # "categories" and "suppliers" |
---|
91 | # |
---|
92 | |
---|
93 | $sql = PivotTableSQL( |
---|
94 | $gDB, # adodb connection |
---|
95 | 'products p ,categories c ,suppliers s', # tables |
---|
96 | 'CompanyName,QuantityPerUnit', # row fields |
---|
97 | 'CategoryName', # column fields |
---|
98 | 'p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID' # joins/where |
---|
99 | ); |
---|
100 | print "<pre>$sql"; |
---|
101 | $rs = $gDB->Execute($sql); |
---|
102 | rs2html($rs); |
---|
103 | |
---|
104 | /* |
---|
105 | Generated SQL: |
---|
106 | |
---|
107 | SELECT CompanyName,QuantityPerUnit, |
---|
108 | SUM(CASE WHEN CategoryName='Beverages' THEN 1 ELSE 0 END) AS "Beverages", |
---|
109 | SUM(CASE WHEN CategoryName='Condiments' THEN 1 ELSE 0 END) AS "Condiments", |
---|
110 | SUM(CASE WHEN CategoryName='Confections' THEN 1 ELSE 0 END) AS "Confections", |
---|
111 | SUM(CASE WHEN CategoryName='Dairy Products' THEN 1 ELSE 0 END) AS "Dairy Products", |
---|
112 | SUM(CASE WHEN CategoryName='Grains/Cereals' THEN 1 ELSE 0 END) AS "Grains/Cereals", |
---|
113 | SUM(CASE WHEN CategoryName='Meat/Poultry' THEN 1 ELSE 0 END) AS "Meat/Poultry", |
---|
114 | SUM(CASE WHEN CategoryName='Produce' THEN 1 ELSE 0 END) AS "Produce", |
---|
115 | SUM(CASE WHEN CategoryName='Seafood' THEN 1 ELSE 0 END) AS "Seafood", |
---|
116 | SUM(1) as Total |
---|
117 | FROM products p ,categories c ,suppliers s WHERE p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID |
---|
118 | GROUP BY CompanyName,QuantityPerUnit |
---|
119 | */ |
---|
120 | //===================================================================== |
---|
121 | |
---|
122 | # example2 |
---|
123 | # |
---|
124 | # Query the main "product" table |
---|
125 | # Set the rows to CompanyName and QuantityPerUnit |
---|
126 | # and the columns to the UnitsInStock for different ranges |
---|
127 | # and define the joins to link to lookup tables |
---|
128 | # "categories" and "suppliers" |
---|
129 | # |
---|
130 | $sql = PivotTableSQL( |
---|
131 | $gDB, # adodb connection |
---|
132 | 'products p ,categories c ,suppliers s', # tables |
---|
133 | 'CompanyName,QuantityPerUnit', # row fields |
---|
134 | # column ranges |
---|
135 | array( |
---|
136 | ' 0 ' => 'UnitsInStock <= 0', |
---|
137 | "1 to 5" => '0 < UnitsInStock and UnitsInStock <= 5', |
---|
138 | "6 to 10" => '5 < UnitsInStock and UnitsInStock <= 10', |
---|
139 | "11 to 15" => '10 < UnitsInStock and UnitsInStock <= 15', |
---|
140 | "16+" =>'15 < UnitsInStock' |
---|
141 | ), |
---|
142 | ' p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID', # joins/where |
---|
143 | 'UnitsInStock', # sum this field |
---|
144 | 'Sum' # sum label prefix |
---|
145 | ); |
---|
146 | print "<pre>$sql"; |
---|
147 | $rs = $gDB->Execute($sql); |
---|
148 | rs2html($rs); |
---|
149 | /* |
---|
150 | Generated SQL: |
---|
151 | |
---|
152 | SELECT CompanyName,QuantityPerUnit, |
---|
153 | SUM(CASE WHEN UnitsInStock <= 0 THEN UnitsInStock ELSE 0 END) AS "Sum 0 ", |
---|
154 | SUM(CASE WHEN 0 < UnitsInStock and UnitsInStock <= 5 THEN UnitsInStock ELSE 0 END) AS "Sum 1 to 5", |
---|
155 | SUM(CASE WHEN 5 < UnitsInStock and UnitsInStock <= 10 THEN UnitsInStock ELSE 0 END) AS "Sum 6 to 10", |
---|
156 | SUM(CASE WHEN 10 < UnitsInStock and UnitsInStock <= 15 THEN UnitsInStock ELSE 0 END) AS "Sum 11 to 15", |
---|
157 | SUM(CASE WHEN 15 < UnitsInStock THEN UnitsInStock ELSE 0 END) AS "Sum 16+", |
---|
158 | SUM(UnitsInStock) AS "Sum UnitsInStock", |
---|
159 | SUM(1) as Total |
---|
160 | FROM products p ,categories c ,suppliers s WHERE p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID |
---|
161 | GROUP BY CompanyName,QuantityPerUnit |
---|
162 | */ |
---|
163 | } |
---|
164 | ?> |
---|