Complex Search Queries in Mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Complex Search Queries in Mysql

Post 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
choppsta
Forum Contributor
Posts: 114
Joined: Thu Jul 03, 2003 11:11 am

Post 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...

?>
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post 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;
Stoneguard
Forum Contributor
Posts: 101
Joined: Wed Aug 13, 2003 9:02 pm
Location: USA

Post 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)
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Building complex MYSQL serach query

Post 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
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post 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
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post 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
Post Reply