Alimadzi,
Okay - given that I have not used normalization before, I followed your advice on normalization and after reading the article laserlight pointed to, I performed the following:
Created 3 tables:
researchers: res_ID (unsigned, auto_increment, primary), last_name, first_name, email, current_member, date_added (timestamp)
surnames_test: sur_ID (small_int,unsigned, auto_increment, primary), surname
SurnamesToResearchers: str_ID (small_int, unsigned, auto_increment, primary), res_ID (small_int, unsigned), sur_ID (small_int, unsigned)
Then within the HTML coding I had the following:
<?
include("../../../includes/db.inc");
//Set the number of columns for the page
$columns = 3;
$dbh=@mysql_connect ("$dbhost", "$dbuser", "$dbpass") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("$dbname");
$qrySurname__letter = "%";
// Output letters as links: (Ascii range from 65 to 90 are letters A to Z!)
// Trying to do this automatically without having to resort to hand coding // of every single link
for($x=65;$x<=90;$x++)
{
if ($x==ord($subsel)) echo "<b>".chr($x)."</b>";
else
echo "<a href=\"surnames.html?letter=".chr($x)."\">".chr($x)."</a> ";
}
$letter = $_GET["letter"];
if (isset($letter))
{
$qrySurname = "SELECT * FROM SurnamesToResearchers,researchers,surnames_test WHERE SurnamesToResearchers.res_ID=researchers.res_ID AND SurnamesToResearchers.sur_ID=surnames_test.sur_ID AND surname LIKE '$letter%' ORDER BY surname";
} else {
$qrySurname = "SELECT * FROM SurnamesToResearchers,researchers,surnames_test WHERE SurnamesToResearchers.res_ID=researchers.res_ID AND SurnamesToResearchers.sur_ID=surnames_test.sur_ID ORDER BY surname";
}
$result = mysql_query($qrySurname);
//Need to know the number of rows, so use this line
$num_rows = mysql_num_rows($result);
echo "#rows: $num_rows";
//Set a new variable called $rows
$rows = ceil($num_rows / $columns);
echo "#rows: $rows";
//For this vertical display, need to run another loop, which will populate an array with our values
while($row = mysql_fetch_array($result)) {
$email[] = $row['email'];
$surname[] = $row['surname'];
$valid_em[] = $row['valid_em'];
$valid_mem[] = $row['current_member'];
$res_ID[] = $row['res_ID'];
}
echo "<TABLE BORDER=1 cellpadding=5 align=center>\n";
for ($i = 0; $i < $rows; $i++) {
echo "<tr>";
for ($j = 0; $j <$columns; $j++) {
if(isset($email[$i + ($j * $rows)])) {
echo "<td width=155><li> <A HREF=\"JavaScript:newWindow('researcher.php?researcher=".$email[$i + ($j * $rows)]."','popup',440,340,'')\">".$surname[$i + ($j * $rows)]."</a> ";
}
if (strcmp("No", $valid_em[$i + ($j * $rows)])==0) {
echo "<img width=16 height=15 align=absmiddle src=\"exclamation.jpg\" border=0> </li>";
}
if (strcmp("Yes", $valid_mem[$i + ($j * $rows)])==0) {
echo "<img align=absmiddle src=\"mem.jpg\" border=0></td>";
}
}
echo "</tr>";
}
echo "</table>";
?>
The results can be seen at the following URL:
http://www.lithuaniangenealogy.org/databases/test/surnames.html
The code generates a table of all the surnames in the database and creates a link displaying the email address of the researcher for that particular surname. When you click on the link, it passes the email address to the file 'researcher.php' and creates a new page listing the researcher email address and the other surnames matching that same email address.
The researcher.php file is as follows:
<?php
include("../../../includes/db.inc");
$dbh=@mysql_connect ("$dbhost", "$dbuser", "$dbpass") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("$dbname");
$researcher = $_GET["researcher"];
if (isset($researcher))
{
$qryResearcher = "SELECT * FROM SurnamesToResearchers,researchers,surnames_test WHERE SurnamesToResearchers.res_ID=researchers.res_ID AND SurnamesToResearchers.sur_ID=surnames_test.sur_ID AND email LIKE '$researcher%' ORDER BY surname";
}
$result = mysql_query($qryResearcher);
//$num = mysql_num_rows($result);
//echo "$num";
echo "<TABLE BORDER=0 cellpadding=5>";
echo "<tr bgcolor=\"#9999CC\">";
echo "<td width=45%><strong>Researcher Information</strong></td>";
echo "</tr>";
echo "<tr><td>Contact email address: <A HREF=\"mailto:$researcher\">$researcher</td></tr>";
echo "<tr><td>Other surnames being researched:</td></tr>";
echo "<tr><td>";
while ($row = mysql_fetch_array($result))
{
echo "<LI><b>{$row['surname']}</B>";
}
echo "</td></tr>";
echo "<tr><td><center><A HREF=\"Javascript:self.close();\">Close Window</A></center></td></tr>";
echo "</TABLE>";
?>
The problem I run into now is that instead of using the researcher email address, I would rather pass the 'res_ID' to 'researcher.php' and still have it give the same information.
I made a minor modifcation to the regular HTML page:
http://www.lithuaniangenealogy.org/databases/test/surnames2.html
Here you can see the res_ID is used but when you click on the link it errors out with the following:
Researcher Information
Contact email address: 1
Other surnames being researched:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/web/lithuani/public_html/databases/test/researcher2.php on line 26
<?php
include("../../../includes/db.inc");
$dbh=@mysql_connect ("$dbhost", "$dbuser", "$dbpass") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("$dbname");
$researcher = $_GET["researcher"];
if (isset($researcher))
{
$qryResearcher = "SELECT * FROM SurnamesToResearchers,researchers,surnames_test WHERE SurnamesToResearchers.res_ID=researchers.res_ID AND SurnamesToResearchers.sur_ID=surnames_test.sur_ID AND res_ID LIKE '$researcher%' ORDER BY surname";
}
$result = mysql_query($qryResearcher);
//$num = mysql_num_rows($result);
//echo "$num";
echo "<TABLE BORDER=0 cellpadding=5>";
echo "<tr bgcolor=\"#9999CC\">";
echo "<td width=45%><strong>Researcher Information</strong></td>";
echo "</tr>";
echo "<tr><td>Contact email address: <A HREF=\"mailto:$researcher\">$researcher</td></tr>";
echo "<tr><td>Other surnames being researched:</td></tr>";
echo "<tr><td>";
while ($row = mysql_fetch_array($result))
{
echo "<LI><b>{$row['surname']}</B>";
}
echo "</td></tr>";
echo "<tr><td><center><A HREF=\"Javascript:self.close();\">Close Window</A></center></td></tr>";
echo "</TABLE>";
?>
I think the problem is with my join and trying to use the res_ID to pull the correct email address that matches the ID and then pull the resulting surnames.
Thoughts?
Sorry for the long post - has been an education in working with normalized databases.
Thanks!
Richard
