Annotation of embedaddon/php/ext/mysqli/tests/mysqli_stmt_bind_result_format.phpt, revision 1.1
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!
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[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