Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 566
  • Last Modified:

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

0
jbpeake
Asked:
jbpeake
1 Solution
 
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
 
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now