MySQL query rows that contain...
Moderator: General Moderators
MySQL query rows that contain...
I'm building a search feature for a database table. I have three elements: Select box containing all the field names in the table of which you'd like to search, a input-text element used for your search terms, and a submit button.
Simple. But I need to know some ways of narrowing down a SQL query.
How do I select all the rows with the field 'X' that contains 'X' keywords?
In the meantime I'm reading the mysql.com reference manual, which in my opinion is a bit confusing.
EDIT: I found FULLTEXT searching with MATCH and AGAINST. This seems to work, only the stop words are bothering my here. I have a field called active and it's value is either "yes" or a random number. "yes" is a listed within the stopwords.
Is there a way to override stopwords?
Simple. But I need to know some ways of narrowing down a SQL query.
How do I select all the rows with the field 'X' that contains 'X' keywords?
In the meantime I'm reading the mysql.com reference manual, which in my opinion is a bit confusing.
EDIT: I found FULLTEXT searching with MATCH and AGAINST. This seems to work, only the stop words are bothering my here. I have a field called active and it's value is either "yes" or a random number. "yes" is a listed within the stopwords.
Is there a way to override stopwords?
Code: Select all
if ($_GET['searchQuery'] && $_GET['searchField'])
{
$searchArray = explode(" ", $GET['searchQuery']);
$query = "SELECT * FROM $table_name WHERE $_GET[searchField] IN(";
for ($i=0; $i <= $searchArray; $i++, $query .= ",")
{
$query .= "$searchArray[$i]";
}
}
else
{
$query = "SELECT * FROM $table_name";
}
$result = mysql_query($query, $connection) or die(mysql_error());
Is there a better way to loop through an array?
Woops. Sorry forgot the count() function.
I have:
And I get:
I have:
Code: Select all
if ($_GET['searchQuery'] && $_GET['searchField'])
{
$searchArray = explode(" ", $GET['searchQuery']);
$query = "SELECT * FROM $table_name WHERE $_GET[searchField] IN($searchArray[0]";
for ($i=1; $i < count($searchArray); $i++)
{
$query .= ",";
$query .= "$searchArray[$i]";
}
$query .= ")";
}
else
{
$query = "SELECT * FROM $table_name";
}
What's up?You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
Umm... I've echoed the results of the query if that's what you mean.
Add to the end:
Then I use this resource to build up a block of html called $table_block. For every row I add a row to this HTML table held within this variable, $table_block. Then once all the looping is done I echo that.
But right now there seems to be an error in my query. So the problem isn't any further then the line of code above ($result = mysql_...die(mysql_error());).
Add to the end:
Code: Select all
$result = mysql_query($query, $connection) or die(mysql_error());
But right now there seems to be an error in my query. So the problem isn't any further then the line of code above ($result = mysql_...die(mysql_error());).
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
printing arrays within double quoted strings require you to use {}
also when you are advised to echo the query, you are being asked to print out the actual query that will be sent to the mysql_query function.
in your case
If the field you are searching on is a text field you will need single quotes around each value IN ('find','me','okay')
also why not just use the implode function or a string replace to construct the IN clause
Code: Select all
$query = "SELECT * FROM $table_name WHERE {$_GET[searchField]}";
in your case
Code: Select all
print $query;
also why not just use the implode function or a string replace to construct the IN clause
Code: Select all
$searchArray = explode(" ", $GET['searchQuery']);
$InClause = "'".implode("','",$searchArray)."'"; //this constructs a string with a start and end ' plus each value is separated with ','
$query = "SELECT * FROM $table_name WHERE {$_GET[searchField]} IN ($InClause)";
Okay so when I use:
I get:
Code: Select all
if ($_GET['searchQuery'] && $_GET['searchField'])
{
$searchArray = explode(" ", $GET['searchQuery']);
$searchClause = "'".implode("','", $searchArray)."'";
$query = "SELECT * FROM $table_name WHERE $_GET[searchField] IN($searchClause)";
echo $query;
}
else
{
$query = "SELECT * FROM $table_name";
}
$result = mysql_query($query, $connection) or die(mysql_error());Why?SELECT * FROM users WHERE username IN('')
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
