Search parameters with php

Hi guys,

I recently posted another thread related to a search engine I was working on based on “Build your own DB driven website etc” by Kevin Yank. The search engine is working, however there are a few bugs I would like to fix and don’t know how to. I’ll focuson just one of them in this thread so that it doesn’t get too confusing.

In the database, there is one table for the jokes (called “joke”) and another table for the theme (called “theme”). These two tables are related by another table called “joketheme”. Each joke should be able to have more than 1 theme and I would like the results to list all the themes without repeating the entries. So far, I haven’t been able to achieve that. In fact, I’m not sure how to even designate 2 themes for 1 joke within MySQL.

table 1: joke
id~joketext~other data
1~joke1~
2~joke2~
3~joke3~

table 2: theme
id~name
1
Knock knock
2~~Lawyers

table 3: joketheme
jokeid~themeid
1
1
1
2
2
2
3
~~1

Do you know what I need to change in the code (or in the MySQL) to list more than one theme in each result (if that entry has more than one theme?)?

Here is my code in the search results page. Thank you in advance for any help!:

<section id="search">
	
	<?php

	$dbcnx = @mysql_connect('localhost', 'root', 'password'); 

	if (!$dbcnx) {
		exit('<p>Unable to connect to the ' . 'database server at this time.</p>');
	}

	if (!@mysql_select_db('ijdb')) { 
		exit('<p>Unable to locate the joke ' . 'database at this time.</p>');
	}

	$authors = @mysql_query('SELECT id, name FROM author'); 
	if (!$authors) {
		exit('<p>Unable to obtain author list from the database.</p>');
	}

	$cats = @mysql_query('SELECT id, name FROM category'); 
	if (!$cats) {
		exit( '<p>Unable to obtain category list from the database.</p>');
	} 

	$themes = @mysql_query('SELECT id, name FROM theme'); 
	if (!$themes) {
		exit( '<p>Unable to obtain category list from the database.</p>');
	}

	$geofoci = @mysql_query('SELECT id, name FROM geofocus'); 
	if (!$geofoci) {
		exit( '<p>Unable to obtain category list from the database.</p>');
	}

	?>
		
<form class="searchField" name="input" action="jokes_search.php" method="post">
  <ul>
	<li>
		<label>Search by keyword:</label>
		<input type="text" name="searchtext" class="styleSearchbox" placeholder="By keyword" value="<?php echo $_POST['searchtext']; ?>">
	</li>
    <li>
			<label>OR by the following: </label>
			<label><select name="aid" size="1" class="styleDropdown">
			<option selected value="">Any Author</option> 
			<?php
				while ($author = mysql_fetch_array($authors)) { 
					$aid = $author['id']; 
					$aname = htmlspecialchars($author['name']); 
					echo "<option value='$aid'>$aname</option>\
";
			} 
			?> 
			</select></label>    		
    	</li>
        <li>
        	<label><select name="cid" size="1" class="styleDropdown">
				<option selected value="">Any Category</option> 
			<?php
			while ($cat = mysql_fetch_array($cats)) { 
				$cid = $cat['id']; 
				$cname = htmlspecialchars($cat['name']); 
				echo "<option value='$cid'>$cname</option>\
";
			} 
			?>
			</select></label>
        </li>
        <li>
        	<label><select name="tid" size="1" class="styleDropdown">
				<option selected value="">Any Theme</option> 
			<?php
			while ($theme = mysql_fetch_array($themes)) { 
				$tid = $theme['id']; 
				$tname = htmlspecialchars($theme['name']); 
				echo "<option value='$tid'>$tname</option>\
";
			} 
			?>
			</select></label>
        </li>
        <li>
        	<label><select name="gfid" size="1" class="styleDropdown">
				<option selected value="">Any Region</option>
			<?php
			while ($geofocus = mysql_fetch_array($geofoci)) { 
				$gfid = $geofocus['id']; 
				$gfname = htmlspecialchars($geofocus['name']); 
				echo "<option value='$gfid'>$gfname</option>\
";
			} 
			?>
			</select></label>
        </li>
	 <li><input type="submit" value="Search" class="searchButton"></li>
    </ul>
</form>
</section>
<section id="results">
	<?php

	$dbcnx = @mysql_connect('localhost', 'root', 'password'); 

	if (!$dbcnx) {
		exit('<p>Unable to connect to the ' . 'database server at this time.</p>');
	}

	if (!@mysql_select_db('ijdb')) { 
		exit('<p>Unable to locate the joke ' . 'database at this time.</p>');
		}

	// The basic SELECT statement 

	$select = 'SELECT DISTINCT joke.id, joke.joketext, joke.jokedate, 
				author.id AS author_id, author.name AS author_name, 
				jokecategory.jokeid AS cat_jokeid, jokecategory.categoryid AS joke_catid, category.id AS cat_id, category.name as cat_name, 
				joketheme.jokeid AS theme_jokeid, joketheme.themeid AS joke_themeid, theme.id AS theme_id, theme.name AS theme_name,
				jokegeofocus.jokeid AS geofocus_jokeid, jokegeofocus.geofocusid AS joke_geofocusid, geofocus.id AS geofocus_id, geofocus.name AS geofocus_name';
	$from	= ' FROM joke, author, jokecategory, category, joketheme, theme, jokegeofocus, geofocus'; 
	$where = ' WHERE joke.authorid = author.id AND joke.id = jokecategory.jokeid AND jokecategory.categoryid = category.id AND joke.id = joketheme.jokeid AND joketheme.themeid = theme.id AND joke.id = jokegeofocus.jokeid AND jokegeofocus.geofocusid = geofocus.id';
	$in = ' ORDER BY jokedate DESC';

	$aid = $_POST['aid']; 
	if ($aid != '') { // An author is selected
		$where .= " AND authorid='$aid'";
	}

	$cid = $_POST['cid']; 
	if ($cid != '') { // A category is selected
		$from .= ''; // usually written as ' ,tablename'
		$where .= " AND joke.id=jokecategory.jokeid AND categoryid='$cid'";
	}

	$tid = $_POST['tid'];
	if ($tid != '') { // A theme is selected
		$from .= '';
		$where .= " AND joke.id=joketheme.jokeid AND themeid='$tid'";
	}

	$gfid = $_POST['gfid'];
	if ($gfid != '') { // A region is selected
		$from .= '';
		$where .= " AND joke.id=jokegeofocus.jokeid AND geofocusid='$gfid'";
	}

	$searchtext = $_POST['searchtext'];
	if ($searchtext != '') { // Some search text was specified
		$where .= " AND keywords LIKE '%$searchtext%'";
		}
?>	


	<ol id="results-list">
		
		<?php	
		
		$jokes = @mysql_query($select . $from . $where . $in); 
		if (!$jokes) {
			echo '</table>'; exit('<p>Error retrieving jokes from database!<br />'.
			'Error: ' . mysql_error() . '</p>');
		}
		
		$numrows = mysql_num_rows($jokes);
		if ($numrows>0){
		while ($joke = mysql_fetch_array($jokes)) { 
			$id = $joke['id'];
			$joketext = htmlspecialchars($joke['joketext']);
			$jokedate = htmlspecialchars($joke['jokedate']);
			$aname = htmlspecialchars($joke['author_name']);
			$category = htmlspecialchars($joke['cat_name']);
			$theme = htmlspecialchars($joke['theme_name']);
			$geofocus = htmlspecialchars($joke['geofocus_name']);
			$position = 200;
			$post = substr($joketext, 0, $position);
			echo "<li id=\\"jump\\">
					<article class=\\"entry\\">
						<header>
							<h3 class=\\"entry-title\\"><a href=''>$aname</a></h3>
						</header>
						<div class=\\"entry-content\\">
							<p>$post...</p>
						</div>
						<div class =\\"entry-attributes\\">
							<p>> Category: $category</p>
							<p> > Theme(s): $theme</p>
							<p> > Region(s) of focus: $geofocus</p>
							</div>
						<footer class=\\"entry-info\\">
	        				<abbr class=\\"published\\">$jokedate</abbr>
	        			</footer>
					</article>
				</li>";
		}
	}
		else
			echo "Sorry, no results were found. Please change your search parameters and try again!";
?>

First of all, please put the apropriate code tags around the code you post. In this case the [noparse]

 and 

[/noparse] tags. I added them for you. It makes it so much easier to read the code.

Second, in a script there’s no need to connect to mysql and select a database twice (unless you’ve closed the connection at some point, or you want to connect to another database).

Of course, these two tips don’t solve your problem. But looking at your code, I don’t see why you shouldn’t get more than one theme if the joke has more of them. Can you give an example of what is it you see in your browser, and what you would like to see instead?

Hi Guido and thanks for editing the PHP tags,

I’m attaching an image of what I am getting in the browser when I search with a keyword. The results are true, but repeated. I would like there to be one result and under Theme(s): Agriculture, Civil Society.

Thank you!

Ok I understand the problem.
I don’t have the time right now to work with your code, but please take a look at this post: http://www.sitepoint.com/forums/showthread.php?796179-Got-explanation-what-I-need-to-do-but-don-t-know-how-to-code-it

It’s a similar problem. Try to implement it in your situation.

Thanks, Guido!

It does look like a very similar problem, unfortunately I still don’t really understand the solution…I don’t know exactly how to implement it into my code. I’ll try to figure it out, but if you have some time later on to guide me through it, I would greatly appreciate it!

Hi again, Guido.

I’m still unable to implement from the example you suggested (if anything I think I “broke” something)…do you think you could help me again if/when you have some time?

Thank you,