Solved

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

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How would I construct this INSERT statement in PDO? 6 33
remote mysql 8 41
$_GET call between URL 3 45
How do I update select listbox after search 2 27
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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.

696 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