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

Posted on 2009-06-28
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

Question by:jbpeake
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 39

Accepted Solution

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

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");

Author Comment

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.

Featured Post

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
The viewer will learn how to count occurrences of each item in an array.
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.

734 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