[SOLVED] Double Search Field not working

dennismonsewicz · December 4, 2007

Below is the code:

<div style="margin-left: 25px;">
    <form name="form" action="advanced_search.php" method="post">
      <input type="text" name="search1" /><br />
      <input type="text" name="search2" />
      <input type="submit" name="Submit" value="Search" />
    </form>
</div>
    
    <?php
	    
		  $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 class='contenttext'>We dont seem to have a search parameter!</p>";
		  include "../includes/footer.php";
		  exit;
		  } else if(!isset($search2))
		  	{
				echo "<p class='contenttext'>We don't seem to have a search parameter! Both Fields must be filled out!</p>";
				include "../includes/footer.php";
				exit;
				}

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

		//specify database ** EDIT REQUIRED HERE **
		mysql_select_db("hrmedia_results") 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 '$search1' between '$search2') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from google where '$search1' between '$search2') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from drudge where '$search1' between '$search2') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from human_events where '$search1' between '$search2') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from newsmax where '$search1' between '$search2') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from street where '$search1' between '$search2') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from townhall where '$search1' between '$search2') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from weatherbug where '$search1' between '$search2') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from worldnet where '$search1' between '$search2') 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++ ;
		  } 
?>

For some reason it is not able to search the databases. Anyone got any ideas?

pocobueno1388 · December 4, 2007

Okay, try changing these lines

<?php

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

		 $numresults=mysql_query($query);
?>

To

<?php

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

		 $numresults=mysql_query($query)or die(mysql_error()." With query $query");

echo "<p>$query<p>";

?>

Show us what it gives you.

?>

dennismonsewicz · December 4, 2007

this is what i got:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') union (select id,promo_code,start_end_date,mailedlist,description,orders,tota' at line 1 With query (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from arcamax where 'advanced' between 'advanced') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from google where 'advanced' between 'advanced') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from drudge where 'advanced' between 'advanced') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from human_events where 'advanced' between 'advanced') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from newsmax where 'advanced' between 'advanced') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from street where 'advanced' between 'advanced') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from townhall where 'advanced' between 'advanced') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from weatherbug where 'advanced' between 'advanced') union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from worldnet where 'advanced' between 'advanced') order by mailedlist

pocobueno1388 · December 4, 2007

It's printing out

where 'advanced' between 'advanced'

Advanced? I thought the variables held dates? Now I'm confused on what your even trying to do...explain what your trying to select from the database.

dennismonsewicz · December 4, 2007

Sorry It is supposed to be a date range. I just reloaded the page without thinking. Sorry. I tried another one and it does the same thing when I insert a date.

Example: 1/1/04

pocobueno1388 · December 4, 2007

I'm not positive, but I think your date format has to be yyyy-mm-dd.

I'm going to have to come back to this thread later on today, I have to go to work.

pocobueno1388 · December 5, 2007

ok...

for my start and end dat it is in one field as follows:

start_end_date (Varchar 255)

So I need to break that field up into the follow:

start (Varchar 255)

end (Varchar 255)

year (Varchar 255)

??

Okay...I think you need to explain exactly what your doing. Also, post your database structure.

I thought you had a table with a date in it (not a start AND end date for each row) and you wanted to be able to search through that table for any records with a date in between two given dates.

dennismonsewicz · December 5, 2007

id int(11)

promo_code varchar(255)

start_end_date varchar(255)

mailedlist varchar(255)

description varchar(255)

subject text

mailqty varchar(255)

actualqty varchar(255)

netdelivered varchar(255)

totalcost varchar(255)

delivered varchar(255)

uniqueopen varchar(255)

percentopen varchar(255)

clicks varchar(255)

percentclicks varchar(255)

leads varchar(255)

clicklead varchar(255)

orders varchar(255)

orderconversion varchar(255)

totalrevenue varchar(255)

avgsale varchar(255)

cpm varchar(255)

roi varchar(255)

netrevenue varchar(255)

The bolded items above are the row names. I am wanting to do a search between two dates and display all the results between those two dates. If I am completely off the wall with any of this please let me know. My PHP work is getting a lot better but I haven't studied table structure that well (as you can probably tell from the above structure)

pocobueno1388 · December 5, 2007

Okay, give me an example of what data would look like in "start_end_date".

dennismonsewicz · December 5, 2007

16-Aug, 4-Sep, 30-July some are like that and some are like 3/17/2007- 3/30

pocobueno1388 · December 5, 2007

Okay, I would definitely separate the start and end date into two different fields, and make them in the format of yyyy-mm-dd. make them the "date" type in the DB. Once you do that, then we can move on to the next step.

dennismonsewicz · December 5, 2007

Gotcha... That is going to take a while. so Let me talk with a data entry person and be right back with you. Thanks for all your help up to this point.

dennismonsewicz · December 5, 2007

I figured out how to do it. I used IN instead of BETWEEN so my code looks like this:

$query = "(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from arcamax where start_end_date in ('$search1',  '$search2')) union
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from google where start_end_date in ('$search1',  '$search2')) union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from drudge where start_end_date in ('$search1',  '$search2')) union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from human_events where start_end_date in ('$search1',  '$search2')) union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from newsmax where start_end_date in ('$search1',  '$search2')) union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from street where start_end_date in ('$search1',  '$search2')) union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from townhall where start_end_date in ('$search1',  '$search2')) union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from weatherbug where start_end_date in ('$search1',  '$search2')) union 
(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from worldnet where start_end_date in ('$search1',  '$search2')) order by id";

So problem solved thanks

Sign In

[SOLVED] Double Search Field not working

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information