Annotation of embedaddon/php/ext/mysqli/tests/mysqli_last_insert_id.phpt, revision 1.1.1.1
1.1 misho 1: --TEST--
2: API vs. SQL LAST_INSERT_ID()
3: --SKIPIF--
4: <?php
5: require_once('skipif.inc');
6: require_once('skipifconnectfailure.inc');
7: ?>
8: --FILE--
9: <?php
10: /*
11: CAUTION: the insert_id() API call is not supposed to return
12: the same value as a call to the LAST_INSERT_ID() SQL function.
13: It is not necessarily a bug if API and SQL function return different
14: values. Check the MySQL C API reference manual for details.
15: */
16: require_once("connect.inc");
17:
18: function get_sql_id($link) {
19: if (!($res = $link->query("SELECT LAST_INSERT_ID() AS _id"))) {
20: printf("[003] [%d] %s\n", $link->errno, $link->error);
21: return NULL;
22: }
23: $row = $res->fetch_assoc();
24: $res->close();
25:
26: return $row['_id'];
27: }
28:
29: if (!$link = my_mysqli_connect($host, $user, $passwd, $db, $port, $socket))
30: printf("[001] Cannot connect to the server using host=%s, user=%s, passwd=***, dbname=%s, port=%s, socket=%s\n",
31: $host, $user, $db, $port, $socket);
32:
33: if (!$link->query("DROP TABLE IF EXISTS test") ||
34: !$link->query("CREATE TABLE test (id INT auto_increment, label varchar(10) not null, PRIMARY KEY (id)) ENGINE=MyISAM") ||
35: !$link->query("INSERT INTO test (id, label) VALUES (null, 'a')")) {
36: printf("[002] [%d] %s\n", $link->errno, $link->error);
37: }
38:
39: $api_id = $link->insert_id;
40: $sql_id = get_sql_id($link);
41: printf("API: %d, SQL: %d\n", $api_id, $sql_id);
42:
43: if ($api_id < 1)
44: printf("[004] Expecting id > 0 got %d, [%d] %s\n", $api_id, $link->errno, $link->error) ;
45: if ($api_id != $sql_id)
46: printf("[005] SQL id %d should be equal to API id %d\n", $sql_id, $api_id);
47:
48: // Not an INSERT, API value must become 0
49: if (!($res = $link->query("SELECT 1 FROM DUAL")))
50: printf("[006] [%d] %s\n", $link->errno, $link->error);
51: else
52: $res->close();
53:
54: $api_id = $link->insert_id;
55: $new_sql_id = get_sql_id($link);
56: if (0 !== $api_id) {
57: printf("[007] API id should have been reset to 0 because previous query was SELECT, got API %d, SQL %d\n",
58: $api_id, $new_sql_id);
59: }
60: if ($new_sql_id != $sql_id) {
61: printf("[008] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id);
62: }
63:
64: // Insert fails, LAST_INSERT_ID shall not change, API shall return 0
65: if ($link->query("INSERT INTO test (id, label) VALUES (null, null)")) {
66: printf("[009] The INSERT did not fail as planned, [%d] %s\n", $link->errno, $link->error);
67: }
68: $api_id = $link->insert_id;
69: $new_sql_id = get_sql_id($link);
70:
71: if (0 !== $api_id) {
72: printf("[010] API id should have been reset to 0 because previous query was SELECT, got API %d, SQL %d\n",
73: $api_id, $new_sql_id);
74: }
75: if ($new_sql_id != $sql_id) {
76: printf("[011] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id);
77: }
78:
79: // Sequence counter pattern...
80: if (!$link->query("UPDATE test SET id=LAST_INSERT_ID(id+1)"))
81: printf("[012] [%d] %s\n", $link->errno, $link->error);
82:
83: $api_id = $link->insert_id;
84: $new_sql_id = get_sql_id($link);
85: if ($api_id < 1)
86: printf("[013] Expecting id > 0 got %d, [%d] %s\n", $api_id, $link->errno, $link->error) ;
87: if ($api_id != $new_sql_id)
88: printf("[014] SQL id %d should be equal to API id %d\n", $new_sql_id, $api_id);
89: if ($sql_id == $new_sql_id)
90: printf("[015] SQL id %d should have had changed, got %d\n", $sql_id, $new_sql_id);
91:
92: $sql_id = $new_sql_id;
93:
94: // Not an INSERT (after UPDATE), API value must become 0
95: if (!$link->query("SET @myvar=1"))
96: printf("[016] [%d] %s\n", $link->errno, $link->error);
97:
98: $api_id = $link->insert_id;
99: $new_sql_id = get_sql_id($link);
100: if (0 !== $api_id) {
101: printf("[017] API id should have been reset to 0 because previous query was SET, got API %d, SQL %d\n",
102: $api_id, $new_sql_id);
103: }
104: if ($new_sql_id != $sql_id) {
105: printf("[018] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id);
106: }
107:
108: if (!$link->query("INSERT INTO test(id, label) VALUES (LAST_INSERT_ID(id + 1), 'b')"))
109: printf("[019] [%d] %s\n", $link->errno, $link->error);
110:
111: $api_id = $link->insert_id;
112: $sql_id = get_sql_id($link);
113: if ($api_id != $sql_id)
114: printf("[020] SQL id %d should be equal to API id %d\n", $sql_id, $api_id);
115:
116: if (!$link->query("INSERT INTO test(label) VALUES ('c')"))
117: printf("[021] [%d] %s\n", $link->errno, $link->error);
118:
119: $api_id = $link->insert_id;
120: $sql_id = get_sql_id($link);
121: if ($api_id != $sql_id)
122: printf("[022] SQL id %d should be equal to API id %d\n", $sql_id, $api_id);
123:
124: if (!($res = $link->query("SELECT id, label FROM test ORDER BY id ASC")))
125: printf("[023] [%d] %s\n", $link->errno, $link->error);
126:
127: printf("Dumping table contents before INSERT...SELECT experiments...\n");
128: while ($row = $res->fetch_assoc()) {
129: printf("id = %d, label = '%s'\n", $row['id'], $row['label']);
130: }
131: $res->close();
132:
133: if (!$link->query("INSERT INTO test(label) SELECT CONCAT(label, id) FROM test ORDER BY id ASC"))
134: printf("[024] [%d] %s\n", $link->errno, $link->error);
135:
136: $api_id = $link->insert_id;
137: $sql_id = get_sql_id($link);
138: if ($api_id != $sql_id)
139: printf("[025] SQL id %d should be equal to API id %d\n", $sql_id, $api_id);
140:
141: if ($link->query("INSERT INTO test(id, label) SELECT id, CONCAT(label, id) FROM test ORDER BY id ASC"))
142: printf("[026] INSERT should have failed because of duplicate PK value, [%d] %s\n", $link->errno, $link->error);
143:
144: $api_id = $link->insert_id;
145: $new_sql_id = get_sql_id($link);
146: if (0 !== $api_id) {
147: printf("[027] API id should have been reset to 0 because previous query failed, got API %d, SQL %d\n",
148: $api_id, $new_sql_id);
149: }
150: if ($new_sql_id != $sql_id) {
151: printf("[028] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id);
152: }
153:
154: /* API insert id will be 101 because of UPDATE, SQL unchanged */
155: if (!$link->query(sprintf("INSERT INTO test(id, label) VALUES (%d, 'z') ON DUPLICATE KEY UPDATE id = 101", $sql_id) ))
156: printf("[029] [%d] %s\n", $link->errno, $link->error);
157:
158: $api_id = $link->insert_id;
159: $new_sql_id = get_sql_id($link);
160: if ($api_id != 101)
161: printf("[030] API id should be %d got %d\n", $sql_id, $api_id);
162: if ($new_sql_id != $sql_id) {
163: printf("[031] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id);
164: }
165:
166: if (!($res = $link->query("SELECT id, label FROM test ORDER BY id ASC")))
167: printf("[032] [%d] %s\n", $link->errno, $link->error);
168:
169: printf("Dumping table contents after INSERT...SELECT...\n");
170: while ($row = $res->fetch_assoc()) {
171: printf("id = %d, label = '%s'\n", $row['id'], $row['label']);
172: }
173: $res->close();
174:
175: print "done!";
176: ?>
177: --CLEAN--
178: <?php
179: require_once("clean_table.inc");
180: ?>
181: --EXPECTF--
182: API: %d, SQL: %d
183: Dumping table contents before INSERT...SELECT experiments...
184: id = %d, label = 'b'
185: id = %d, label = 'a'
186: id = %d, label = 'c'
187: Dumping table contents after INSERT...SELECT...
188: id = %d, label = 'b'
189: id = %d, label = 'a'
190: id = %d, label = 'c'
191: id = %d, label = 'a%d'
192: id = %d, label = 'c%d'
193: id = 101, label = 'b%d'
194: done!
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to mysqli_last_insert_id.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