Page 1 of 1

Complex Search Queries in Mysql

Posted: Wed Oct 15, 2003 7:52 am
by kendall
Hello,

I am trying to build a search form that will invlove a complex query for two separate terms....to give an example of what i mean....

e.g.

I have a form that searchs a daabase or 2 bits of inoformation

form - input phrase

and

form - input year - input month - input day

the form allows the user to search by string term or by supplying a date.

now my table is made up

table - index full text (title, description)
table - field date(yy mm dd)

so the form searchs the tables index if the query is for the phrase and the date field if the query is for the date. Now the scenario is

the search script should be able to search for either a string or date or both

for the date string the user may enter only a year or a month or a day or a combination of the 3

In trying to build the script i realise that it may be a wise thing to do 2 separate searchs i.e mysql_query(search for string,$connection) then mysql_query(search for date,$connection) and then combine the results of both for display.

my question is?
In methods of building complex search forms and scripts is my theory correct? or can this be doen with on simple query with the database i.e. mysql_query(search for string or date,$connection)
This question is more of a matter for advice...your input is greatly appreciated.

Kendall

Posted: Wed Oct 15, 2003 9:15 am
by choppsta
The way I approach this kind of thing is to build the SQL query dynamically...

For example, something like:

Code: Select all

<?php

function process_form() {

	// GET THE FORM VARIABLES
	// (this presumes Magic Quotes GPC is on)

	$phrase = $_POST["phrase"];

	if ($_POST["year"] && $_POST["month"] && $_POST["day"]) {
		$date = $_POST["year"].'-'.$_POST["month"].'-'.$_POST["day"];
	}

	// BUILD THE WHERE CLAUSE

	if ($phrase) {
		$where = " AND (text LIKE '%$phrase%')";
	}

	if ($date) {
		$where .= " AND (date = '$date')";
	}

	// DO THE QUERY

	$query = "SELECT * FROM Table WHERE 1 $where ORDER BY stuff";
	$query = mysql_query($query);

	//...
	//etc...
	
}
?>
Hope this helps...

?>

Posted: Thu Oct 16, 2003 3:51 pm
by xisle
take it a step further and write a switch() or if statements to handle just a month or just a year, etc.

Code: Select all

// m/d/y
  if($m !="" && $d !="" && $y !="")&#123;
    $dfind=" && entrydate LIKE '$y-$m-$d'";
  &#125;
  // y
  if($m =="" && $d =="" && $y !="")&#123;
    $dfind=" && entrydate LIKE '$y-%'";
  &#125;
  // m
  if($m !="" && $d =="" && $y =="")&#123;
    $dfind=" && entrydate LIKE '%-$m-%'";
  &#125;
  // d
  if($m =="" && $d !="" && $y =="")&#123;
    $dfind=" && entrydate LIKE '%-$d'";
  &#125;     
  // d/y  
  if($m =="" && $d !="" && $y !="")&#123;
    $dfind=" && entrydate LIKE '$y-%-$d'";
  &#125;
  // m/y  
  if($m !="" && $d =="" && $y !="")&#123;
    $dfind=" && entrydate LIKE '$y-$m-%'";
  &#125;  
  // m/d 
  if($m !="" && $d !="" && $y =="")&#123;
    $dfind=" && entrydate LIKE '%-$m-$d'";
  &#125;

Posted: Thu Oct 16, 2003 6:52 pm
by Stoneguard
Since you are querying data from two tables with different formats, I would simply run two different queries. However, you can use the UNION statement to run two selects in one query and join the results together. Something like

Code: Select all

(select ColA as Column1, ColB as Column2 from Table1)
UNION
(select ColC as Column1, ColD as Column2 from Table2)

Building complex MYSQL serach query

Posted: Fri Oct 17, 2003 7:49 am
by kendall
Stoneguard,

I don't think you quite followed the question...i'm however NOT searching two tables but two fields consisting of a DATE and STRING TYPES...However that UNION function seems like something i should explore...how does it workif i was to UNION the same table using aliases? .... im gonna try it out

Choppsta,

Thanks for your input it's what would have normally been done...i just thought maybe someone cud have seen a more convenient way.

Xsile,
Hmmmm....believ it or not i was trying to have a go at using it like that but i found it a bit legnthly and tedious or untidy for lack of a better word....

guys,

i was doing my own testing and came up with this

Code: Select all

$search = "SELECT *, DATE_FORMAT(Date, '%%W %%D %%M %%Y') AS theDATE, MATCH(Title, Description) AGAINST('%s') AS Score FROM online_events WHERE MATCH(Title, Description) AGAINST('%s') OR YEAR(Date) = '%s' AND MONTH(Date) = '%s' OR DAYOFMONTH(Date) = '%s' ORDER BY Score ASC %s";
		// get total results
		$query = sprintf($search,$term,$term,$year,$month,$day,false);
it is close to what i want but still lack some accuracy all i do is a

if $year; $year = $year or false kinda approach

Kendall

Posted: Fri Oct 17, 2003 10:12 am
by xisle
I knew you would say that LOL
...not sure the query will be simple enough given what you need to accomplish, so I would break the query up dynamically. But that is just my opinion.

With my above example the query 'could' be quite clean :wink:

Code: Select all

$query="SELECT id from feed 
WHERE id!=''&#123;$keywordfind&#125;&#123;$categoryfind&#125;&#123;$datefind&#125;";
$result=mysql_query($query) or die(mysql_error());
When you get it working please share as it will be quite useful.
I also enjoy figuring out a complex query, it is very cool when you get it correct :D

Posted: Fri Oct 17, 2003 10:57 am
by kendall
xilse,

AH...true i am however more dependent on MYSQL to do the tedious i am looking into the UNION thing as what i would want to do is do both query regardless of wheather it needs to or not as i like the potential that the query can return results from both aspects combined. Sorting may be a bit hard but worth it/...i think. Im yet to attempt it doe...got somethings pressing to get rid off.

Will post my findings for reference

Kendall