I have three tables in my database; ACTOR_DETAIL, MOVIE_ACTOR, and MOVIE_TITLE. Entries from each table include:
For ACTOR_DETAIL (4 fields = actor sequence number, name, birthday, gender):
(ACTOR_SEQUENCE_NB) (NAME) (BIRTHDAY) (GENDER)
001 John 1/1/1964 Male
002 Dave 2/2/1980 Male
003 Jane 3/3/1975 Female
For MOVIE_TITLE (3 Fields = MOVIE_SEQUENCE_NB, MOVIE TITLE, YEAR)
SSN Name City
001 MovieTitleA 1987
002 MovieTitleB 2008
003 MovieTitleC 2014
and the last table, MOVIE_ACTOR, brings the two together (3 fields = MOVIE_SEQUENCE_NB, ACTOR_SEQUENCE_NB, CHARACTER_PLAYED)
WSN SSN Pay
001 003 The Protagonist (John, who was in MovieTitleC, played the Protagonist)
003 001 The Antagonist (Jane, who was in MovieTitleA, played the Antagonist)
002 003 The Anti-Hero (Dave, who played in MovieTitleC, played the Anti-Hero)
I am currently making a search form, that will allow users to input the name of the Worker. The search form will then take the input name, match it with the corresponding WSN, take the WSN and match it with the corresponding SSN, take the SSN, then match it with the corresponding Store Name and City, then output that information (The Store Name and City from the STORE_NAME table).
In my document titled: searchform.html, this is the snippet that makes the form:
<html>
<head>
<title>Search</title>
</head>
<body bgcolor=#ffffff>
<h2>Search</h2>
<form name="search" method="post" action="searchform.php">
Name of Actor: <input type="text" name="find1" />
<input type="submit" name="search" value="Search" />
</form>
</body>
</html>
and here is my php document:
<?php
include "config.php";
echo "<h2>Search Results:</h2><p>";
if(isset($_POST['search']))
{
$find1 =$_POST['find1'];
$find2 =$_POST['find2'];
$field =$_POST['field'];
}
// We perform a bit of filtering
$find1 = strtoupper($find1);
$find1 = strip_tags($find1);
$find1 = trim ($find1);
$find2 = strtoupper($find2);
$find2 = strip_tags($find2);
$find2 = trim ($find2);
//Now we search for our search term, in the field the user specified
$iname = mysql_query("select ACTOR_DETAIL.ACTOR_SEQUENCE_NB, MOVIE_SEQUENCE_NB.MOVIE_TITLE, MOVIE_SEQUENCE_NB.YEAR_FILMED
from MOVIE_ACTOR join
ACTOR_DETAIL
on MOVIE_ACTOR.ACTOR_SEQUENCE_NB = ACTOR_DETAIL.ACTOR_SEQUENCE_NB join
MOVIE_TITLE
on MOVIE_ACTOR.MOVIE_SEQUENCE_NB = MOVIE_TITLE.MOVIE_SEQUENCE_NB
where ACTOR_DETAIL.NAME = "$find1")
or die(mysql_error());
//And we display the results
while($result = mysql_fetch_array($iname ))
{
echo "<b>Movie Name:</b> " .$result['MOVIE_TITLE'];
echo "<br> ";
echo "<b>Year:</b> ".$result['YEAR_FILMED'];
echo "<br>";
echo "<br>";
}
$anymatches = mysql_num_rows($iname);
if ($anymatches == 0)
{
echo "Sorry, but there aren't any movies with that combination of actors!<br><br>";
}
?>
I have ommitted the DB Connection, but I have confirmed that it works. My error appears to be with the SQL statement, but I'm not quite sure what it is. Can anyone help?
