z9tQxcdM

1drop table if exists articles;
2create table articles
3(
4article_id int unsigned not null auto_increment primary key,
5name varchar(255) not null
6)
7engine=innodb;
8
9drop procedure if exists insert_article;
10drop procedure if exists list_latest_articles;
11
12delimiter #
13
14create procedure insert_article
15(
16in p_name varchar(255)
17)
18begin
19
20declare v_article_id int unsigned default 0;
21
22 insert into articles (name) values (p_name);
23 
24 set v_article_id = last_insert_id();
25 
26 -- use v_article_id else where...
27 
28 select v_article_id as article_id;
29
30end#
31
32create procedure list_latest_articles()
33begin
34  select * from articles order by article_id desc limit 5;
35end#
36
37delimiter ;
38
39call insert_article('foo');
40call insert_article('bar');
41call list_latest_articles();
42
43<?php
44
45ob_start(); 
46
47try
48{
49	$db = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);
50
51	if ($db->connect_errno) 
52		throw new exception(sprintf("Could not connect: %s", $db->connect_error));
53
54	$inTrans = false;	
55	//$db->autocommit(FALSE); // does not update global status so use start trans instead !
56	
57	$sqlCmd = "start transaction";
58	$result = $db->query($sqlCmd);
59	
60	if(!$result) throw new exception(sprintf("Invalid query : %s %s", $sqlCmd, $db->error));
61	
62	$inTrans = true;	
63	$name = $db->real_escape_string(uniqid()); // some random name
64	
65	$sqlCmd = sprintf("call insert_article('%s')", $name);	
66	$result = $db->query($sqlCmd);
67
68	if(!$result) throw new exception(sprintf("Invalid query : %s %s", $sqlCmd, $db->error));
69		
70	$row = $result->fetch_assoc(); 
71	$articleID = $row["article_id"]; 
72	
73	$result->free(); 
74	$db->next_result();
75		
76	$result = $db->commit();
77	if(!$result) throw new exception(sprintf("Invalid query : %s %s", $sqlCmd, $db->error));
78	
79	$inTrans = false;
80	
81	echo sprintf("new article id = %d<br/>", $articleID);
82	
83	$sqlCmd = "call list_latest_articles()";
84	$result = $db->query($sqlCmd);
85	
86	if(!$result) throw new exception(sprintf("Invalid query : %s %s", $sqlCmd, $db->error));
87		
88	while ($row = $result->fetch_assoc()){
89		echo sprintf("article id = %d name = %s<br/>", $row["article_id"], $row["name"]);
90	}
91	
92	$result->free();
93	$db->next_result();
94	
95	$status = array("Com_begin","com_commit","com_rollback","Com_call_procedure","Com_insert","Com_update","Com_select");
96	$vars = array();
97
98	foreach($status as $s){
99		
100		$sqlCmd = sprintf("show global status like '%s'", $s);
101		$result = $db->query($sqlCmd);
102		
103		if(!$result) throw new exception(sprintf("Invalid query : %s %s", $sqlCmd, $db->error));
104		
105		while($row = $result->fetch_assoc()) $vars[] = array($row["Variable_name"], $row["Value"]);
106	}
107	
108	$result->free();
109	$db->next_result();
110	echo "<pre>", print_r($vars), "</pre>";
111
112}
113catch(exception $ex)
114{
115	ob_clean(); 
116	if(!$db->connect_errno && $inTrans) $db->rollback();
117	echo sprintf("zomg borked - %s", $ex->getMessage());
118}
119
120
121//finally
122if(!$db->connect_errno) $db->close();
123ob_end_flush();
124
125?>