Mysql Syntax Error On Search Form

[kaiman]

Hi Again Everyone,

 

I am trying to debug a search form but keep getting the following syntax error:

 

Error: 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 'MATCH (title, keywords) AGAINST('') as score FROM website_search WHERE MATCH (ti' at line 1 with query SELECT count(*) MATCH (title, keywords) AGAINST('') as score FROM website_search WHERE MATCH (title, keywords) AGAINST ('') ORDER BY score DESC

 

Here is the code in question:

 

 

// query the database
$sql = "SELECT count(*) MATCH (title, keywords) AGAINST('". $keyword ."') as score FROM $tbl_name1 WHERE MATCH (title, keywords) AGAINST ('". $keyword ."') ORDER BY score DESC";

 

I am sure it is some small thing I am missing but can't seem to pick it out.

 

Any help or suggestions would be appreciated.

 

Thanks,

 

kaiman

[Jessica]

You're missing a comma between your fields in the field list, and also your $keyword is blank. There might be more, I don't know much about match.

[kaiman]

You're missing a comma between your fields in the field list, and also your $keyword is blank. There might be more, I don't know much about match.

 

The keyword is being introduced through $_GET

 

// search keyword
$keyword = mysql_real_escape_string($_GET['keyword']);

 

I have tried hard coding in a $keyword but still no go. Also have double checked commas between fields without luck.

 

Any other suggestions?

 

Thanks again,

 

kaiman

[Psycho]

Have you echo'd the query to the page to verify what it actually contains?

 

EDIT: Also, what are you trying to accomplish using MATCH() in the select part of the query? My assumption is that MATCH would return either a 0 or 1 based upon whether there was a "match". But, since you are using the same MATCH clause in the WHERE condition all of the records returned would be a match. So, there is no need for that in the WHERE clause - if I am understanding it right.

 

Nevermind, MATCH can be used in the SELECT query to get the "value" of the match. I think the error is that count(*) is an implied GROUP BY, but the MATCH() in the select list is not using a SUM(), COUNT() or other type of GROUP BY condition. So, the two values you are trying to get in the SELECT list are not of the same type - singular and plural

 

Since your query also has an ORDER BY I will assume you really want all the matching records and not the COUNT()?

$query = "SELECT *, MATCH (title, keywords) AGAINST('{$keyword}') as score
	  FROM $tbl_name1
	  WHERE MATCH (title, keywords) AGAINST ('{$keyword}')
	  ORDER BY score DESC";

Edited November 1, 2012 by Psycho

[Barand]

The keyword is being introduced through $_GET

 

Well, absolutely no chance it can be blank then.

 

 

Also have double checked commas between fields without luck.

 

You are having no luck because you put none in there to check.

[Jessica]

See how your error says "AGAINST('')" and your query says AGAINST ('". $keyword ."')?? That means that your $keyword is empty. 

 

The keyword is being introduced through $_GET

 

// search keyword
$keyword = mysql_real_escape_string($_GET['keyword']);

 

I have tried hard coding in a $keyword but still no go. Also have double checked commas between fields without luck.

 

 

Post your updated code.

Edited November 1, 2012 by Jessica

[kaiman]

See how your error says "AGAINST('')" and your query says AGAINST ('". $keyword ."')?? That means that your $keyword is empty.

 

 

 

Post your updated code.

 

Okay, thanks to Psycho's comment I was able to get the db query MATCH to work successfully, but yes Jessica you are correct, the $_GET variable is not being passed correctly.

 

On the form processing page I have this:

 

// search form
// connects to server and selects database
include ("../includes/dbconnect.inc.php");
// table name
$tbl_name1 = "website_search";
// removes magic_quotes_gpc slashes
function stripQuotes($arg) {
if (get_magic_quotes_runtime()) {
 return stripslashes($arg);
} else {
 return $arg;
}
}
// protect against mysql injection
function cleanString($string){
htmlentities(mysql_real_escape_string($string));
return $string;
}
// values sent from form
$keyword = stripQuotes($_GET['keyword']);
$keyword = cleanString($keyword);
header("Location: http://www.mysite.com/search/results/?keyword=$keyword");
exit;

 

Then on the search results page I have this:

 

// connects to server and selects database
include ("../../scripts/includes/dbconnect.inc.php");
// search results

// table name
$tbl_name1 = "website_search";
// search keyword
$keyword = mysql_real_escape_string($_GET['keyword']);

// query the database
$sql = "SELECT *, MATCH (title, keywords) AGAINST('". $keyword ."') as score FROM $tbl_name1 WHERE MATCH (title, keywords) AGAINST ('". $keyword ."') ORDER BY score DESC";
// $query = mysql_query($sql);
$query = mysql_query($sql) or die("Error: ". mysql_error(). " with query ". $sql);
// link
$url = $row['url'];
// display results
$search_result = mysql_num_rows($query);
if ($search_result > 0) {
echo "<h2>Search results for ".$keyword.":</h2>\n";
while ($result = mysql_fetch_array($query)) {
echo (" <p>{$result['title']} ({$result['score']}");
echo " \"$url\"</p>\n";
}
}
// if no results display message
else {
echo "<h2>Sorry, your search for ".$keyword." returned no results</h2>\n";
echo " <p class=\"medium_spacer\">Return to the <a href=\"javascript:history.back()\">previous page</a>.</p>\n";
}

 

Any ideas why the variables aren't being passed?

 

Thanks again,

 

kaiman

[kaiman]

Okay, I got it. I wasn't passing the correct value from the form =P It always something, huh! Sarcasm aside, thanks for all the help.

 

kaiman