MySQL Text field Search Difficulties

:confused;
Hi - I've been trying to set up a recordset in Dreamweaver MX that will
compare 3 user submitted form fields to specific columns in the database along
with 3 hardcoded flags that are also compared to items in the database. The
three user submitted fields are a Experience Type which is matched exactly with
a varchar field, an integer which is matched as >= an integer field and a
keyword which I'm trying to match against a Text field using wildcards. I can
get the search to work but it's not properly filtering by the Text field.

SQL Statement something like:

SELECT * FROM recordset
WHERE WorkExperience = 'formvariable1' AND YearsExperience >= 'formvariable2'
AND ResumeField LIKE '%formvariable3%' AND Flag1 = 'Yes' AND FLAG2 = 'Yes' AND
Flag3 = 'No'

I've also tried creating a fulltext index of the Text field in question and
using a MATCH(Texfield) AGAINST ('variable') type of setup. When used by
itself this works fine. But when I try to filter the recordset against the
additional fields as well it doesn't seem to filter anymore. MY SQL statement
is something like this:

Select * from Recordset
WHERE MATCH('Experience') AGAINST ('keyword') AND WorkExperience =
'formvariable'

The result set I get seems to be treating the AND modifier as an OR. Results
will have the formvariable match, but may or may not have the Keyword match.

Anybody have any ideas? I've been banging my head up against the wall on this
one for several days and I'm not getting anywhere. Thanks in advance!

It may not be your SELECT statement that's at fault, but the variable being
passed from the form. Try echoing the SELECT statement to the screen to see
if it's as you expect e.g. <?php echo $query_Recordset1 ?>


"rapier993" <webforumsuser (AT) macromedia (DOT) com> wrote

:confused;
Hi - I've been trying to set up a recordset in Dreamweaver MX that will
compare 3 user submitted form fields to specific columns in the database
along
with 3 hardcoded flags that are also compared to items in the database.
The
three user submitted fields are a Experience Type which is matched exactly
with
a varchar field, an integer which is matched as >= an integer field and a
keyword which I'm trying to match against a Text field using wildcards. I
can
get the search to work but it's not properly filtering by the Text field.

SQL Statement something like:

SELECT * FROM recordset
WHERE WorkExperience = 'formvariable1' AND YearsExperience >=
'formvariable2'
AND ResumeField LIKE '%formvariable3%' AND Flag1 = 'Yes' AND FLAG2 = 'Yes'
AND
Flag3 = 'No'

I've also tried creating a fulltext index of the Text field in question
and
using a MATCH(Texfield) AGAINST ('variable') type of setup. When used by
itself this works fine. But when I try to filter the recordset against
the
additional fields as well it doesn't seem to filter anymore. MY SQL
statement
is something like this:

Select * from Recordset
WHERE MATCH('Experience') AGAINST ('keyword') AND WorkExperience =
'formvariable'

The result set I get seems to be treating the AND modifier as an OR.
Results
will have the formvariable match, but may or may not have the Keyword
match.

Anybody have any ideas? I've been banging my head up against the wall on
this
one for several days and I'm not getting anywhere. Thanks in advance!

It may not be your SELECT statement that's at fault, but the variable being
passed from the form. Try echoing the SELECT statement to the screen to see
if it's as you expect e.g. <?php echo $query_Recordset1 ?>

I thought about this too.. .the results page displays the search terms entered
by the user. The variables seem to be passing just fine.

Do you want to echo the SELECT top the screen then copy paste to here?

"rapier993" <webforumsuser (AT) macromedia (DOT) com> wrote

It may not be your SELECT statement that's at fault, but the variable
being
passed from the form. Try echoing the SELECT statement to the screen to
see
if it's as you expect e.g. <?php echo $query_Recordset1 ?

I thought about this too.. .the results page displays the search terms
entered
by the user. The variables seem to be passing just fine.

Here are two samples that I've tried.

SELECT *
FROM registrations
WHERE (WorkExperience LIKE '%keyword%' AND Expertise1 = 'Var1' and YearsExp1

= 'Var2' AND ActivateFlag = 'active' AND procflag = 'reject') OR
(WorkExperience LIKE '%keyword%' AND Expertise2 = 'colname' and YearsExp2 >=

'colname2' AND ActivateFlag = 'active' AND procflag = 'reject') OR
(WorkExperience LIKE '%keyword%' AND Expertise3 = 'colname' and YearsExp3 >=
'colname2' AND ActivateFlag = 'active' AND procflag = 'reject')

I've also been trying to accomplish the same thing with the MATCH/AGAINST
statements. Here's one of my test cases.
Select *
FROM registrations
where MATCH(WorkExperience) AGAINST ('keyword') AND Expertise1 = 'Var1' and
YearsExp1 >= 'Var2' AND ActivateFlag = 'active' AND procflag = 'reject'

Ultimately I need the keyword to match against the WorkExperience field and
get a match against one ouf of three sets of Experience and YearsExp for a
record to be a valid member of the recordset.

Well like I said - Do you want to echo the SELECT top the screen then copy
paste to here?

Showing what's in your code isn't as definitive as what's really happening
at runtime.