设置“标签"MySQL 数据库中的条目系统 - IT屋-程序员软件开发技术分享社区
首页 PHP 设置“标签"MySQL 数据库中的条目系统
设置“标签"MySQL 数据库中的条目系统
[英] Setting up a "tag" system for entries in a MySQL database
本文介绍了设置“标签"MySQL 数据库中的条目系统的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下 PHP 表单用于在 MySQL 数据库中创建条目:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><头>...<身体><div class="容器"><div class="header">... <div class="sidebar1">...
<div class="内容"><div id="风格化" class="myform"><form id="form" name="form" action="" method="post"><h1>在数据库中创建一个新条目</h1><table width="100%" border="0" cellpadding="6"><tr><td colspan="2"><legend>文章详情</legend></td></tr><tr><td width="20%" align="right"><span class="field">文章标题:</span></td><td width="80%" align="left"><span class="field"><input name="articletitle" type="text" value="<?php echo $articletitle; ?>"大小=50"/></span></td></tr><tr><td align="right"><span class="field">文章作者:</span></td><td align="left"><span class="field"><input name="articleorganization" type="text" value="<?php echo $articleorganization; ?>"大小=50"/></span></td></tr><tr><td align="right"><span class="field">访问日期:</span></td><td align="left"><span class="field"><input name="articledate" type="text" value="MM/DD/YYYY" size="50"/></span></td></tr><tr><td align="right"><span class="field">文章网址:</span></td><td align="left"><span class="field"><input name="articleurl" type="text" value="<?php echo $articleurl; ?>"大小=50"/></span></td></tr><tr><td align="right"><span class="field">文章标签:</span></td><td align="left"><span class="field"><input type="checkbox" name="articletags[]" value="geology" id="articletags_0"/><input type="checkbox" name="articletags[]" value="astronomy" id="articletags_1"/></span></td></tr><footer><input type="submit" name="submit" value="添加这篇文章"></footer></表单>
<div class="footer">...
</html><?php}包括('settings.php');如果(计数($articletags)> 0){$articletags_string = implode(",", $articletags);}if ($_SERVER['REQUEST_METHOD'] == 'POST'){$articletitle = mysql_real_escape_string(htmlspecialchars($_POST['articletitle']));$articleorganization = mysql_real_escape_string(htmlspecialchars($_POST['articleorganization']));$articledate = mysql_real_escape_string(htmlspecialchars($_POST['articledate']));$articleurl = mysql_real_escape_string(htmlspecialchars($_POST['articleurl']));$articletags = implode(',', $_POST['articletags']);if ($articletitle == '' || $articleorganization == ''){$error = '错误:请填写所有必填字段！';renderForm($articletitle, $articleorganization);}别的{mysql_query("插入文章SET articletitle='$articletitle', articleorganization='$articleorganization', articledate='$articledate', articleurl='$articleurl'");mysql_query("插入文章标签设置文章标签='$文章标签'")或死(mysql_error())；header("位置:addsuccess.php");}}别的{renderForm('','','','','');}?>目前表单会将正确的信息提交给数据库中的两个表，articles 和 articles_tags，但我不知道如何建立关系.
最终的结果是，单独表单上的复选框将是可编辑的(即，如果我在入口页面上选中一个框，然后转到编辑页面，该框已经被选中，然后可以未选中，该条目可以更新).
不过，现在我正在尝试使该关系出现在第二个表中.表格的布局是:
文章:
id |文章标题 |文章组织|文章日期 |文章网址
articles_tags:
id |article_id |tag_id |文章标签
我认为 我需要以某种方式获取文章 ID 和标签 ID 并将它们插入到第二个表中(?).
这需要第三张桌子吗?
我是否在正确的轨道上?
解决方案
内联标签存储 根据标签的大小，您可以将它们存储在 article_tags 表中:
article_id |标签内容添加UNIQUE(article_id, tag_contents)以避免每篇文章存储重复标签
重要
这种方法有一个缺点；标签不相互连接，因此如果对标签进行更改，您必须更新整个 article_tags 表.此外，如果标签明显长于 4 个字节，您最好使用下一个解决方案.
外部标签存储 创建另一个表tags:
id |标签内容添加UNIQUE(tag_contents)以避免存储重复标签
修改`article_tags:
article_id |tag_id添加UNIQUE(article_id, tag_id)以避免每篇文章存储重复标签
I have the following PHP form for creating entries in a MySQL database:
<?php
function renderForm($articletitle, $articleorganization, $articledate, $articleurl, $articletags )
{
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
. . .
</head>
<body>
<div class="container">
<div class="header">
. . .
</div>
<div class="sidebar1">
. . .
</div>
<div class="content">
<div id="stylized" class="myform">
<form id="form" name="form" action="" method="post">
<h1>Create a new entry in the database</h1>
<table width="100%" border="0" cellpadding="6">
<tr>
<td colspan="2"><legend>Article details</legend></td>
</tr>
<tr>
<td width="20%" align="right"><span class="field">Article Title:</span></td>
<td width="80%" align="left"><span class="field">
<input name="articletitle" type="text" value="<?php echo $articletitle; ?>" size="50"/>
</span></td>
</tr>
<tr>
<td align="right"><span class="field">Article Author:</span></td>
<td align="left"><span class="field">
<input name="articleorganization" type="text" value="<?php echo $articleorganization; ?>" size="50"/>
</span></td>
</tr>
<tr>
<td align="right"><span class="field">Access Date:</span></td>
<td align="left"><span class="field">
<input name="articledate" type="text" value="MM/DD/YYYY" size="50"/>
</span></td>
</tr>
<tr>
<td align="right"><span class="field">Article URL:</span></td>
<td align="left"><span class="field">
<input name="articleurl" type="text" value="<?php echo $articleurl; ?>" size="50"/>
</span></td>
</tr>
<tr>
<td align="right"><span class="field">Article Tags:</span></td>
<td align="left"><span class="field">
<input type="checkbox" name="articletags[]" value="geology" id="articletags_0" />
<input type="checkbox" name="articletags[]" value="astronomy" id="articletags_1" />
</span></td>
</tr>
</table>
<footer><input type="submit" name="submit" value="Add this Article"></footer>
</form>
</div>
<div class="footer">
. . .
</div>
</body>
</html>
<?php
}
include('settings.php');
if (count($articletags) > 0)
{
$articletags_string = implode(",", $articletags);
}
if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
$articletitle = mysql_real_escape_string(htmlspecialchars($_POST['articletitle']));
$articleorganization = mysql_real_escape_string(htmlspecialchars($_POST['articleorganization']));
$articledate = mysql_real_escape_string(htmlspecialchars($_POST['articledate']));
$articleurl = mysql_real_escape_string(htmlspecialchars($_POST['articleurl']));
$articletags = implode(',', $_POST['articletags']);
if ($articletitle == '' || $articleorganization == '')
{
$error = 'ERROR: Please fill in all required fields!';
renderForm($articletitle, $articleorganization);
}
else
{
mysql_query("INSERT INTO articles SET articletitle='$articletitle', articleorganization='$articleorganization', articledate='$articledate', articleurl='$articleurl' ");
mysql_query("INSERT INTO articles_tags SET articletags='$articletags' ")
or die(mysql_error());
header("Location:addsuccess.php");
}
}
else
{
renderForm('','','','','');
}
?>
Currently the form will submit the correct information to two tables within the database, articles and articles_tags, but I can't figure out how to make a relation.
The end result is that the checkboxes on a separate form will be editable (that is, if I check a box on the entry page and then go to the edit page, that box will already be checked, and it can then be unchecked and that entry can be updated).
Right now, though, I'm trying to make the relation appear in the second table. The layout of the tables is:
articles:
id | articletitle | articleorganization | articledate | articleurl
articles_tags:
id | article_id | tag_id | articlestags
I think I need to somehow get the article ID and the tag ID and insert them into the second table(?).
Does this require a third table?
Am I even on the right track here?
解决方案
Inline tag storage
Depending on the size of your tags you could store the them inside the article_tags table:
article_id | tag_contents
Add UNIQUE(article_id, tag_contents) to avoid storing duplicate tags per article
Important
This approach has a downside; the tags are not connected to each other, so if changes are made to a tag you have to update the whole article_tags table. Also, if the tags are considerably longer than 4 bytes you're better off with the next solution.
External tag storage
Create another table tags:
id | tag_contents
Add UNIQUE(tag_contents) to avoid storing duplicate tags
Modify `article_tags:
article_id | tag_id
Add UNIQUE(article_id, tag_id) to avoid storing duplicate tags per article
这篇关于设置“标签"MySQL 数据库中的条目系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
