How to organise the MYSQL database?

I don't know a lot about MYSQL and am having trouble designing my database structure. I don't understand all the inf开发者_Python百科ormation 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.

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

• 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.
• Typeof - What type of file it is (example: 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)

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.

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

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++;
}
?>

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.

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 
$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"); 
?>