如何组织MYSQL数据库？

I don't know a lot about MYSQL and am having trouble designing my database structure. I don't understand all the information given to me online too much jargon expecting me to understand other terms and ideas and do not want to take a course for this one job so I hope this is simple enough.

我对MYSQL了解不多,而且我在设计数据库结构时遇到了麻烦。我不明白在网上给我的所有信息太多行话,希望我理解其他的术语和想法,不想为这一项工作选择一门课程,所以我希望这很简单。

I want to create a list of files on a page. There should be 4 fields:

我想在页面上创建一个文件列表。应该有4个字段:

Name - files title

名称 - 文件标题

Units - which of the select units this file applies to (example: 27 or X1 or 10), I want to be able to add multiple units to some files.

单位 - 此文件适用于哪个选择单位(例如:27或X1或10),我希望能够将多个单位添加到某些文件中。

Typeof - What type of file it is (example: swf, pdf, url).

Typeof - 它是什么类型的文件(例如:swf,pdf,url)。

Project - What project the file was produced for (example: sexual health, information literacy)

项目 - 文件的生成项目(例如:性健康,信息素养)

Outcomes - This should be able to store the numbers 1-5 so one file may have (1,3 and 5)

结果 - 这应该能够存储数字1-5,因此一个文件可能有(1,3和5)

The problem is how I want to use this data, I want to be able to first search through units and find a match say for unit 27, then I want to find each project in that unit and list the files within that project.

问题是我想如何使用这些数据,我希望能够首先搜索单位并找到单元27的匹配说,然后我想找到该单元中的每个项目并列出该项目中的文件。

The problem is files may and should appear multiple times on the page because some files have multiple units but I cant think how to best do this, searching the index of a string seems messy.

问题是文件可能并且应该在页面上多次出现,因为有些文件有多个单元但是我不能想如何最好地做到这一点,搜索字符串的索引似乎很乱。

To help visualise what I want here is an image of how the page will be structured: how the page will be structured http://biteof.com/example.bmp

为了帮助可视化我想要的内容,我们将看到如何构建页面的图像:如何构建页面http://biteof.com/example.bmp

Your help is much appreciated thanks.

非常感谢您的帮助。

What I have so far:

到目前为止我所拥有的:

  name    text                       utf8_unicode_ci 
  typeof set('swf','fla','web','pdf') utf8_unicode_ci  No None                
  units text utf8_unicode_ci  No None                
  url text utf8_unicode_ci  No None                
  project set('sexual health','information literacy','experimental') utf8_unicode_ci  No None                
  outcomes text utf8_unicode_ci  No None

@Nick:

<?php 
$query = "SELECT * FROM `repository` WHERE `units` = '27' LIMIT 0, 999 ";
$result=mysql_query($query) or die(mysql_error()) ;
$num = mysql_num_rows($result);

$i=0;
while ($i < $num) {

$name=mysql_result($result,$i,"name");
$filetype=mysql_result($result,$i,"typeof");
$project=mysql_result($result,$i,"project");
$units=mysql_result($result,$i,"units");
$url=mysql_result($result,$i,"url");

echo "File name: ".$name."<br>";
echo "Units: ".$units."<br>";
echo "In project: ".$project."<br>";
echo "Url: ".$url."<br>";

$i++;
}
?>

2 个解决方案

#1

After reading your question a few times it seems that you understand how to build the tables, you just need help querying them. Do you have code you can share that you've tried?

在阅读了几次问题之后,您似乎了解了如何构建表,您只需要帮助查询它们。你有可以分享的代码吗?

You should probably do a table join.

你应该做一个表连接。

#2

Solved :)

I decided to make a new table for each unit because the file may be the same across units but the outcomes are different. the rest of my database I kept the same and used php to sort the probably horribly structured db... here is my code if your interested:

我决定为每个单元制作一个新表,因为不同单位的文件可能相同,但结果不同。我的数据库的其余部分保持不变,并使用PHP来排序可能可怕的结构化数据库...如果您感兴趣,这里是我的代码:

<?php 
$table = array("301","302","310","311","25","26","27","28","29","1","2","3","4","8","10","X1","X2");
for ($a=0;$a<sizeof($table);$a++) {
$query = "SELECT * FROM `$table[$a]`";
$result=mysql_query($query) or die(mysql_error()) ;
$num = mysql_num_rows($result);
if ($num > 0) {
include($_SERVER['DOCUMENT_ROOT']."/units_and_evidence/units/".$table[$a].".php");
$projects = array();
for ($b=0;$b<$num;$b++) {
$project=mysql_result($result,$b,"project");
array_push($projects, $project);
}
$unique_projects = array_unique($projects);
for($c=0;$c<sizeof($unique_projects);$c++) {
$project_string = ucfirst($unique_projects[$c]);
print ('<div class="project"><h3>'.$project_string.'</h3>');
for($d=0;$d<$num;$d++) {
$name=mysql_result($result,$d,"name");
$project=mysql_result($result,$d,"project");
$typeof=mysql_result($result,$d,"typeof");
$url=mysql_result($result,$d,"url");
$outcomes=mysql_result($result,$d,"outcomes");
if ($project == $unique_projects[$c]) {
$outcomes_array= array();
for ($e=0;$e<strlen($outcomes);$e++) {
array_push($outcomes_array, $outcomes[$e]);
}
rsort($outcomes_array);
if ($typeof == swf) {
$proj = str_replace(' ', '', $project); 
$file = str_replace(' ', '', $url); 
print ('<li><a href="flashdelivery.php?proj='.urlencode($proj).'&file='.urlencode($file).'" class="'.$typeof.'selector">');
}
else {
print ('<li><a href="'.$url.'" class="'.$typeof.'selector">');
}
for ($f=0;$f<sizeof($outcomes_array);$f++) {
  print ('<span class="n'.$outcomes_array[$f].'"></span>');
}
print ('&raquo; '.ucfirst($name).'</a></li>');
}
}
print ('</div>');
}
print ("</ul>");
}
}
include($_SERVER['DOCUMENT_ROOT']."/footer.php"); 
?>

#1