![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to mysqli_stmt_bind_result_format.phpt CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / php / ext / mysqli / tests|
Return to mysqli_stmt_bind_result_format.phpt CVS log | Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / php / ext / mysqli / tests |
1.1 misho 1: --TEST--
2: Playing with SELECT FORMAT(...) AS _format - see also bugs.php.net/42378
3: --SKIPIF--
4: <?php
5: require_once('skipif.inc');
6: require_once('skipifemb.inc');
7: require_once('skipifconnectfailure.inc');
8: ?>
9: --INI--
10: memory_limit=83886080
11: --FILE--
12: <?php
13: require_once("connect.inc");
14:
15: function create_table($link, $column, $min, $max, $engine, $offset) {
16:
17: if (!mysqli_query($link, 'DROP TABLE IF EXISTS test')) {
18: printf("[%03d] Cannot drop table test, [%d] %s\n",
19: $offset,
20: mysqli_errno($link), mysqli_error($link));
21: return array();
22: }
23:
24: $sql = sprintf("CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, col1 %s) ENGINE=%s",
25: $column, $engine);
26: if (!mysqli_query($link, $sql)) {
27: printf("[%03d] Cannot create table test, [%d] %s\n",
28: $offset + 1,
29: mysqli_errno($link), mysqli_error($link));
30: return array();
31: }
32:
33: $values = array();
34: for ($i = 1; $i <= 100; $i++) {
35: $col1 = mt_rand($min, $max);
36: $values[$i] = $col1;
37: $sql = sprintf("INSERT INTO test(id, col1) VALUES (%d, %f)",
38: $i, $col1);
39: if (!mysqli_query($link, $sql)) {
40: printf("[%03d] Cannot insert data, [%d] %s\n",
41: $offset + 2,
42: mysqli_errno($link), mysqli_error($link));
43: return array();
44: }
45: }
46:
47: return $values;
48: }
49:
50: function test_format($link, $format, $from, $order_by, $expected, $offset) {
51:
52: if (!$stmt = mysqli_stmt_init($link)) {
53: printf("[%03d] Cannot create PS, [%d] %s\n",
54: $offset,
55: mysqli_errno($link), mysqli_error($link));
56: return false;
57: }
58:
59: if ($order_by)
60: $sql = sprintf('SELECT %s AS _format FROM %s ORDER BY %s', $format, $from, $order_by);
61: else
62: $sql = sprintf('SELECT %s AS _format FROM %s', $format, $from);
63:
64: if (!mysqli_stmt_prepare($stmt, $sql)) {
65: printf("[%03d] Cannot prepare PS, [%d] %s\n",
66: $offset + 1,
67: mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
68: return false;
69: }
70:
71: if (!mysqli_stmt_execute($stmt)) {
72: printf("[%03d] Cannot execute PS, [%d] %s\n",
73: $offset + 2,
74: mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
75: return false;
76: }
77:
78: if (!mysqli_stmt_store_result($stmt)) {
79: printf("[%03d] Cannot store result set, [%d] %s\n",
80: $offset + 3,
81: mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
82: return false;
83: }
84:
85: if (!is_array($expected)) {
86:
87: $result = null;
88: if (!mysqli_stmt_bind_result($stmt, $result)) {
89: printf("[%03d] Cannot bind result, [%d] %s\n",
90: $offset + 4,
91: mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
92: return false;
93: }
94:
95: if (!mysqli_stmt_fetch($stmt)) {
96: printf("[%03d] Cannot fetch result,, [%d] %s\n",
97: $offset + 5,
98: mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
99: return false;
100: }
101:
102: if ($result !== $expected) {
103: printf("[%03d] Expecting %s/%s got %s/%s with %s - %s.\n",
104: $offset + 6,
105: gettype($expected), $expected,
106: gettype($result), $result,
107: $format, $sql);
108: }
109:
110: } else {
111:
112: $order_by_col = $result = null;
113: if (!mysqli_stmt_bind_result($stmt, $order_by_col, $result)) {
114: printf("[%03d] Cannot bind result, [%d] %s\n",
115: $offset + 7,
116: mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
117: return false;
118: }
119:
120: reset($expected);
121: while ((list($k, $v) = each($expected)) && mysqli_stmt_fetch($stmt)) {
122: if ($result !== $v) {
123: printf("[%03d] Row %d - expecting %s/%s got %s/%s [%s] with %s - %s.\n",
124: $offset + 8,
125: $k,
126: gettype($v), $v,
127: gettype($result), $result,
128: $order_by_col,
129: $format, $sql);
130: }
131: }
132:
133: }
134:
135: mysqli_stmt_free_result($stmt);
136: mysqli_stmt_close($stmt);
137:
138: return true;
139: }
140:
141: if (!$link = my_mysqli_connect($host, $user, $passwd, $db, $port, $socket))
142: printf("[001] Cannot connect - [%d] %s\n",
143: mysqli_connect_errno(),
144: mysqli_connect_error());
145:
146: /* select from dual - pseudo table */
147: test_format($link, 'FORMAT(1.01, 0)', 'DUAL', null, '1', 10);
148: test_format($link, 'FORMAT(1.23, 1)', 'DUAL', null, '1.2', 20);
149: test_format($link, 'FORMAT(1.23, 2)', 'DUAL', null, '1.23', 30);
150: test_format($link, 'FORMAT(1234.567, 3)', 'DUAL', null, '1,234.567', 40);
151: /* no typo! */
152: test_format($link, 'FORMAT(1234.567, 4)', 'DUAL', null, '1,234.5670', 50);
153:
154: mysqli_close($link);
155: require_once('table.inc');
156:
157: /* select from existing table */
158: test_format($link, 'FORMAT(id, 0)', 'test', null, '1', 60);
159: test_format($link, 'FORMAT(id + 0.1, 1)', 'test', null, '1.1', 70);
160: test_format($link, 'FORMAT(id + 0.01, 2)', 'test', null, '1.01', 80);
161:
162: /* create new table and select from it */
163: $expected = create_table($link, 'FLOAT', -10000, 10000, $engine, 90);
164: foreach ($expected as $k => $v)
165: $expected[$k] = number_format(round($v), 0, '.', ',');
166: test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 100);
167:
168: $expected = create_table($link, 'FLOAT UNSIGNED', 0, 10000, $engine, 110);
169: foreach ($expected as $k => $v)
170: $expected[$k] = number_format(round($v), 0, '.', ',');
171: test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 120);
172:
173: $expected = create_table($link, 'TINYINT', -128, 127, $engine, 130);
174: foreach ($expected as $k => $v)
175: $expected[$k] = number_format(round($v), 0, '.', ',');
176: test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 140);
177:
178: $expected = create_table($link, 'SMALLINT UNSIGNED', 0, 65535, $engine, 150);
179: foreach ($expected as $k => $v)
180: $expected[$k] = number_format(round($v), 0, '.', ',');
181: test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 160);
182:
183: $expected = create_table($link, 'MEDIUMINT', 0, 8388607, $engine, 170);
184: foreach ($expected as $k => $v)
185: $expected[$k] = number_format(round($v), 0, '.', ',');
186: test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 180);
187:
188: $expected = create_table($link, 'INT UNSIGNED', 0, 1000, $engine, 190);
189: foreach ($expected as $k => $v)
190: $expected[$k] = number_format(round($v), 0, '.', ',');
191: test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 200);
192:
193: $expected = create_table($link, 'BIGINT', -1000, 1000, $engine, 210);
194: foreach ($expected as $k => $v)
195: $expected[$k] = number_format(round($v), 0, '.', ',');
196: test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 220);
197:
198: $expected = create_table($link, 'DECIMAL(5,0)', -1000, 1000, $engine, 230);
199: foreach ($expected as $k => $v)
200: $expected[$k] = number_format(round($v), 0, '.', ',');
201: test_format($link, 'id AS order_by_col, FORMAT(col1, 0)', 'test', 'id', $expected, 240);
202:
203: // http://bugs.php.net/bug.php?id=42378
204: if (!mysqli_query($link, "DROP TABLE IF EXISTS test")) {
205: printf("[300] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
206: }
207:
208: if (mysqli_query($link, "CREATE TABLE `test` (
209: `targetport` int(11) NOT NULL default '0',
210: `sources` double(17,4) default NULL,
211: `current_sources` double(17,0) default NULL,
212: `reports` double(17,4) default NULL,
213: `current_reports` double(17,0) default NULL,
214: `targets` double(17,4) default NULL,
215: `current_targets` double(17,0) default NULL,
216: `maxsources` int(11) default NULL,
217: `maxtargets` int(11) default NULL,
218: `maxreports` int(11) default NULL,
219: `trend` float default NULL,
220: PRIMARY KEY (`targetport`)
221: ) ENGINE=InnoDB DEFAULT CHARSET=latin1")) {
222:
223: do {
224: $values = array();
225: for ($i = 0; $i < 200; $i++) {
226: $current_targets = mt_rand(-100000, 100000) / 10;
227: do {
228: $trend = (mt_rand(0, 3) > 1) ? (mt_rand(-10000, 10000) / 100) : 'NULL';
229: } while (isset($values[$trend]));
230:
231: $sql = sprintf('INSERT INTO test(targetport, current_targets, maxreports, trend) VALUES (%d, %f, %s, %s)',
232: $i,
233: $current_targets,
234: (mt_rand(0, 1) > 0) ? mt_rand(0, 1000) : 'NULL',
235: $trend);
236: if (!mysqli_query($link, $sql)) {
237: printf("[301] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
238: break 2;
239: }
240: if ($current_targets > 0 && $trend != 'NULL')
241: $values[$trend] = $i;
242: }
243: krsort($values);
244:
245: if (!$stmt = mysqli_stmt_init($link)) {
246: printf("[302] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
247: break;
248: }
249:
250: if (!mysqli_stmt_prepare($stmt, 'SELECT trend, targetport, FORMAT(trend, 2) FROM test WHERE current_targets > 0 AND trend IS NOT NULL ORDER BY trend DESC LIMIT 100')) {
251: printf("[303] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
252: break;
253: }
254:
255: if (!mysqli_stmt_execute($stmt)) {
256: printf("[304] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
257: break;
258: }
259:
260: if (!mysqli_stmt_store_result($stmt)) {
261: printf("[305] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
262: break;
263: }
264:
265: $trend = $targetport = $format = null;
266: if (!mysqli_stmt_bind_result($stmt, $trend, $targetport, $format)) {
267:
268: printf("[305] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
269: break;
270: }
271:
272: reset($values);
273: while (mysqli_stmt_fetch($stmt)) {
274: list($exp_trend, $exp_targetport) = each($values);
275: if ($targetport != $exp_targetport) {
276: printf("[306] Values fetched from MySQL seem to be wrong, check manually\n");
277: printf("%s/%s - %s/%s - '%s'\n", $trend, $exp_trend, $targetport, $exp_targetport, $format);
278: }
279: }
280: mysqli_stmt_free_result($stmt);
281: mysqli_stmt_close($stmt);
282:
283: // same but OO interface
284: if (!$stmt = mysqli_stmt_init($link)) {
285: printf("[307] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
286: break;
287: }
288:
289: if (!$stmt->prepare('SELECT trend, targetport, FORMAT(trend, 2) FROM test WHERE current_targets > 0 AND trend IS NOT NULL ORDER BY trend DESC LIMIT 100')) {
290: printf("[308] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
291: break;
292: }
293:
294: if (!$stmt->execute()) {
295: printf("[309] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
296: break;
297: }
298:
299: if (!$stmt->store_result()) {
300: printf("[310] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
301: break;
302: }
303:
304: $trend = $targetport = $format = null;
305: if (!$stmt->bind_result($trend, $targetport, $format)) {
306:
307: printf("[311] [%d] %s\n", mysqli_stmt_errno($link), mysqli_stmt_error($link));
308: break;
309: }
310:
311: reset($values);
312: while ($stmt->fetch()) {
313: list($exp_trend, $exp_targetport) = each($values);
314: if ($targetport != $exp_targetport) {
315: printf("[312] Values fetched from MySQL seem to be wrong, check manually\n");
316: printf("%s/%s - %s/%s - '%s'\n", $trend, $exp_trend, $targetport, $exp_targetport, $format);
317: }
318: }
319: $stmt->free_result();
320: $stmt->close();
321:
322: } while (false);
323:
324: } else {
325: var_dump(mysqli_error($link));
326: }
327:
328:
329: mysqli_close($link);
330: print "done!";
331: ?>
332: --CLEAN--
333: <?php
334: require_once("clean_table.inc");
335: ?>
336: --EXPECTF--
337: done!