Search Engine - multiple problems

[EternalSorrow]

I'm currently working on a search engine which has various degrees of complexity.  It's design is simple, with two input boxes ($title and $name) and a single drop down menu ($occupation), but the databases are what makes the engine complicated.

 

It reads from these three tables with these makeups:

 

People

ID

Name

Occupation

 

Film

ID

Title

Year

 

Related

Title

Name

 

The problems I'm (currently) having are two:

 

When I choose to search using the $title input box from the FILM table, and leave the $name box from the PEOPLE table empty, I receive the correct $title but also all rows from the PEOPLE table because $name is seeing $empty as "SELECT * FROM people".  What I want is for the $empty input box NOT to be searched in the query UNLESS they have a value matching what was inputted into the $title box.

 

The next problem I have is with the $occupation drop down.  It works perfectly when I use the drop down in conjunction with the $name input box, because they have the same table (PEOPLE).  However, the $title box is SELECTING from the FILM table, and I don't know how to connect the two ($title input box and $occupation drop down) when they don't have any corresponding fields, except if somehow I could relate them through the RELATED database.  I have no idea how to accomplish such a complicated task, and was wondering if anyone else has ever come across this problem themselves.

 

Here's the full code for anybody's pleasure.

<form action="#results" method="POST">
<table align="center" style="text-align: right;">
<tr><td>Title: <input type="text" name="title">

<tr><td>Name: <input type="text" name="name">

<tr><td>Occupation: <select name="occupation">
<option value="all">Search All</option>
<?php
$qOccupation = "SELECT DISTINCT occupation FROM people ";
$rsOccupation = mysql_query($qOccupation) or die ('Cannot execute query');

while ($row = mysql_fetch_array($rsOccupation))
{
extract($row);
echo '<option value="'.$occupation.'">'.$occupation.'</option>';

}
?>
</select>

<tr><td style="text-align: right;">
<input type="submit" name="search" value="Search the Site" class="contact">
</table>
</form>

Here are the <a name="results">results</a> of your search:

<p><?php

if(isset($_POST[search])) {

$title = strtolower(strip_tags(mysql_escape_string($_POST['title'])));
$name = strtolower(strip_tags(mysql_escape_string($_POST['name'])));
$occupation = strip_tags(mysql_escape_string($_POST['occupation']));

if(!empty($title)) {
$terms = "WHERE title LIKE '%$title%'";
}

if(!empty($name)) {
$term = "WHERE name LIKE '%$name%'";
}

$join = (empty($name)) ? "WHERE" : "AND";

$sql_occupation = ($occupation == all) ? "" : "$join occupation='$occupation'";

if ($sql_occupation != ""){
   $join = "AND";
}

$qSearch = "SELECT DISTINCT film.title, image, id, NULL AS occupation, year, 'film' AS table1 FROM film 
$terms UNION ALL SELECT DISTINCT people.name, img, id, occupation, NULL, 'people' FROM people $term $sql_occupation ";

$rsSearch = mysql_query($qSearch) or die(mysql_error());

if (mysql_num_rows($rsSearch) == 0)
{
print '<p>Sorry, there were no results returned for your search. Please try again.</p>';
}
else
{
print '<center><p><strong>'.mysql_num_rows($rsSearch).'</strong> item(s) found.</p></center>';

while ($row = mysql_fetch_assoc($rsSearch))
{
extract($row);
   
if ($row['table1'] == 'film')
echo '<div class="boxgrid captionfull">
<a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg"></a>
<div class="cover boxcaption">
<a href="filmography.php?title='.$title.'">'.$title.' ('.$year.')</a>
</div>
</div>';

if ($row['table1'] == 'people')
echo '<div class="boxgrid captionfull">
<a href="biography.php?name='.$title.'"><img src="images/thumbnails/'.$image.'.jpg"></a>
<div class="cover boxcaption">
<a href="biography.php?name='.$title.'">'.$title.' ('.$occupation.')</a>
</div>
</div>';

}

}

}
?>

[joel24]

to join film and people, use a "JOIN".

your related table should have people ID (people_id) and film ID (film_id) rather than title and name.

$sql = "SELECT * FROM people JOIN related ON people.id = related.people_id JOIN film ON related.film_id = film.id WHERE whatever";

 

 

i don't understand whether you want to search for films? Or search for actors?... shouldn't there be a radio button so they can select?

 

i'd have two seperate MYSQL queries... and then two loops to echo the information, one for people and one for films...?

[EternalSorrow]

to join film and people, use a "JOIN".

your related table should have people ID (people_id) and film ID (film_id) rather than title and name.

$sql = "SELECT * FROM people JOIN related ON people.id = related.people_id JOIN film ON related.film_id = film.id WHERE whatever";

 

I'm still working on the databases and it's easier to view the actual titles and names of the films and people, so this is the way it's set up for now.

 

And if you'll look at the code, it's a little more complicated than merely having a JOIN.  The two tables, PEOPLE and FILM, hold the information and have no relational $field to connect, so no JOIN is possible there.  That's what the relational table, RELATED, is for.

 

The example you've given is entirely disregarding the UNION ALL which is included in my search engine script, which is the only way I've found those two can be in a single statement without a relational $field.  The problem I'm having is because of the lack of relation, I can't figure out how to connect the people.$occupation with the film.$title using the RELATED table, something a simple JOIN doesn't seem able to do (I've tried several ways).

 

i don't understand whether you want to search for films? Or search for actors?... shouldn't there be a radio button so they can select?

 

i'd have two seperate MYSQL queries... and then two loops to echo the information, one for people and one for films...?

 

The search engine has both options available to search simultaneously.  It would hardly be a comprehensive engine without all the variables in both tables included.  Unfortunately, when only one of the input boxes has information entered, all rows for the other table are automatically queried and outputted, like so:

 

People

Leslie Howard

Cary Grant

 

Film

The Scarlet Pimpernel

Arsenic And Old Lace

 

Related

The Scarlet Pimpernel | Leslie Howard

Arsenic And Old Lace | Cary Grant

 

search for title: the

 

output: The Scarlet Pimpernel | Leslie Howard | Cary Grant

 

Now the last name in that sequence, Cary Grant, has no relation to The Scarlet Pimpernel.  The name was outputted because all rows were returned from the PEOPLE table without regard to relation.  Somehow the two tables need to be connected through the RELATED table to filter the content from both tables to have matching relations.

 

Both problems may actually be related, that of the $occupation difficulty and the $empty problem.  Unfortunately I still have no idea how to connect them via the RELATED table.

[EternalSorrow]

I may have actually made a breakthrough with this, but there are still a couple of bugs.

 

First, I found that by modifying the following I could get the two unrelated tables to relate to one another:

 

Original:

if(!empty($title)) {
$terms = "WHERE title LIKE '%$title%'";
}

if(!empty($name)) {
$term = "WHERE name LIKE '%$name%'";
}

 

Revised:

if(!empty($title)) {
$terms = "WHERE film.title LIKE '%$title%'";
}
else {
$terms = "WHERE related.name LIKE '%$name%' AND related.title LIKE '%$title%'";
}

if(!empty($name)) {
$term = "WHERE people.name LIKE '%$name%'";
}
else {
$term = "WHERE related.name LIKE '%$name%' AND related.title LIKE '%$title%'";
}

 

The ELSE gives the tables limited parameters to use when the field is $empty.  However, there are still a few bugs.

 

First, I can't use the drop down menu alone, meaning one of the input texts must be filled in.  It's probably because of the new ELSE statements in the $terms and $term fields, but I can't seem to find a way around eliminating one or the other ($terms/$term versus $occupation).

 

The second problem is no doubt in conjunction with the first.  Whenever I type a person's name and choose the incorrect occupation, it will still come up with the correct films for the person but won't output the person, like so:

 

PEOPLE

Leslie Howard | actor

 

Related

The Scarlet Pimpernel

Pygmalion

 

type in $name: Leslie Howard

$occupation: actress

 

result: The Scarlet Pimpernel | Pygmalion

 

This may also boil down to the $occupation field not connected properly to the FILM table, because such a field doesn't exist there.

 

Any ideas how to properly connect the $occupation field and how to adjust the script so if a visitor wants, they only have to use the $occupation drop down menu when searching?

 

Here's the fully modified script (form is unchanged):

<?php

if(isset($_POST[search])) {

$title = strtolower(strip_tags(mysql_escape_string($_POST['title'])));
$name = strtolower(strip_tags(mysql_escape_string($_POST['name'])));
$occupation = strip_tags(mysql_escape_string($_POST['occupation']));

if(!empty($title)) {
$terms = "WHERE film.title LIKE '%$title%'";
}
else {
$terms = "WHERE related.name LIKE '%$name%' AND related.title LIKE '%$title%'";
}

if(!empty($name)) {
$term = "WHERE people.name LIKE '%$name%'";
}
else {
$term = "WHERE related.name LIKE '%$name%' AND related.title LIKE '%$title%'";
}

$join = (empty($name) && empty($title)) ? "WHERE" : "AND";

$sql_occupation = ($occupation == all) ? "" : "$join occupation='$occupation'";

if ($sql_occupation != ""){
   $join = "AND";
}

$qSearch = "SELECT DISTINCT film.title, image, id, NULL AS occupation, year, 'film' AS table1 FROM film 
LEFT JOIN related ON film.title = related.title $terms   
UNION ALL SELECT DISTINCT people.name, img, id, occupation, NULL, 'people' FROM people 
LEFT JOIN related ON people.name = related.name $term $sql_occupation ORDER BY id DESC ";

$rsSearch = mysql_query($qSearch) or die(mysql_error());

if (mysql_num_rows($rsSearch) == 0)
{
print '<p>Sorry, there were no results returned for your search. Please try again.</p>';
}
else
{
print '<center><p><strong>'.mysql_num_rows($rsSearch).'</strong> item(s) found.</p></center>';

while ($row = mysql_fetch_assoc($rsSearch))
{
extract($row);
   
if ($row['table1'] == 'film')
echo '<div class="boxgrid captionfull">
<a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg"></a>
<div class="cover boxcaption">
<a href="filmography.php?title='.$title.'">'.$title.' ('.$year.')</a>
</div>
</div>';

if ($row['table1'] == 'people')
echo '<div class="boxgrid captionfull">
<a href="biography.php?name='.$title.'"><img src="images/thumbnails/'.$image.'.jpg"></a>
<div class="cover boxcaption">
<a href="biography.php?name='.$title.'">'.$title.' ('.$occupation.')</a>
</div>
</div>';

}

}

}
?>

[joel24]

you've got a many to many relationship between actors and films so you need a association table.

i.e.

 

People

pid <-- people id

Name

Occupation

 

Film

fid <-- film id

Title

Year

 

Related

fid

pid

 

then for each relationship insert the corresponding ID numbers into the related table.

then use joins and you'll be able to do all your searches without the union all.....

 

also, at the moment you're referring to database entries by "name" and "title", both of which are not necessarily unique identifiers for each row, for your links, use unique ids... like <a href="biography.php?pid='.$id.'">

[EternalSorrow]

you've got a many to many relationship between actors and films so you need a association table.

i.e.

 

People

pid <-- people id

Name

Occupation

 

Film

fid <-- film id

Title

Year

 

Related

fid

pid

 

then for each relationship insert the corresponding ID numbers into the related table.

then use joins and you'll be able to do all your searches without the union all.....

 

The "association table" is the RELATED table (what I call "relational").  I've tried to use LEFT JOINs for the $query and have had more negative results.  With a snippet I have no results will show from the PEOPLE table, only from the FILM table, though the number of results shown is the correct amount in every search.

 

So this is what it would do:

 

People

Leslie Howard

Merle Oberon

Raymond Massey

 

Film

The Scarlet Pimpernel

 

Related

The Scarlet Pimpernel | Leslie Howard

The Scarlet Pimpernel | Merle Oberon

The Scarlet Pimpernel | Raymond Massey

 

type in $title: the

output $query: The Scarlet Pimpernel | The Scarlet Pimpernel | The Scarlet Pimpernel

 

When in actuality the output needs to look like this:

output $query: The Scarlet Pimpernel | Leslie Howard | Merle Oberon | Raymond Massey

 

also, at the moment you're referring to database entries by "name" and "title", both of which are not necessarily unique identifiers for each row, for your links, use unique ids... like <a href="biography.php?pid='.$id.'">

 

They are unique identifiers because I have specifically NOT entered duplicate names and/or titles.  This is NOT a permanent condition, but one, as I've written before, which is more easily understandable while I work on the coding.

 

Here's the modified code with the LEFT JOINs, but I would take suggestions on how to improve either code given in this thread:

<p><?php

if(isset($_POST[search])) {

$title = strtolower(strip_tags(mysql_escape_string($_POST['title'])));
$name = strtolower(strip_tags(mysql_escape_string($_POST['name'])));
$occupation = strip_tags(mysql_escape_string($_POST['occupation']));

$termsArray = array();

if(!empty($title)) {
$termsArray[] = "f.title LIKE '%$title%'";
}

if(!empty($name)) {
$termsArray[] = "p.name LIKE '%$name%'";
}

if (count($termsArray) > 0){
   $terms = implode(" AND ", $termsArray);
   $terms = " WHERE ".$terms;
   unset($termsArray);
}

$join = (empty($title) && empty($name)) ? "WHERE" : "AND";

$sql_occupation = ($occupation == all) ? "" : "$join occupation='$occupation'";

if ($sql_occupation != ""){
   $join = "AND";
}

$qSearch = "SELECT DISTINCT f.*, 'film' AS table1, p.*, 'people', r.* FROM related r 
LEFT JOIN film f ON r.title = f.title 
LEFT JOIN people p ON r.name = p.name 
$terms $sql_occupation ";

$rsSearch = mysql_query($qSearch) or die(mysql_error());

if (mysql_num_rows($rsSearch) == 0)
{
print '<p>Sorry, there were no results returned for your search. Please try again.</p>';
}
else
{
print '<center><p><strong>'.mysql_num_rows($rsSearch).'</strong> related item(s) found.</p></center>';

while ($row = mysql_fetch_assoc($rsSearch))
{
extract($row);

if ($row['table1'] == 'film')
echo '<div class="boxgrid captionfull">
<a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg"></a>
<div class="cover boxcaption">
<a href="filmography.php?title='.$title.'">'.$title.' ('.$year.')</a>
</div>
</div>';

if ($row['table1'] == 'people')
echo '<div class="boxgrid captionfull">
<a href="biography.php?name='.$title.'"><img src="images/thumbnails/'.$image.'.jpg"></a>
<div class="cover boxcaption">
<a href="biography.php?name='.$title.'">'.$title.' ('.$occupation.')</a>
</div>
</div>';

}

}

}
?>

[joel24]

for the related items have something like

$lastTitle = '';
while ($r = mysql_fetch_array($rsSearch))
{
$title = $r['title'];
if ($title == $lastTitle)
{
echo ' | ' . $r['name'];
}
else
{
echo "$title | {$r['name']}";
$lastTitle = $title;
}
}

[EternalSorrow]

for the related items have something like

$lastTitle = '';
while ($r = mysql_fetch_array($rsSearch))
{
$title = $r['title'];
if ($title == $lastTitle)
{
echo ' | ' . $r['name'];
}
else
{
echo "$title | {$r['name']}";
$lastTitle = $title;
}
}

 

Could you explain the code, especially where I'm supposed to place this snippet (received an error on attempt)?

[joel24]

<?php

if(isset($_POST[search])) {

$title = strtolower(strip_tags(mysql_escape_string($_POST['title'])));
$name = strtolower(strip_tags(mysql_escape_string($_POST['name'])));
$occupation = strip_tags(mysql_escape_string($_POST['occupation']));

$termsArray = array();

if(!empty($title)) {
$termsArray[] = "f.title LIKE '%$title%'";
}

if(!empty($name)) {
$termsArray[] = "p.name LIKE '%$name%'";
}

if (count($termsArray) > 0){
   $terms = implode(" AND ", $termsArray);
   $terms = " WHERE ".$terms;
   unset($termsArray);
}

$join = (empty($title) && empty($name)) ? "WHERE" : "AND";

$sql_occupation = ($occupation == all) ? "" : "$join occupation='$occupation'";

if ($sql_occupation != ""){
   $join = "AND";
}

$qSearch = "SELECT DISTINCT f.*, 'film' AS table1, p.*, 'people', r.* FROM related r 
LEFT JOIN film f ON r.title = f.title 
LEFT JOIN people p ON r.name = p.name 
$terms $sql_occupation ";

$rsSearch = mysql_query($qSearch) or die(mysql_error());

if (mysql_num_rows($rsSearch) == 0)
{
print '<p>Sorry, there were no results returned for your search. Please try again.</p>';
}
else
{
print '<center><p><strong>'.mysql_num_rows($rsSearch).'</strong> related item(s) found.</p></center>';

$film = '';
$people = '';
$relationships = '';
$lastTitle = '';
$lastPerson = '';

while ($row = mysql_fetch_array($rsSearch))
{
extract($row);

//check if last name equals current name, if so 
if ($name != $lastPerson) 
{
$people .= '<div class="boxgrid captionfull">
<a href="biography.php?name='.$title.'"><img src="images/thumbnails/'.$image.'.jpg"></a>
<div class="cover boxcaption">
<a href="biography.php?name='.$title.'">'.$title.' ('.$occupation.')</a>
</div>
</div>';

$lastPerson = $name;
}

//check if the last film in loop is the same, if so add names to the end, otherwise start new line and add to film variable
$title = $row['title'];
if ($title == $lastTitle)
{
$relationships .= ' | ' . $name;
}
else
{
$relationships .= "<br />$title | {$name}";

$lastTitle = $title;

$film .= '<div class="boxgrid captionfull">
<a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg"></a>
<div class="cover boxcaption">
<a href="filmography.php?title='.$title.'">'.$title.' ('.$year.')</a>
</div>
</div>';
}

}

}

echo "FILM<br />$film<br/>PEOPLE<br/>$people<br/>RELATIONSHIPS<br/>$relations";

}
?>

[EternalSorrow]

<?php

 

if(isset($_POST[search])) {....

 

The previous errors with the $occupation field still exist and if I delete the $occupation field from both search engine scripts I've given, and the one you've given, I can essentially do the same result your script gives using this simpler example (with the added bonus of my result count being correct):

 

<?php

if(isset($_POST[search])) {

$title = strtolower(strip_tags(mysql_escape_string($_POST['title'])));
$name = strtolower(strip_tags(mysql_escape_string($_POST['name'])));
$occupation = strip_tags(mysql_escape_string($_POST['occupation']));

if(!empty($title)) {
$terms = "WHERE film.title LIKE '%$title%'";
}
else {
$terms = "WHERE related.name LIKE '%$name%' AND related.title LIKE '%$title%'";
}

if(!empty($name)) {
$term = "WHERE people.name LIKE '%$name%'";
}
else {
$term = "WHERE related.name LIKE '%$name%' AND related.title LIKE '%$title%'";
}

$qSearch = "SELECT DISTINCT film.title, image, id, NULL AS occupation, year, 'film' AS table1 FROM film 
LEFT JOIN related ON film.title = related.title $terms   
UNION ALL SELECT DISTINCT people.name, img, id, occupation, NULL, 'people' FROM people 
LEFT JOIN related ON people.name = related.name $term ORDER BY id DESC ";

$rsSearch = mysql_query($qSearch) or die(mysql_error());

if (mysql_num_rows($rsSearch) == 0)
{
print '<p>Sorry, there were no results returned for your search. Please try again.</p>';
}
else
{
print '<center><p><strong>'.mysql_num_rows($rsSearch).'</strong> related item(s) found.</p></center>';

while ($row = mysql_fetch_assoc($rsSearch))
{
extract($row);

if ($row['table1'] == 'film')
echo '<div class="boxgrid captionfull">
<a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg"></a>
<div class="cover boxcaption">
<a href="filmography.php?title='.$title.'">'.$title.' ('.$year.')</a>
</div>
</div>';

if ($row['table1'] == 'people')
echo '<div class="boxgrid captionfull">
<a href="biography.php?name='.$title.'"><img src="images/thumbnails/'.$image.'.jpg"></a>
<div class="cover boxcaption">
<a href="biography.php?name='.$title.'">'.$title.' ('.$occupation.')</a>
</div>
</div>';

}
}

}
?>

 

However, I did play with your code to simplify the material a bit, mostly to eliminate unnecessary pieces:

 

<?php

if(isset($_POST[search])) {

$title = strtolower(strip_tags(mysql_escape_string($_POST['title'])));
$name = strtolower(strip_tags(mysql_escape_string($_POST['name'])));
$occupation = strip_tags(mysql_escape_string($_POST['occupation']));

$termsArray = array();

if(!empty($title)) {
$termsArray[] = "f.title LIKE '%$title%'";
}

if(!empty($name)) {
$termsArray[] = "p.name LIKE '%$name%'";
}

if (count($termsArray) > 0){
   $terms = implode(" AND ", $termsArray);
   $terms = " WHERE ".$terms;
   unset($termsArray);
}

$join = (empty($title) && empty($name)) ? "WHERE" : "AND";

$sql_occupation = ($occupation == all) ? "" : "$join occupation='$occupation'";

if ($sql_occupation != ""){
   $join = "AND";
}

$qSearch = "SELECT DISTINCT f.*, p.*, r.* FROM related r 
LEFT JOIN film f ON r.title = f.title 
LEFT JOIN people p ON r.name = p.name 
$terms $sql_occupation ";

$rsSearch = mysql_query($qSearch) or die(mysql_error());

if (mysql_num_rows($rsSearch) == 0)
{
print '<p>Sorry, there were no results returned for your search. Please try again.</p>';
}
else
{
print '<center><p><strong>'.mysql_num_rows($rsSearch).'</strong> related item(s) found.</p></center>';

$film = '';
$people = '';
$relationships = '';
$lastTitle = '';
$lastPerson = '';

while ($row = mysql_fetch_array($rsSearch))
{
extract($row);

//check if last name equals current name, if so 
if ($name != $lastPerson) 
{
$people .= '<div class="boxgrid captionfull">
<a href="biography.php?name='.$name.'"><img src="images/thumbnails/'.$img.'.jpg"></a>
<div class="cover boxcaption">
<a href="biography.php?name='.$name.'">'.$name.' ('.$occupation.')</a>
</div>
</div>';

$lastPerson = $name;
}

//check if the last film in loop is the same, if so add names to the end, otherwise start new line and add to film variable
$title = $row['title'];
if ($title == $lastTitle)
{
$relationships .= ' | ' . $name;
}
else
{
$relationships .= "<br />$title | {$name}";

$lastTitle = $title;

$film .= '<div class="boxgrid captionfull">
<a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg"></a>
<div class="cover boxcaption">
<a href="filmography.php?title='.$title.'">'.$title.' ('.$year.')</a>
</div>
</div>';
}

}

}

echo "$film$people";

}
?>