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 !=""){
$dfind=" && entrydate LIKE '$y-$m-$d'";
}
// y
if($m =="" && $d =="" && $y !=""){
$dfind=" && entrydate LIKE '$y-%'";
}
// m
if($m !="" && $d =="" && $y ==""){
$dfind=" && entrydate LIKE '%-$m-%'";
}
// d
if($m =="" && $d !="" && $y ==""){
$dfind=" && entrydate LIKE '%-$d'";
}
// d/y
if($m =="" && $d !="" && $y !=""){
$dfind=" && entrydate LIKE '$y-%-$d'";
}
// m/y
if($m !="" && $d =="" && $y !=""){
$dfind=" && entrydate LIKE '$y-$m-%'";
}
// m/d
if($m !="" && $d !="" && $y ==""){
$dfind=" && entrydate LIKE '%-$m-$d'";
}
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
Code: Select all
$query="SELECT id from feed
WHERE id!=''{$keywordfind}{$categoryfind}{$datefind}";
$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

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