Search between two fields

[dennismonsewicz]

I am wanting to do an "advanced" search for a full text search script i have working right now. How would I implement two fields and a user go to the search page and type in what they are looking for between those two fields and then have the PHP script search the DB for the requested values?

 

For example: if a user wants to search between to dates and pull up all of the entries that happened between those two dats

[pocobueno1388]

You would use BETWEEN

 

SELECT * FROM table WHERE `date` BETWEEN '$date1' AND '$date2'

[dennismonsewicz]

ok cool i will try that...

 

would my SQL statement be this:

 

select * from table like %$search_variable1% between like %$search_variable2%

[pocobueno1388]

No, like this

 

SELECT * FROM table WHERE '$search_variable1' BETWEEN '$search_variable2'

[dennismonsewicz]

ok...i will try it and get back with ya. thanks for your help

[dennismonsewicz]

ok... it didn't work

 

Here is my code:

 

 $search1 = $_POST['search1'];
		  $search2 = $_POST['search2'];
		  
		  $trimmed1 = trim($search1); //trim whitespace from the stored variables
		  $trimmed2 = trim($search2);
		  
			/*
			// PHP Code that is used to write to a text file. Is not working right now, so is down at the moment
			$myFile = "results.txt";
			$fh = fopen($myFile, 'a') or die("can't open file");
			$stringData = $trimmed;
			fwrite($fh, $stringData); 
			*/

		// rows to return
		$limit=9999; 

		// check for an empty string and display a message.
		if ($trimmed1 && $trimmed2 == "")
		  {
		  echo "<p class='contenttext'>Please enter a search...</p>";
		  include "../includes/footer.php";
		  exit;
		  }

		// check for a search parameter
		if (!isset($search1))
		  {
		  echo "<p>We dont seem to have a search parameter!</p>";
		  exit;
		  } else if(!isset($search2))
		  	{
				echo "<p>We don't seem to have a search parameter!</p>";
				}

		//connect to your database ** EDIT REQUIRED HERE **
		mysql_connect("localhost","DBusername","DBpassword"); //(host, username, password)

		//specify database ** EDIT REQUIRED HERE **
		mysql_select_db("DBtable") or die("Unable to select database"); //select which database we're using

		// Build SQL Query  
		$query = "(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from arcamax where '$trimmed1' between '$trimmed2') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from google where '$trimmed1' between '$trimmed2') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from drudge where '$trimmed1' between '$trimmed2') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from human_events where '$trimmed1' between '$trimmed2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from newsmax where '$trimmed1' between '$trimmed2') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from street where '$trimmed1' between '$trimmed2') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from townhall where '$trimmed1' between '$trimmed2') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from weatherbug where '$trimmed1' between '$trimmed2') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from worldnet where '$trimmed1' between '$trimmed2') order by mailedlist"; 

		 $numresults=mysql_query($query);
		 $numrows=mysql_num_rows($numresults);

		// If we have no results, offer a google search as an alternative

		if ($numrows == 0)
		  {
		  echo "<h4 class='contenttext'>Results</h4>";
		  echo "<p class='contenttext'>Sorry, your search: <span class='yellowhighlight'> <b>" . $trimmed1 . "</b> & <b>" . $trimmed2 . "</b> </span> returned zero results</p>";

		/*// google
		 echo "<p><a href=\"http://www.google.com/search?q=" 
		  . $trimmed . "\" target=\"_blank\" title=\"Look up 
		  " . $trimmed . " on Google\">Click here</a> to try the 
		  search on google</p>";*/
		  include "../includes/footer.php";
		  exit;
		  }

		// next determine if s has been passed to script, if not use 0
		  if (empty($s)) {
		  $s=0;
		  }

		// get results
		  $query .= " limit $s,$limit";
		  $result = mysql_query($query) or die("Couldn't execute query");

		// display what the person searched for
		echo "<p class='contenttext'>You searched for: <b>" . $trimmed1 . "</b> & <b>" . $trimmed1 . "</p>";

		// begin to show results set
		echo "<p class='contenttext'>Results (Click a product below to view more information) <br /><br />";
		$count = 1 + $s ;

		// now you can display the results returned
		  while ($row= mysql_fetch_array($result)) {
			  $rowid = $row["id"];
			  $promo_code = $row["promo_code"];
			  $start_end_date = $row["start_end_date"];
			  $description = $row["description"];
			  $vendor = $row["mailedlist"];
			  $orders = $row["orders"];
			  $totalrevenue = $row["totalrevenue"];

			  echo "$count.) " . $promo_code . "<br />";
			  echo "Start/End Date: " . $start_end_date . "<br />";
			  echo "Vendor: " . $vendor . "<br />";
			  echo "Description: " . $description . "<br />";
			  echo "Orders: " . $orders . "<br />";
			  echo "Total Revenue: " . $totalrevenue . "<br />";
			  echo "<a href='../media/mediacase.php?action=view&id=" . $rowid . "&db=" . $vendor . "'>View More</a> <br /><br />";
			  $count++ ;
		  } ?>

 

I don't receive an error message just receive this: Sorry, your search:  advanced & advanced  returned zero results

[dennismonsewicz]

any other suggestions?