Solved

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

Posted on 2009-06-28
2
550 Views
Last Modified: 2013-12-13
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
Comment
Question by:jbpeake
2 Comments
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 500 total points
ID: 24732734
>> 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
 

Author Comment

by:jbpeake
ID: 24732992
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question