php - 构建和验证标签系统

Question

我正在创建一个类似于 one 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);

    }

我用一个数组查询了标签表，该数组填充了用户输入的标签。我将结果打包到一个包含标签和标签主键的数组中。然后我开始检查每个用户输入的标签并根据数组检查它。我遇到问题的部分是当我点击标签时获取主键。

我不打算以这种特殊方式进行。如果有另一种方法可以达到相同的结果，但不会那么复杂，我很乐意听到。

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;

希望这有帮助:)

编辑:

ofc，在任何其他 RDBMS(sql server、oracle)中，sproc 都是微不足道的:)