1--TEST--
2Prepared Statements and SELECT UNION
3--EXTENSIONS--
4mysqli
5--SKIPIF--
6<?php
7require_once 'skipifconnectfailure.inc';
8?>
9--FILE--
10<?php
11    require_once 'connect.inc';
12    require_once 'table.inc';
13
14    // Regular (non-prepared) queries
15    print "Using CAST('somestring' AS CHAR)...\n";
16    if (!($res = $link->query("SELECT CAST('one' AS CHAR) AS column1 UNION SELECT CAST('three' AS CHAR) UNION SELECT CAST('two' AS CHAR)")))
17        printf("[001] [%d] %s\n", $link->errno, $link->error);
18
19    $data = array();
20    while ($row = $res->fetch_assoc()) {
21        $data[] = $row['column1'];
22        var_dump($row['column1']);
23    }
24    $res->free();
25
26    // Prepared Statements
27    if (!($stmt = $link->prepare("SELECT CAST('one' AS CHAR) AS column1 UNION SELECT CAST('three' AS CHAR) UNION SELECT CAST('two' AS CHAR)")))
28        printf("[002] [%d] %s\n", $link->errno, $link->error);
29
30    $column1 = null;
31    if (!$stmt->execute() || !$stmt->bind_result($column1))
32        printf("[003] [%d] %s\n", $stmt->errno, $stmt->error);
33
34    $index = 0;
35    while ($stmt->fetch()) {
36        if ($data[$index] != $column1) {
37            printf("[004] Row %d, expecting %s/%s got %s/%s\n",
38                $index + 1, gettype($data[$index]), $data[$index], gettype($column1), $column1);
39        }
40        $index++;
41    }
42    $stmt->close();
43
44    /*
45    Advantage mysqlnd -
46    The metadata mysqlnd has available after prepare is better than
47    the one made available by the MySQL Client Library (libmysql).
48    "libmysql" will give wrong results and that is OK -
49    http://bugs.mysql.com/bug.php?id=47483
50    */
51    if (!($stmt = $link->prepare("SELECT CAST('one' AS CHAR) AS column1 UNION SELECT CAST('three' AS CHAR) UNION SELECT CAST('two' AS CHAR)")))
52        printf("[005] [%d] %s\n", $link->errno, $link->error);
53
54    $column1 = null;
55    /* Note: bind_result before execute */
56    if (!$stmt->bind_result($column1) || !$stmt->execute())
57        printf("[006] [%d] %s\n", $stmt->errno, $stmt->error);
58
59    $index = 0;
60    while ($stmt->fetch()) {
61        if ($data[$index] != $column1) {
62            printf("[007] Row %d, expecting %s/%s got %s/%s\n",
63                $index + 1, gettype($data[$index]), $data[$index], gettype($column1), $column1);
64        }
65        $index++;
66    }
67    $stmt->close();
68
69    // Regular (non-prepared) queries
70    print "Mixing CAST('somestring'AS CHAR), integer and CAST(integer AS CHAR)...\n";
71    if (!($res = $link->query("SELECT 1 AS column1 UNION SELECT CAST('three' AS CHAR) UNION SELECT CAST(2 AS CHAR)")))
72        printf("[008] [%d] %s\n", $link->errno, $link->error);
73
74    $data = array();
75    while ($row = $res->fetch_assoc()) {
76        $data[] = $row['column1'];
77    }
78    $res->free();
79
80    // Prepared Statements
81    if (!($stmt = $link->prepare("SELECT 1 AS column1 UNION SELECT CAST('three' AS CHAR) UNION SELECT CAST(2 AS CHAR)")))
82        printf("[009] [%d] %s\n", $link->errno, $link->error);
83
84    $column1 = null;
85    if (!$stmt->execute() || !$stmt->bind_result($column1))
86        printf("[010] [%d] %s\n", $stmt->errno, $stmt->error);
87
88    $index = 0;
89    while ($stmt->fetch()) {
90        if ($data[$index] != $column1) {
91            printf("[011] Row %d, expecting %s/%s got %s/%s\n",
92                $index + 1, gettype($data[$index]), $data[$index], gettype($column1), $column1);
93        }
94        var_dump($column1);
95        $index++;
96    }
97    $stmt->close();
98
99    /* Advantage mysqlnd - see above... */
100    if (!($stmt = $link->prepare("SELECT 1 AS column1 UNION SELECT CAST('three' AS CHAR) UNION SELECT CAST(2 AS CHAR)")))
101        printf("[012] [%d] %s\n", $link->errno, $link->error);
102
103    $column1 = null;
104    if (!$stmt->bind_result($column1) || !$stmt->execute())
105        printf("[013] [%d] %s\n", $stmt->errno, $stmt->error);
106
107    $index = 0;
108    while ($stmt->fetch()) {
109        if ($data[$index] != $column1) {
110            printf("[014] Row %d, expecting %s/%s got %s/%s\n",
111                $index + 1, gettype($data[$index]), $data[$index], gettype($column1), $column1);
112        }
113        $index++;
114    }
115    $stmt->close();
116
117    print "Using integer only...\n";
118    if (!($res = $link->query("SELECT 1 AS column1 UNION SELECT 303 UNION SELECT 2")))
119        printf("[015] [%d] %s\n", $link->errno, $link->error);
120
121    $data = array();
122    while ($row = $res->fetch_assoc()) {
123        $data[] = $row['column1'];
124    }
125    $res->free();
126
127    // Prepared Statements
128    if (!($stmt = $link->prepare("SELECT 1 AS column1 UNION SELECT 303 UNION SELECT 2")))
129        printf("[016] [%d] %s\n", $link->errno, $link->error);
130
131    $column1 = null;
132    if (!$stmt->execute() || !$stmt->bind_result($column1))
133        printf("[017] [%d] %s\n", $stmt->errno, $stmt->error);
134
135    $index = 0;
136    while ($stmt->fetch()) {
137        if ($data[$index] != $column1) {
138            printf("[018] Row %d, expecting %s/%s got %s/%s\n",
139                $index + 1, gettype($data[$index]), $data[$index], gettype($column1), $column1);
140        }
141        var_dump($column1);
142        $index++;
143    }
144    $stmt->close();
145
146    /* Advantage mysqlnd - see above */
147    if (!($stmt = $link->prepare("SELECT 1 AS column1 UNION SELECT 303 UNION SELECT 2")))
148        printf("[019] [%d] %s\n", $link->errno, $link->error);
149
150    $column1 = null;
151    if (!$stmt->bind_result($column1) || !$stmt->execute())
152        printf("[020] [%d] %s\n", $stmt->errno, $stmt->error);
153
154    $index = 0;
155    while ($stmt->fetch()) {
156        if ($data[$index] != $column1) {
157            printf("[021] Row %d, expecting %s/%s got %s/%s\n",
158                $index + 1, gettype($data[$index]), $data[$index], gettype($column1), $column1);
159        }
160        $index++;
161    }
162    $stmt->close();
163
164    print "Testing bind_param(), strings only...\n";
165    $two = 'two';
166    $three = 'three';
167    if (!($stmt = $link->prepare("SELECT 'one' AS column1 UNION SELECT ? UNION SELECT ?")))
168        printf("[022] [%d] %s\n", $stmt->errno, $stmt->error);
169
170    $column1 = null;
171    if (!$stmt->bind_param('ss', $three, $two) || !$stmt->execute() || !$stmt->bind_result($column1))
172        printf("[023] [%d] %s\n", $stmt->errno, $stmt->error);
173
174    $index = 0;
175    $data = array();
176    while ($stmt->fetch()) {
177        $data[$index++] = $column1;
178        var_dump($column1);
179    }
180    $stmt->close();
181
182    /* Advantage mysqlnd - see above */
183    $two = 'two';
184    $three = 'three';
185    if (!($stmt = $link->prepare("SELECT 'one' AS column1 UNION SELECT ? UNION SELECT ?")))
186        printf("[024] [%d] %s\n", $stmt->errno, $stmt->error);
187
188    $column1 = null;
189    if (!$stmt->bind_param('ss', $three, $two) || !$stmt->bind_result($column1) || !$stmt->execute())
190        printf("[025] [%d] %s\n", $stmt->errno, $stmt->error);
191
192    $index = 0;
193    while ($stmt->fetch()) {
194        if ($data[$index] != $column1) {
195            printf("[26] Row %d, expecting %s/%s, got %s/%s\n",
196                $index + 1, gettype($data[$index]), $data[$index], gettype($column1), $column1);
197        }
198        $index++;
199    }
200    $stmt->close();
201
202    print "Testing bind_param(), strings only, with CAST AS CHAR...\n";
203    $two = 'two';
204    $three = 'three beers are more than enough';
205    if (!($stmt = $link->prepare("SELECT CAST('one' AS CHAR) AS column1 UNION SELECT CAST(? AS CHAR) UNION SELECT CAST(? AS CHAR)")))
206        printf("[027] [%d] %s\n", $stmt->errno, $stmt->error);
207
208    $column1 = null;
209    if (!$stmt->bind_param('ss', $three, $two) || !$stmt->execute() || !$stmt->bind_result($column1))
210        printf("[028] [%d] %s\n", $stmt->errno, $stmt->error);
211
212    $index = 0;
213    $data = array();
214    while ($stmt->fetch()) {
215        $data[$index++] = $column1;
216        var_dump($column1);
217    }
218    $stmt->close();
219
220    /* Advantage mysqlnd - see above */
221    $two = 'two';
222    $three = 'three beers are more than enough';
223    if (!($stmt = $link->prepare("SELECT CAST('one' AS CHAR) AS column1 UNION SELECT CAST(? AS CHAR) UNION SELECT CAST(? AS CHAR)")))
224        printf("[029] [%d] %s\n", $stmt->errno, $stmt->error);
225
226    $column1 = null;
227    if (!$stmt->bind_param('ss', $three, $two) || !$stmt->bind_result($column1) || !$stmt->execute())
228        printf("[030] [%d] %s\n", $stmt->errno, $stmt->error);
229
230    $index = 0;
231    while ($stmt->fetch()) {
232        if ($data[$index] != $column1) {
233            printf("[31] Row %d, expecting %s/%s, got %s/%s\n",
234                $index + 1, gettype($data[$index]), $data[$index], gettype($column1), $column1);
235        }
236        $index++;
237    }
238    $stmt->close();
239
240    $link->close();
241
242    print "done!";
243?>
244--EXPECT--
245Using CAST('somestring' AS CHAR)...
246string(3) "one"
247string(5) "three"
248string(3) "two"
249Mixing CAST('somestring'AS CHAR), integer and CAST(integer AS CHAR)...
250string(1) "1"
251string(5) "three"
252string(1) "2"
253Using integer only...
254int(1)
255int(303)
256int(2)
257Testing bind_param(), strings only...
258string(3) "one"
259string(5) "three"
260string(3) "two"
261Testing bind_param(), strings only, with CAST AS CHAR...
262string(3) "one"
263string(32) "three beers are more than enough"
264string(3) "two"
265done!
266