MySQL query rows that contain...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

MySQL query rows that contain...

Post 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?
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You want an IN() clause.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You're comparing an integer to an array. Image
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Have you echoed $query?
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post 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());).
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I said $query; I meant $query.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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().
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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

Code: Select all

print $query;
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)"; 



User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

also you havent used single quotes in the array key used within the query

$_GET['searchField']
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It would suggest $_GET['searchQuery'] was an empty string.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Use isset() or empty() to check, don't just throw them into a conditional.
Post Reply