构建和验证标签系统

Question

我正在创建一个与stack overflow使用的标签系统类似的标签系统，我需要根据标签数据库检查新输入的标签，如果找到匹配，则使用现有标签的键连接到项目。我在这个验证过程中遇到了一些问题。我不知道如何着手做这件事。这就是我到目前为止所拥有的。

$sql = $dbRead->quoteInto("SELECT * FROM item_tag WHERE tag IN (?)", $tag_where);

$tag_result = $dbRead->fetchAll($sql);

    $i = 0;
    while ($i < count($tags)) {

if (in_array($tags[$i], $tag_result)) {

    $array_key = array_search($tags[$i], $tag_result);

    }
else {
    $data = array ('tag' => $tags[$i]);
$dbWrite->insert('item_tag', $data);
$tag_ID = $dbWrite->lastInsertId();  }

$data = array('itemID' => $item_ID,
              'tagID'   => $tag_ID);
$dbWrite->insert('item_tag_connection', $data);
++$i;
    }

问题是if else的这一部分。

if (in_array($tags[$i], $tag_result)) {

    $array_key = array_search($tags[$i], $tag_result);

    }

我用一个数组来查询标记表，数组中填充了用户输入的标记。我将结果打包到一个包含标记和标记主键的数组中。然后，我仔细检查了每个用户的entered tag，并对照数组进行了检查。我遇到问题的部分是，当我在标签上得到一个匹配时，获取主键。

我并不打算用这种特殊的方式来做这件事。如果有另一种方法来实现同样的结果，那就不那么复杂了，我很想听听。

Answer 1

我会使用一个存储过程，并从我的PHP代码向MySQL发出一次调用。不幸的是，MySQL并不擅长处理数组类型参数，也不支持表返回类型，所以这个存储过程可能看起来有点丑陋:(

示例调用：

-- usage: call insert_question(<user_id>,<question>,<tags>,<separator>);

call insert_question(1,'why are stored procs useful ?', 'database,mysql,stored-procedures,kiss,performance',',');

$sql = sprintf("call insert_question(%d,'%s','%s','%s')", $userID,$ques,$tags,$separator);

输出：

mysql> select * from tags order by tag_id;
+--------+-------------------+
| tag_id | tag               |
+--------+-------------------+
|      1 | database          |
|      2 | mysql             |
|      3 | stored-procedures |
+--------+-------------------+
3 rows in set (0.00 sec)

mysql> select * from questions order by question_id;
Empty set (0.00 sec)

mysql> select * from question_tags order by tag_id, question_id;
Empty set (0.00 sec)

现在我们将调用存储过程：

call insert_question(1,'why are stored procs useful ?', 'database,mysql,stored-procedures,kiss,performance',',');

mysql> select * from tags order by tag_id;
+--------+-------------------+
| tag_id | tag               |
+--------+-------------------+
|      1 | database          |
|      2 | mysql             |
|      3 | stored-procedures |
|      4 | kiss              |
|      5 | performance       |
+--------+-------------------+
5 rows in set (0.00 sec)

mysql> select * from questions order by question_id;
+-------------+---------+-------------------------------+---------------------+
| question_id | user_id | question                      | created_date        |
+-------------+---------+-------------------------------+---------------------+
|           1 |       1 | why are stored procs useful ? | 2012-02-02 00:54:26 |
+-------------+---------+-------------------------------+---------------------+
1 row in set (0.00 sec)

mysql> select * from question_tags order by tag_id, question_id;
+--------+-------------+
| tag_id | question_id |
+--------+-------------+
|      1 |           1 |
|      2 |           1 |
|      3 |           1 |
|      4 |           1 |
|      5 |           1 |
+--------+-------------+
5 rows in set (0.00 sec)

脚本：

完整脚本在这里：http://pastie.org/3299425

-- TABLES

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varchar(32) unique not null
)
engine=innodb;

drop table if exists tags;
create table tags
(
tag_id smallint unsigned not null auto_increment primary key,
tag varchar(255) unique not null
)
engine=innodb;

drop table if exists questions;
create table questions
(
question_id int unsigned not null auto_increment primary key,
user_id int unsigned not null,
question varchar(512) not null,
created_date datetime not null
)
engine=innodb;

drop table if exists question_tags;
create table question_tags
(
tag_id smallint unsigned not null,
question_id int unsigned not null,
primary key (tag_id, question_id) -- clustered composite pk
)
engine=innodb;

-- PROCS

drop procedure if exists insert_question;

delimiter #
create procedure insert_question
(
in p_user_id int unsigned,
in p_question varchar(512),
in p_tags_csv mediumtext, -- comma separated plz
in p_separator char(1)
)
/*
usage:
call insert_question(1,'why are stored procs useful ?', 'database,mysql,stored-procedures,kiss,performance',',');

$sql = sprintf("call insert_question(%d,'%s','%s','%s)", $userID,$ques,$tags,$separator);

*/

proc_main:begin

declare v_question_id int unsigned default 0;
declare v_done tinyint unsigned default 0;
declare v_idx int unsigned default 1;
declare v_tag varchar(255) default null;

-- validate input params

if p_separator is null or length(p_separator) <= 0 then 
  set p_separator = ',';
end if;
if p_question is null or length(p_question) <= 0 then 
  leave proc_main;
end if;
if p_tags_csv is null or length(p_tags_csv) <= 0 then 
  leave proc_main;
end if;

-- split the tags into a memory table (ugly bit as mysql doesnt support table types)

drop temporary table if exists tmp_tags;

create temporary table tmp_tags(
 tag_id smallint unsigned null,
 tag varchar(255)
)engine = memory;   

while not v_done do

  set v_tag = trim(substring(p_tags_csv, v_idx, 
    if(locate(p_separator, p_tags_csv, v_idx) > 0, 
       locate(p_separator, p_tags_csv, v_idx) - v_idx, length(p_tags_csv))));

  if length(v_tag) > 0 then
    set v_idx = v_idx + length(v_tag) + 1;
    insert into tmp_tags(tag) values(v_tag);
  else
    set v_done = 1;
  end if;
end while;

-- which tags do we already have ?

update tmp_tags tt
inner join tags t on t.tag = tt.tag
set tt.tag_id = t.tag_id;

-- insert tags

insert into tags (tag) select tag from tmp_tags where tag_id is null;

update tmp_tags tt
inner join tags t on t.tag = tt.tag
set tt.tag_id = t.tag_id
where
 tt.tag_id is null;

-- insert question and question_tags

insert into questions (user_id, question, created_date) values (p_user_id, p_question, now());

set v_question_id = last_insert_id();

insert into question_tags 
select distinct tag_id, v_question_id from tmp_tags;

-- return output

select 
 p_question,
 u.user_id,
 u.username,
 v_question_id, 
 tt.* 
from 
 tmp_tags tt
inner join users u on u.user_id = p_user_id
order by
 tt.tag_id;

-- cleanup

drop temporary table if exists tmp_tags;

end proc_main #

delimiter ;

-- TEST DATA

insert into users (username) values ('f00');
insert into tags (tag) values ('database'),('mysql'),('stored-procedures');

-- TESTING

select * from users order by user_id;
select * from tags order by tag_id;
select * from questions order by question_id;
select * from question_tags order by tag_id, question_id;

call insert_question(1,'why are stored procs useful ?', 'database,mysql,stored-procedures,kiss,performance',',');

select * from tags order by tag_id;
select * from questions order by question_id;
select * from question_tags order by tag_id, question_id;

希望这能有所帮助:)

编辑：

在任何其他RDBMS (sql服务器、oracle)中，存储过程都是微不足道的：)

Answer 2

这是您使用数组$tag_result的方式。请注意，$tag_result不是一个标记数组，而是一个行数组。据我从您的代码可以看出，每一行都包含一个tagID和一个tag。因此，您的$tag_result数组将如下所示：

$tag_result = [ {tagID: 1, tag: "php"} , {tagID: 2, tag: "mysql"}, {tagID: 3, tag: "tags"} ]

要做到这一点，最好的方法是，不是从数据库中查询所有标签(这非常非常低效)，而是像这样查询：

$sql = $dbRead->quoteInto("SELECT * FROM item_tag WHERE tag IN (?)", "'" . join("','", $tags) . "'" );