Please help with db design...

I posted a question on db design, and a problem I was having with duplicate entry... here is a post by Laidback with help...


" The primary problem you have is due to poor database design. You need to have a primary key set or even a non-primary, but unique index. If you have this then your data will not be duplicated. You would get an error on this page, but you will not have duplicate data. You can fix the error simply...All you need to do is create a function that checks if that key exists in the database and if it does exist craft a friendly error messages and kindly direct the user on how to re-input their data and resubmit.
"

Now I am posting the code for one of the tables to see how it should be changed:

"CREATE TABLE teachnews ( id smallint(4) unsigned NOT NULL AUTO_INCREMENT,
title varchar (50) NOT NULL,
date varchar (50) NOT NULL,
body text NOT NULL,
message1 varchar (50) NOT NULL,
message2 varchar (50) NOT NULL,
PRIMARY KEY (id), UNIQUE KEY (id));"

and here is the code for entering data into this table:

[php]<?php
session_start();
<?php
$result = mysql_query("SELECT * FROM teachs WHERE teach = '$foldername' ",$connection);
while ($myrow = mysql_fetch_array($result)) {
printf("<a href=\"%s?id=%s\">%s</a> \n", $PHP_SELF, $myrow["id"], $myrow["title"]);
printf("<a href=\"%s?id=%s&delete=yes\">(DELETE)</a><br>", $PHP_SELF, $myrow["id"]);
echo "<hr>";
}
?>
</p>
<p>&nbsp;</p></td>
</tr>
<tr>
<td colspan="6" valign="top"> <p>
<?php
@mysql_select_db($db_name,$connection);
if ($submit) {
// here if no ID then adding else we're editing
if ($id) {
$sql = "UPDATE teachs SET title ='$title',date='$date',body='$body',popup='$popup' ,popuptext='$popuptext', teacher='$foldername' WHERE id=$id";
} else {
$sql = "INSERT INTO teachs (title,date,body,popup,popuptext,teacher) VALUES ('$title','$date','$body','$popup','$popuptext','$ foldername')";
}
// run SQL against the DB
$result = mysql_query($sql);
echo "Record updated/edited!<p>";
} elseif ($delete)
{
// delete a record
$sql = "DELETE FROM teachs WHERE id=$id";
$result = mysql_query($sql);
echo "$sql Record deleted!<p>";
} else {
// this part happens if we don't press submit
if (!$id) {
// print the list if there is not editing

}
?>
<form method="post" action="<?PHP echo $PHP_SELF?>">
<?PHP
if ($id) {
// editing so select a record
$sql = "SELECT * FROM teachs WHERE id=$id";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
$id = $myrow["id"];
$title = $myrow["title"];
$date = $myrow["date"];
$body = $myrow["body"];
$popup = $myrow["popup"];
$popuptext = $myrow["popuptext"];
// print the id for editing
?>
<p>
<input type=hidden name="id" value="<?PHP echo $id ?>">
<?PHP
}
?>
</p>
Title of News:
<input type="Text" name="title" value="<?PHP echo $title ?>">
<br>
<br>
Date Assigned:
<input type="Text" name="date" value="<?PHP echo $date ?>">
<br>
<br>
Message: ( Important! You must user 'Enter' key to go to next line. Else
the lines on the page will <br>
be VERY long. )<br>
<font size="2">( If you want to enter a web address into this message
box, try <em><strong>this</strong></em>: <br>
Enter your normal text, then this script: <font color="#0000FF"><a
href ="http://</font></font><font color="#FF0000" size="2">www.yourwebpage.com</font><font color="#0000FF" size="2">"></font><font color="#FF0000" size="2">YourWebName</font><font color="#0000FF" size="2"></a>
</font><font size="2"><br>
Where it says "<font color="#FF0000">yourwebpage.com</font>"
you put in the address of the site (www.google.com) and where it<br>
says "<font color="#FF0000">YourWebName</font>" you replace
with the title you want readers to see (Google). The <font color="#0000FF">blue
</font>type is HTML code!<br>
If you want to know how to change colors, use italics, and input email
links, view the Instructions / Help page.)</font> <br>
<TEXTAREA name="body" wrap cols=80 rows=10><?PHP echo $body ?></TEXTAREA>
<br>
If you wish to add more text to your Quick News, <br>
either check the radio button to add More Info. to <br>
a popup window, then include the text in the text box below.<br>
<input name="popup" type="radio" value="yes">
<br>
Add the extra long text that will apear in the popup window.<br>
<TEXTAREA name="popuptext" wrap cols=80 rows=10><?PHP echo $popuptext ?></TEXTAREA>

<input type="hidden" name="teacher" value= "<?php echo "$teachername"; ?>" >
<input type="Submit" name="submit" value="Enter information">
<a href="<?PHP echo $PHP_SELF?>">Clear and Refresh</a>
</form>
<p>&nbsp;</p>
<?PHP
}
?></p>[php]

Laidback described a way to change the script to check for an existing entry, and if so, refresh? Please help.

In the teachnews table you have: PRIMARY KEY (id), UNIQUE KEY (id)

That is redundant since a primary key is already unique.

I see you have a column described as: title varchar (50) NOT NULL

This is a good candidate for a unique key
You could also use the body text, but I think title is just as good, and likely would be less costly to query and compare on.

It is quite simple to check for an existing value. Just run a query something like the following:

SELECT title from teachnews WHERE title=$_POST['title'];

Just an idea. You will have to tighten it up for yourself. I suspect this will be all you need though... You can check this query by using the mysql_num_rows function.

This would be a duplicate item if that function returns a positive number ( > 0 ).

I have an "if" statement at the beginning for authentication that I didn't display, and one further down for "submit",

Where would you suggest I put in the query? I need a little more explaination.

Thanks laidback, I almost have it...


lbd