Page 1 of 2
MySQL query rows that contain...
Posted: Tue Apr 03, 2007 4:33 pm
by JellyFish
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?
Posted: Fri Apr 06, 2007 5:32 pm
by JellyFish
How could I select the rows of a database table that contain one or more keys in an array, php or mySQL array?
Posted: Fri Apr 06, 2007 5:52 pm
by feyd
You want an IN() clause.
Posted: Sun Apr 08, 2007 3:46 pm
by JellyFish
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());
It appears to be running slow, as if it were looping more then the length of the array. And it also doesn't display anything...
Is there a better way to loop through an array?
Posted: Sun Apr 08, 2007 3:57 pm
by feyd
You're comparing an integer to an array.

Posted: Sun Apr 08, 2007 4:12 pm
by JellyFish
Woops. Sorry forgot the count() function.
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";
}
And I get:
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
What's up?
Posted: Sun Apr 08, 2007 4:18 pm
by feyd
Have you echoed $query?
Posted: Tue Apr 10, 2007 3:05 pm
by JellyFish
Umm... I've echoed the results of the query if that's what you mean.
Add to the end:
Code: Select all
$result = mysql_query($query, $connection) or die(mysql_error());
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());).
Posted: Tue Apr 10, 2007 3:31 pm
by feyd
I said $query; I meant $query.
Posted: Tue Apr 10, 2007 5:10 pm
by RobertGonzalez
It is just a guess, but I would think it has to do with the comma appended to the end of the list items you are search IN().
Posted: Wed Apr 11, 2007 5:13 am
by mikeq
printing arrays within double quoted strings require you to use {}
Code: Select all
$query = "SELECT * FROM $table_name WHERE {$_GET[searchField]}";
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
$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)";
Posted: Wed Apr 11, 2007 5:24 am
by mikeq
also you havent used single quotes in the array key used within the query
$_GET['searchField']
Posted: Wed Apr 11, 2007 3:36 pm
by JellyFish
Okay so when I use:
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());
I get:
SELECT * FROM users WHERE username IN('')
Why?
Posted: Wed Apr 11, 2007 3:40 pm
by feyd
It would suggest $_GET['searchQuery'] was an empty string.
Posted: Wed Apr 11, 2007 3:55 pm
by RobertGonzalez
Use
isset() or
empty() to check, don't just throw them into a conditional.