Hello Laura, and welcome to webmaster-talk.com.
That's a rather large question you are asking here.
I'll try to cover it at best.
To begin with, you need to be able to lay down the flow of this milk informations. How are they constructed, and how should tehy be presented.
For example, I suppose that there are going to be a list of producers and X tests for each producers with the results.
This could be presented that way:
The 1/N next to the tables represent how many relations can be there.
From Producers to the tests, there is N tests (virtually any number) to 1 producer.
The TestingDate field will serve to separate each testing batch, and to keep an history of the milk quality.
That covers a simple DB layout, but it looks enough given what you explained before.
As for the site itself, you will need a server side language to query the database.
Be it PHP or ASP, as you prefer.
The layout here is realtively simple:
1) set-up an authentication page
2) create 1 page to list the entries in the database, possibly filtered by producers
3) create a page to modify the producer infos
4) create a page to insert new tests results
Have the page 2, 3 and 4 to check in a session for an indication that the users is correctly logged, or send him back to the login page.
I'd do something like this, in PHP:
Login:
PHP Code:
<?
$_SESSION['logged']=False;
if(sizeof($_POST)>0){
$uname=isset($_POST['username'])?trim(addslashes($_POST['username'])):null;
$pwd=isset($_POST['pwd'])?trim(addslashes($_POST['pwd'])):null;
$q="select * from users where username='$uname' and pwd='$pwd'";
$r=mysql_query($q);
if(mysql_num_rows($r)>0){
//we have found a user with the given username and password, we go to the main list page
$_SESSION['logged']=true;
header('location:/displayList.php');
exit();
}
}
?>
<html>
//The form code here, with an action post to itself
list:
PHP Code:
<?php
if( (!isset($_SESSION['logged'])) || ($_SESSION['logged']==false) ){
//the user is not authenticated, redirect to the login
header('location:/login.php');
exit();
}
$producerId=null;
if(sizeof($_POST)>0){
//The user wants to filter the list
$producer=trim(addslashes($_POST['producerId']));
$filter="and p.ProducerId=$producerId";
}
$q="Select * from Producers p inner join TestAttributes t on t.ProducerFk=p.ProducerId where 1=1 $filter order by p.Name"
$r=mysql_query($q);
?>
<html>
<!-- the page goes here -->
<table>
<tr>
<th>field1</th>
<th>field2</th>
<th>field3</th>
<th>field4</th>
</tr>
<?php
foreach($o=mysql_fetch_object($r)){
//we output the query result
echo "
<tr>
<td>{$o->field1}</td>
<td>{$o->field2}</td>
<td>{$o->field3}</td>
<td>{$o->field4}</td>
</tr>
";
}
?>
And so on.
It's not terribly complicated, it's just time consuming.
