SQL query that queries MATCH AGAINST on rows that meet a criteria

Hey gang, I have a user table with a column named state_id and I have a bunch of people from multiple states registered and all of their information stored in this table.  Also, when a visitor logs in to the site I have that users state_id in a session variable named $sessioin_state_id.  

The sql query below works, but it searches the entire table and results in all the users who meet the search criteria regardless of their state.  So if someone searches for "Smith" they get all of the Smiths from all of the states.  Now I want to use the users $session_state_id in the query below so that only the Smiths from users state are returned. Is this possible?  I tried adding "state_id=$session_state_id AND " after the WHERE, but I couldn't get it to work.  Any suggestions?
$sql = "SELECT username, first_name, middle_name, last_name FROM users WHERE MATCH(first_name, middle_name, last_name) AGAINST ('%$searchstring%' IN BOOLEAN MODE)";

Open in new window

jbpeakeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roger BaklundCommented:
>> Is this possible?  

Yes.

>> I tried adding "state_id=$session_state_id AND " after the WHERE, but I couldn't get it to work.  Any suggestions?

Did you get an error? Are you sure $session_state_id has a value? Session variables are usually stored in the $_SESSION array. Is the state_id numeric, or is it a character code (a string)?

This should work:

$sql = "SELECT username, first_name, middle_name, last_name FROM users
  WHERE state_id=$session_state_id AND
    MATCH(first_name, middle_name, last_name) AGAINST ('%$searchstring%' IN BOOLEAN MODE)";

To check for errors, do something like this:

$res = mysql_query($sql);
if(!$res) die(mysql_error()."<br>\nQuery: $sql");
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jbpeakeAuthor Commented:
Grrrr... I made a stupid mistake,

Thanks for the quick response cxr you made me realize the mistake.  I had it working when logged in, but it stopped working when I wasn't logged in, as you pointed out, when $session_state_id wasn't defined it wouldn't work.  Easy to fix with an if than statement.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.