EternalSorrow Posted November 14, 2009 Share Posted November 14, 2009 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>'; } } } ?> Quote Link to comment Share on other sites More sharing options...
joel24 Posted November 14, 2009 Share Posted November 14, 2009 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...? Quote Link to comment Share on other sites More sharing options...
EternalSorrow Posted November 14, 2009 Author Share Posted November 14, 2009 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. Quote Link to comment Share on other sites More sharing options...
EternalSorrow Posted November 14, 2009 Author Share Posted November 14, 2009 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>'; } } } ?> Quote Link to comment Share on other sites More sharing options...
joel24 Posted November 14, 2009 Share Posted November 14, 2009 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.'"> Quote Link to comment Share on other sites More sharing options...
EternalSorrow Posted November 14, 2009 Author Share Posted November 14, 2009 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>'; } } } ?> Quote Link to comment Share on other sites More sharing options...
joel24 Posted November 15, 2009 Share Posted November 15, 2009 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; } } Quote Link to comment Share on other sites More sharing options...
EternalSorrow Posted November 15, 2009 Author Share Posted November 15, 2009 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)? Quote Link to comment Share on other sites More sharing options...
joel24 Posted November 16, 2009 Share Posted November 16, 2009 <?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"; } ?> Quote Link to comment Share on other sites More sharing options...
EternalSorrow Posted November 16, 2009 Author Share Posted November 16, 2009 <?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"; } ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.