pgSmqf6g

1Building and verifying a tag system
2$sql = $dbRead->quoteInto("SELECT * FROM item_tag WHERE tag IN (?)", $tag_where);
3
4$tag_result = $dbRead->fetchAll($sql);
5
6    $i = 0;
7    while ($i < count($tags)) {
8
9if (in_array($tags[$i], $tag_result)) {
10
11    $array_key = array_search($tags[$i], $tag_result);
12
13    }
14else {
15    $data = array ('tag' => $tags[$i]);
16$dbWrite->insert('item_tag', $data);
17$tag_ID = $dbWrite->lastInsertId();  }
18
19$data = array('itemID' => $item_ID,
20              'tagID'   => $tag_ID);
21$dbWrite->insert('item_tag_connection', $data);
22++$i;
23    }
24	
25if (in_array($tags[$i], $tag_result)) {
26
27    $array_key = array_search($tags[$i], $tag_result);
28
29    }
30	
31-- usage: call insert_question(<user_id>,<question>,<tags>,<separator>);
32
33call insert_question(1,'why are stored procs useful ?', 'database,mysql,stored-procedures,kiss,performance',',');
34
35$sql = sprintf("call insert_question(%d,'%s','%s','%s')", $userID,$ques,$tags,$separator);
36	
37mysql> select * from tags order by tag_id;
38+--------+-------------------+
39| tag_id | tag               |
40+--------+-------------------+
41|      1 | database          |
42|      2 | mysql             |
43|      3 | stored-procedures |
44+--------+-------------------+
453 rows in set (0.00 sec)
46
47mysql> select * from questions order by question_id;
48Empty set (0.00 sec)
49
50mysql> select * from question_tags order by tag_id, question_id;
51Empty set (0.00 sec)
52	
53call insert_question(1,'why are stored procs useful ?', 'database,mysql,stored-procedures,kiss,performance',',');
54
55mysql> select * from tags order by tag_id;
56+--------+-------------------+
57| tag_id | tag               |
58+--------+-------------------+
59|      1 | database          |
60|      2 | mysql             |
61|      3 | stored-procedures |
62|      4 | kiss              |
63|      5 | performance       |
64+--------+-------------------+
655 rows in set (0.00 sec)
66
67mysql> select * from questions order by question_id;
68+-------------+---------+-------------------------------+---------------------+
69| question_id | user_id | question                      | created_date        |
70+-------------+---------+-------------------------------+---------------------+
71|           1 |       1 | why are stored procs useful ? | 2012-02-02 00:54:26 |
72+-------------+---------+-------------------------------+---------------------+
731 row in set (0.00 sec)
74
75mysql> select * from question_tags order by tag_id, question_id;
76+--------+-------------+
77| tag_id | question_id |
78+--------+-------------+
79|      1 |           1 |
80|      2 |           1 |
81|      3 |           1 |
82|      4 |           1 |
83|      5 |           1 |
84+--------+-------------+
855 rows in set (0.00 sec)
86	
87-- TABLES
88
89drop table if exists users;
90create table users
91(
92user_id int unsigned not null auto_increment primary key,
93username varchar(32) unique not null
94)
95engine=innodb;
96
97drop table if exists tags;
98create table tags
99(
100tag_id smallint unsigned not null auto_increment primary key,
101tag varchar(255) unique not null
102)
103engine=innodb;
104
105drop table if exists questions;
106create table questions
107(
108question_id int unsigned not null auto_increment primary key,
109user_id int unsigned not null,
110question varchar(512) not null,
111created_date datetime not null
112)
113engine=innodb;
114
115drop table if exists question_tags;
116create table question_tags
117(
118tag_id smallint unsigned not null,
119question_id int unsigned not null,
120primary key (tag_id, question_id) -- clustered composite pk
121)
122engine=innodb;
123
124-- PROCS
125
126drop procedure if exists insert_question;
127
128delimiter #
129create procedure insert_question
130(
131in p_user_id int unsigned,
132in p_question varchar(512),
133in p_tags_csv mediumtext, -- comma separated plz
134in p_separator char(1)
135)
136/*
137usage:
138call insert_question(1,'why are stored procs useful ?', 'database,mysql,stored-procedures,kiss,performance',',');
139
140$sql = sprintf("call insert_question(%d,'%s','%s','%s)", $userID,$ques,$tags,$separator);
141
142*/
143
144proc_main:begin
145
146declare v_question_id int unsigned default 0;
147declare v_done tinyint unsigned default 0;
148declare v_idx int unsigned default 1;
149declare v_tag varchar(255) default null;
150
151-- validate input params
152
153if p_separator is null or length(p_separator) <= 0 then 
154  set p_separator = ',';
155end if;
156if p_question is null or length(p_question) <= 0 then 
157  leave proc_main;
158end if;
159if p_tags_csv is null or length(p_tags_csv) <= 0 then 
160  leave proc_main;
161end if;
162
163-- split the tags into a memory table (ugly bit as mysql doesnt support table types)
164
165drop temporary table if exists tmp_tags;
166
167create temporary table tmp_tags(
168 tag_id smallint unsigned null,
169 tag varchar(255)
170)engine = memory;   
171
172while not v_done do
173
174  set v_tag = trim(substring(p_tags_csv, v_idx, 
175    if(locate(p_separator, p_tags_csv, v_idx) > 0, 
176       locate(p_separator, p_tags_csv, v_idx) - v_idx, length(p_tags_csv))));
177
178  if length(v_tag) > 0 then
179    set v_idx = v_idx + length(v_tag) + 1;
180    insert into tmp_tags(tag) values(v_tag);
181  else
182    set v_done = 1;
183  end if;
184end while;
185
186-- which tags do we already have ?
187
188update tmp_tags tt
189inner join tags t on t.tag = tt.tag
190set tt.tag_id = t.tag_id;
191
192-- insert tags
193
194insert into tags (tag) select tag from tmp_tags where tag_id is null;
195
196update tmp_tags tt
197inner join tags t on t.tag = tt.tag
198set tt.tag_id = t.tag_id
199where
200 tt.tag_id is null;
201
202-- insert question and question_tags
203
204insert into questions (user_id, question, created_date) values (p_user_id, p_question, now());
205
206set v_question_id = last_insert_id();
207
208insert into question_tags 
209select distinct tag_id, v_question_id from tmp_tags;
210
211-- return output
212
213select 
214 p_question,
215 u.user_id,
216 u.username,
217 v_question_id, 
218 tt.* 
219from 
220 tmp_tags tt
221inner join users u on u.user_id = p_user_id
222order by
223 tt.tag_id;
224
225-- cleanup
226
227drop temporary table if exists tmp_tags;
228
229end proc_main #
230
231delimiter ;
232
233-- TEST DATA
234
235insert into users (username) values ('f00');
236insert into tags (tag) values ('database'),('mysql'),('stored-procedures');
237
238-- TESTING
239
240select * from users order by user_id;
241select * from tags order by tag_id;
242select * from questions order by question_id;
243select * from question_tags order by tag_id, question_id;
244
245call insert_question(1,'why are stored procs useful ?', 'database,mysql,stored-procedures,kiss,performance',',');
246
247select * from tags order by tag_id;
248select * from questions order by question_id;
249select * from question_tags order by tag_id, question_id;
250	
251$tag_result = [ {tagID: 1, tag: "php"} , {tagID: 2, tag: "mysql"}, {tagID: 3, tag: "tags"} ]
252	
253$sql = $dbRead->quoteInto("SELECT * FROM item_tag WHERE tag IN (?)", "'" . join("','", $tags) . "'" );