Page 1 of 1
Complex Search Expressions (performing search queries)
Posted: Mon Aug 11, 2003 8:36 am
by kendall
Hello,
I have a form with the following input fields
[syntax=php]<input type=text id='Name'>
<input radio value = 'AND' >
<input radio value = 'OR">
<input type =text id='year'>
<input type =text id='month'>
<input type=text id='day'>[/syntax]
then I have database with the following table
table - individuals
field1 - first name
field2 - last name
field3 - middle name
field4 - birth day
field5 - date died
The objective to be able to return results based opon a search of the following criterias
by a Name - whether first or middle or last
by a Year
by a Day
by a month
by a combination of both name and date
by any combinations of year month date
my question concerns building the mysql query...in building these queries what expressions can be used that will best achieve the objectives as the queries are complex and I am uunable to determine a direction to take in achieving this...
any advice or if there is any examples will be helpful thanks
Kendall
[quote]
I learn by expression...
[/quote][/quote]
Posted: Mon Aug 11, 2003 9:12 pm
by JAM
I might be way of in the understanding department... (4am, again)
Code: Select all
select
*
from
table
where
(field1 like '%name%' or field2 like '%name%' or field3 like '%name%') or
(field5 = 'year') or
(field4 = 'day') or
(fieldX = month) or
(
(field1 like '%name%' or field2 like '%name%' or field3 like '%name%') $formandor
field5 = 'year'
) or
(
(field5 = 'year') or
(field4 = 'day') or
(fieldX = 'month')
)
a) The 'year','day' etc of course needs to be replaced the your form's $_POST data, the same with $formandor that is the same form's AND/OR options boxes.
b) You didn't give fieldname for month, so I named it fieldX.
c) I tried to build it like in your later part of message.
d) But i think that you need to map up different queries basen on your forms output (If both a name and month is entered, what happens then? Didn't fall in under your criterias...)
e) I'm not suprised if anyone has better suggestions...
Good luck tho, hopa I wasn't floating away totally here...
Complex Search Expressions (performing search queries)
Posted: Tue Aug 12, 2003 8:10 am
by kendall
JAM,
thanks for your response...Yes you are on the rite track...BUT I didnt think it was capable to have a query string that long with the 'OR' command. I tried some stuff but i kept getting SYNTAX ERRORS.
conering the year-month-date situation....
I have a date column so if i had to query a month, date or year i will have to do some extract() or substring() (hence y i think i may be running into errors i guess)
the closes thing i came up with was this
[syntax=php]<?php
$query = "SELECT * FROM quesnel_individuals WHERE %s %s %s ORDER BY FName, Born ASC";
if(strstr($NAME," ")) // check if the query is for more than one name type
$query_terms1 = "MATCH(FName, MName, LName) AGAINST('".$NAME."')";
else $query_terms1 = "FName LIKE '%".$NAME."%' OR LName LIKE '%".$NAME."%'";
$query_terms2 = "Born LIKE '%".$DATE."%'";
$query = sprintf($query, $query_terms1, $QUERYTYPE, $query_terms2);
?>[/syntax]
which is not accurate but its close to what i want to accomplish...
Can anyone offer some advice here...thanks
Kendall
Re: Complex Search Expressions (performing search queries)
Posted: Tue Aug 12, 2003 8:14 am
by kendall
JAM,
thanks for your response...Yes you are on the rite track...BUT I didnt think it was capable to have a query string that long with the 'OR' command. I tried some stuff but i kept getting SYNTAX ERRORS.
conering the year-month-date situation....
I have a date column so if i had to query a month, date or year i will have to do some extract() or substring() (hence y i think i may be running into errors i guess)
the closes thing i came up with was this
[syntax=php]<?php
$query = "SELECT * FROM quesnel_individuals WHERE %s %s %s ORDER BY FName, Born ASC";
if(strstr($NAME," ")) // check if the query is for more than one name type
$query_terms1 = "MATCH(FName, MName, LName) AGAINST('".$NAME."')";
else $query_terms1 = "FName LIKE '%".$NAME."%' OR LName LIKE '%".$NAME."%'";
$query_terms2 = "Born LIKE '%".$DATE."%'";
$query = sprintf($query, $query_terms1, $QUERYTYPE, $query_terms2);
?>[/syntax]
which is not accurate but its close to what i want to accomplish...
Can anyone offer some advice here...thanks
Kendall
[quote]Does any one no why my message coding arent being formatted?? im using netscape 7.1 [/quote]
Re: Complex Search Expressions (performing search queries)
Posted: Tue Aug 12, 2003 9:54 am
by JAM
kendall wrote:thanks for your response...Yes you are on the rite track...BUT I didnt think it was capable to have a query string that long with the 'OR' command. I tried some stuff but i kept getting SYNTAX ERRORS.
Yah, I'm not 100% sure that the code submitted is something that would work, alltho I personally think it could (after some tweaking). Have in mind that;
Code: Select all
x or z or y
// is not
(x) or (z or y)
...so you are able to build up some interesting where-clauses.
conering the year-month-date situation....
I have a date column so if i had to query a month, date or year i will have to do some extract() or substring() (hence y i think i may be running into errors i guess)
You could try playing around with MySQL's MONTH(), DAY() etc. functions.
Code: Select all
mysql> SELECT MONTH('datefield');
// where datefield is '1998-02-03'
// returns 2
Does any one no why my message coding arent being formatted?? im using netscape 7.1
You have BBCode enabled (lower left while posting, also see the checkbox under the <textarea>)?
Complex Search Expressions (performing search queries)
Posted: Tue Aug 12, 2003 11:18 am
by kendall
JAm,
well i have been looking into the EXTRACT(YEAR_MONTH FROM column) = $var issue(in the myswl docs i see them being used in SELECT CLAUSES. My use is more for the WHERE CLAUSES) but this practically has me boggled as to how i go about defining when to do a search by year, month, day, year and month, year month day while still testing wheather to seach by Name
my issue here is that im seeing i may have to do 2 separate search query then bring them together but im thinking there is a better way only because im not MYSQL inclined.
Kendall
P.S.
now i seee