Very complex script i need help with please.

[jerry_louise]

Needing quite a hand here i best explain its use, right there is a form on my stories page for searching, i has 4 fields (body, poster, author, title) and it also has 3 little icons(so far) that are descriptive of a theme in a story such as violence, or drama and when clicked these can be a true or false state for working with php.
My problem comes when working all of this into a search system, i would like a user to be able to enter data into the fields for example title"Murder novel 1", author"Jenny", title(none for this example and same for the body). and The user has 2 icons selected say Drama =true and Violence=true.
So the happy script should return a result that has a story that contains all of these things only.
So my scriptie is as below and does not really work and its getting beyond my ability's to work this out its so complex but when it works will be so great please give me a hand.

[jerry_louise]

<?php
	echo '<div class="seach_box_stories">';
	if (isset($_GET['search'])) {
	session_start();  

	if (isset($_POST['search'])) {
    	$_SESSION['search'] = $_POST['search'];
	}
	$search = $_SESSION['search'];
	echo '<span class="search_left">Search results.</span>';
	}
	else {
	echo '<span class="search_left">Use the form below to preform a detailed search.</span>';
	}
	echo '</div>'; // end seach_box_stories //
	if (isset($_GET['search'])) {
		// null //
	}
	else {
		echo '<form action="index.php?view_mode=advanced_search&search" method="post">';
			echo '<span class="search_left_form">';
				echo '<span class="form_inside">Title:</span><span class="form_inside"><input type="text" name="title"></span>';
			echo'</span>';
			echo '<span class="search_right_form">';
				echo '<span class="form_inside">Author:</span><span class="form_inside"><input type="text" name="author"></span>';
			echo'</span>';
			echo '<span class="search_left_form">';
				echo '<span class="form_inside">Poster:</span><span class="form_inside"><input type="text" name="poster"></span>';
			echo'</span>';
			echo '<span class="search_right_form">';
				echo '<span class="form_inside">Body text:</span><span class="form_inside"><input type="text" name="body"></span>';
			echo'</span>';
			echo '<span class="search_tag_form"><span class="form_inside">Click on tags to include them in your search.</span>';
			echo '<table width="100px" cellpadding="3px" cellspacing="2px">
				<tr>
					<td>
						<a class="checkbox_item1" rel="item1" title="Using item1 or dummy tits."></a>
						<input id="item1" name="item1" value="1" type="hidden">

					<script type="text/javascript">
					$(".checkbox_item1").click(function() {
					$(this).toggleClass("selected");
					valfield = document.getElementById(this.rel);
					valfield.value = (valfield.value == 0) ? 1 : 0;
					});
					</script>	
				</td>
				<td>
					<a class="checkbox_item2" rel="item2" title="Using item2."></a>
					<input id="item2" name="item2" value="1" type="hidden">

					<script type="text/javascript">
					$(".checkbox_item2").click(function() {
					$(this).toggleClass("selected");
					valfield = document.getElementById(this.rel);
					valfield.value = (valfield.value == 0) ? 1 : 0;
					});
					</script>	
				</td>
				<td>
					<a class="checkbox_item3" rel="item3" title="Using item3."></a>
					<input id="item3" name="item3" value="1" type="hidden">

					<script type="text/javascript">
					$(".checkbox_item3").click(function() {
					$(this).toggleClass("selected");
					valfield = document.getElementById(this.rel);
					valfield.value = (valfield.value == 0) ? 1 : 0;
					});
					</script>	
				</td>
			</tr>
		</table>
		';

[jerry_louise]

echo'</span>';
			echo '<span class="search_submit_form">';
				echo '<span class="form_inside"><input type="submit" value="Search" class="form_button"></span>';
			echo'</span>';
		echo '</form>';
	}
	if (isset($_GET['search'])) {
	session_start();  

	if (isset($_POST['title'])) {
    	$_SESSION['title'] = $_POST['title'];
	}
	$title_search = $_SESSION['title'];
	if (isset($_POST['poster'])) {
    	$_SESSION['poster'] = $_POST['poster'];
	}
	$poster_search = $_SESSION['poster'];
	if (isset($_POST['author'])) {
    	$_SESSION['author'] = $_POST['author'];
	}
	$author_search = $_SESSION['author'];
	if (isset($_POST['body'])) {
    	$_SESSION['body'] = $_POST['body'];
	}
	$body_search = $_SESSION['body'];

////constants////
	if ($_POST['item1'] == "0") {
		$item1 = "true";
	}
	else {
		$item1 = "false";
	}
	if ($_POST['item2'] == "0") {
		$item2 = "true";
	}
	else {
		$item2 = "false";
	}
	if ($_POST['item3'] == "0") {
		$item3 = "true";
	}
	else {
		$item3 = "false";
	}
			// how many rows to show per page
$rowsPerPage = 20;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
	$pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
// how many rows we have in database
$query   = "SELECT COUNT(story.`id`) AS `numrows`, tag.`id`, tag.`item1`, tag.`item2`, tag.`item3` FROM `storys` AS `story` LEFT JOIN `storys_key` AS `tag` ON tag.`story_id` = story.`id` WHERE
story.`title` LIKE'%$title_search%'
OR
LOWER(story.`title`) LIKE '%$title_search%'
OR
UPPER(story.`title`) LIKE '%$title_search%'
OR
story.`body` LIKE'%$body_search%'
OR
LOWER(story.`body`) LIKE '%$body_search%'
OR
UPPER(story.`body`) LIKE '%$body_search%'
OR
story.`author` LIKE'%$author_search%'
OR
LOWER(story.`author`) LIKE '%$author_search%'
OR
UPPER(story.`author`) LIKE '%$author_search%'
OR
story.`poster` LIKE'%$poster_search%'
OR
LOWER(story.`poster`) LIKE '%$poster_search%'
OR
UPPER(story.`poster`) LIKE '%$poster_search%'
";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav = '';
for($page = 1; $page <= $maxPage; $page++)
{
	if ($page == $pageNum)
	{
		$nav .= "<span class=\"number_button_norm\">$page</span>";   // no need to create a link to current page
	}
	else
	{
		if ($_GET['view_mode'] == "") {
		$nav .= "<span class=\"number_button\"><a href=\"$self?page=$page\">$page</a></span>";
		}
		else {
		$nav .= "<span class=\"number_button\"><a href=\"$self?search&view_mode=".$_GET['view_mode']."&page=$page\">$page</a></span>";
		}
	}		
}

$sql = "SELECT rate.`total_value`, rate.`total_votes`, story.`id`, story.`title`, story.`author`, story.`poster`, story.`views`, story.`body`, story.`flag`, COUNT(comment.`story_id`) AS `commentcount` tag.`id`, tag.`item1`, tag.`item2`, tag.`item3` FROM `storys` story LEFT JOIN `storys_comments` comment ON story.`id`= comment.`story_id` LEFT JOIN `stories_ratings` AS `rate` ON story.`id` = rate.`id` LEFT JOIN `storys_key` AS `tag` ON tag.`story_id` = story.`id`
WHERE
story.`title` LIKE'%$title_search%'
OR
LOWER(story.`title`) LIKE '%$title_search%'
OR
UPPER(story.`title`) LIKE '%$title_search%'
OR
story.`body` LIKE'%$body_search%'
OR
LOWER(story.`body`) LIKE '%$body_search%'
OR
UPPER(story.`body`) LIKE '%$body_search%'
OR
story.`author` LIKE'%$author_search%'
OR
LOWER(story.`author`) LIKE '%$author_search%'
OR
UPPER(story.`author`) LIKE '%$author_search%'
OR
story.`poster` LIKE'%$poster_search%'
OR
LOWER(story.`poster`) LIKE '%$poster_search%'
OR
UPPER(story.`poster`) LIKE '%$poster_search%'
GROUP BY story.id ORDER BY ".$filter_by." LIMIT $offset, $rowsPerPage";
if ($numrows <= $rowsPerPage) {
	//dont display page numbers//
	}
	else {
		echo "<span class=\"story_pagenumber\"><span class=\"story_page_number\">Page number</span>".$nav."</span>";
	}
$result = mysql_query($sql) or die("Query failed"); 
if(mysql_num_rows($result)) 
{
   while($row = mysql_fetch_array($result)) 
   { 
	$id = $row["id"];
	$current_rating = $row["total_value"];
	$count = $row["total_votes"];
	$rating_unitwidth = "20";
	$body = substr($row["body"], 0, 500);
	// determine whether the user has voted, so we know how to draw the ul/li
	echo '<div class="ext_box">';
		if (stripslashes($row["author"]) == "") {
		     printf( 
         '
		<div class="table_row">
			<div class="ext_left">
				<span class="ext_title">%s</span>
				<span class="ext_body">%s...</p></span>
				<span class="info_ext_storys">
					<span class="read_more"><a href="show_story.php?id=%s">Read more</a></span>
					<span class="ext_views">Views: (%s) | Comments (%s)</span>
				</span>
			</div>
			<div class="ext_right">
				<span class="ext_poster_title">Posted by</span>
				<span class="ext_poster">%s</span>
		', 
         stripslashes($row["title"]),  

		 stripslashes($body), 
		 stripslashes($row["id"]),  

		 stripslashes($row["views"]),
		 stripslashes($row["commentcount"]), 
         stripslashes($row["poster"])
      ); 
		}
		else if (stripslashes($row["poster"]) == "") {
		     printf( 
         '
		<div class="table_row">
			<div class="ext_left">
				<span class="ext_title">%s</span>
				<span class="ext_body">%s...</p></span>
				<span class="info_ext_storys">
					<span class="read_more"><a href="show_story.php?id=%s">Read more</a></span>
					<span class="ext_views">Views: (%s) | Comments (%s)</span>
				</span>
			</div>
			<div class="ext_right">
				<span class="ext_author_title">Author</span>
				<span class="ext_author">%s</span>
		', 
         stripslashes($row["title"]),  

		 stripslashes($body),
		 stripslashes($row["id"]),   

		 stripslashes($row["views"]),
		 stripslashes($row["commentcount"]), 
         stripslashes($row["author"])
      ); 
		}
		else {
      printf( 
         '
		<div class="table_row">
			<div class="ext_left">
				<span class="ext_title">%s</span>
				<span class="ext_body">%s...</p></span>
				<span class="info_ext_storys">
					<span class="read_more"><a href="show_story.php?id=%s">Read more</a></span>
					<span class="ext_views">Views: (%s) | Comments (%s)</span>
				</span>
			</div>
			<div class="ext_right">
				<span class="ext_poster_title">Posted by</span>
				<span class="ext_poster">%s</span>
				<span class="ext_author_title">Author</span>
				<span class="ext_author">%s</span>
		', 
         stripslashes($row["title"]),  

		 stripslashes($body), 
		 stripslashes($row["id"]),  

		 stripslashes($row["views"]),
		 stripslashes($row["commentcount"]), 
         stripslashes($row["poster"]),
		 stripslashes($row["author"])
      ); 
		}
		echo '<span class="ext_rating_title">Rating</span>';
		// now draw the rating bar
		$rating_width = @number_format($current_rating/$count,2)*$rating_unitwidth;
		$rating1 = @number_format($current_rating/$count,1);
		$rating2 = @number_format($current_rating/$count,2);
		echo '<span class="ext_rating">';
		echo '<span class="rating">';
		echo '<div class="ratingblock">';
		echo '<div class="bg_nd">';
		echo '<div id="unit_long'.$id.'">';
		echo '<ul id="unit_ul'.$id.'" class="unit-rating" style="width:'.$rating_unitwidth*$units.'px;">';
		echo '<li class="current-rating" style="width:'.$rating_width.'px;">Currently '.$rating2.'/'.$units.'</li>';
		echo '</ul>';
		echo '</div>';
		echo '</div>';
		echo '</div>';
		echo '</span>';
		echo '</span>';
		echo '<span class="ext_tags_title">Tags</span>';
		echo '<span class="ext_tags">';
		include("key.php");
		echo '</span';
		echo '</div>';
		echo '</div>'; // end of ext right
		echo '</div>'; // end of ext box
   }   

} 
else 
{ 
   print '<div class="table_row"><span class="title"><p>Your search turned up no results.</p></span></div>'; 
}
	}
?>

[jerry_louise]

think i mite have been trying to make this too difficult, i gona make a varable that i inject into the sql statment that uses isset to set up the script. something like

$insert_to_sql = 'WHERE (isset($title)) AND (isset($author))';

But the problem i see is how do i work out how many AND i need.

[jerry_louise]

I made a wee test thingie for making the dynamic WHERE statement, now all i need to work out is how to only have as many AND statements as needed and only output the data i need.

<?php
	if (!isset($_GET['subpage'])) {
		echo '<form action="search.php?subpage=upload" method="post" />';
		echo 'Title: <input type="text" name="title">';
		echo '<br />';
		echo 'Body: <input type="text" name="body">';
		echo '<br />';
		echo 'Author: <input type="text" name="author">';
		echo '<br />';
		echo '<input type="checkbox" name="1" value="true">';
		echo '<input type="checkbox" name="2" value="true">';
		echo '<input type="checkbox" name="3" value="true">';
		echo '<input type="submit">';
		echo '</form>';
	}
	if (isset($_GET['subpage']) AND $_GET['subpage'] == "upload") {
		$title = $_POST['title'];
		$body = $_POST['body'];
		$author = $_POST['author'];
		$num1 = $_POST['1'];
		$num2 = $_POST['2'];
		$num3 = $_POST['3'];
		if (isset($_POST['title'])) {
			if ($_POST['title'] == "") {
				// nought //
			}
			else {
			$title_string = "
				story.`title` LIKE'%$title%'
				OR
				LOWER(story.`title`) LIKE '%$title%'
				OR
				UPPER(story.`title`) LIKE '%$title%'
			";
			}
		}
		if (isset($_POST['author'])) {
			if ($_POST['author'] == "") {
				// nought //
			}
			else {
			$author_string = "
				story.`author` LIKE'%$author%'
				OR
				LOWER(story.`author`) LIKE '%$author%'
				OR
				UPPER(story.`author`) LIKE '%$author%'
			";
			}
		}
		if (isset($_POST['body'])) {
			if ($_POST['body'] == "") {
				// nought //
			}
			else {
			$body_string = "
				story.`body` LIKE'%$body%'
				OR
				LOWER(story.`body`) LIKE '%$body%'
				OR
				UPPER(story.`body`) LIKE '%$body%'
			";
			}
		}
		if (isset($_POST['1'])) {
			if ($_POST['1'] == "") {
				// nought //
			}
			else {
			$num1_string = "
				key.`1` = '$num1'
			";
			}
		}
		if (isset($_POST['2'])) {
			if ($_POST['2'] == "") {
				// nought //
			}
			else {
			$num2_string = "
				key.`2` = '$num2'
			";
			}
		}
		if (isset($_POST['3'])) {
			if ($_POST['3'] == "") {
				// nought //
			}
			else {
			$num3_string = "
				key.`3` = '$num3'
			";
			}
		}
		echo 'WHERE '.$title_string.' AND '.$author_string.' AND '.$body_string.' AND '.$num1_string.' AND '.$num2_string.' AND '.$num3_string.''; 
		echo '<br />';
		echo '<a href="search.php">back</a>';
	}
?>

[igorlopez]

A somewhat clumsy way could be:

$strArray[] = $title_string;
$strArray[] = $author_string;
$strArray[] = $body_string;
$strArray[] = $num1_string;
$strArray[] = $num2_string;
$strArray[] = $num3_string;
$queryStr = 'WHERE ';
$andStr = ' AND ';
foreach($strArray as $str)
{
   if(!empty($str))  // I must learn to not use isempty in PHP
   {
      $queryStr = $queryStr . $str . $andStr;
   }
}
// Remove last $andStr added to str
$queryStr = substr($queryStr, 0, strlen($queryStr)-strlen($andStr));
echo $queryStr;

Precaution as usual as I have not tested it myself

[jerry_louise]

comes out empty, included code for you to see.

<?php
	if (!isset($_GET['subpage'])) {
		echo '<form action="search.php?subpage=upload" method="post" />';
		echo 'Title: <input type="text" name="title">';
		echo '<br />';
		echo 'Body: <input type="text" name="body">';
		echo '<br />';
		echo 'Author: <input type="text" name="author">';
		echo '<br />';
		echo '<input type="checkbox" name="1" value="true">';
		echo '<input type="checkbox" name="2" value="true">';
		echo '<input type="checkbox" name="3" value="true">';
		echo '<input type="submit">';
		echo '</form>';
	}
	if (isset($_GET['subpage']) AND $_GET['subpage'] == "upload") {
		echo '<a href="search.php">back</a>';
		$title = $_POST['title'];
		$body = $_POST['body'];
		$author = $_POST['author'];
		$num1 = $_POST['1'];
		$num2 = $_POST['2'];
		$num3 = $_POST['3'];
		if (isset($_POST['title'])) {
			if ($_POST['title'] == "") {
				// nought //
			}
			else {
			$title_string = "
				story.`title` LIKE'%$title%'
				OR
				LOWER(story.`title`) LIKE '%$title%'
				OR
				UPPER(story.`title`) LIKE '%$title%'
			";
			}
		}
		if (isset($_POST['author'])) {
			if ($_POST['author'] == "") {
				// nought //
			}
			else {
			$author_string = "
				story.`author` LIKE'%$author%'
				OR
				LOWER(story.`author`) LIKE '%$author%'
				OR
				UPPER(story.`author`) LIKE '%$author%'
			";
			}
		}
		if (isset($_POST['body'])) {
			if ($_POST['body'] == "") {
				// nought //
			}
			else {
			$body_string = "
				story.`body` LIKE'%$body%'
				OR
				LOWER(story.`body`) LIKE '%$body%'
				OR
				UPPER(story.`body`) LIKE '%$body%'
			";
			}
		}
		if (isset($_POST['1'])) {
			if ($_POST['1'] == "") {
				// nought //
			}
			else {
			$num1_string = "
				key.`1` = '$num1'
			";
			}
		}
		if (isset($_POST['2'])) {
			if ($_POST['2'] == "") {
				// nought //
			}
			else {
			$num2_string = "
				key.`2` = '$num2'
			";
			}
		}
		if (isset($_POST['3'])) {
			if ($_POST['3'] == "") {
				// nought //
			}
			else {
			$num3_string = "
				key.`3` = '$num3'
			";
			}
		}
		$strArray[] = $title_string; 
		$strArray[] = $author_string; 
		$strArray[] = $body_string; 
		$strArray[] = $num1_string; 
		$strArray[] = $num2_string; 
		$strArray[] = $num3_string; 
		$queryStr = 'WHERE '; 
		$andStr = ' AND '; 
		foreach($strArray as $str) 
		{ 
   			if(!isempty($str)) 
   			{ 
     			$queryStr = $queryStr . $str . $andStr; 
   			} 
		}	 
		// Remove last $andStr added to str 
		$queryStr = substr($queryStr, 0, strlen($queryStr)-strlen($andStr)); 
		echo $queryStr;
	}
?>

[igorlopez]

My misstake, you should have gotten an error on !isempty($str) which should be just !empty($str)
Make sure you have error reporting turned on.

[jerry_louise]

thanks i think i can get this to go exactly what i need now. its much simpler than doing it in the sql which is what i was going to do. ^_