hello. i am having a perplexingly paradoxical problem with a simple mysql search engine.
my search engine searches through multiple tables in mysql by using a couple of loops to properly generate mysql SELECT statements for multiple search words, and to return results for the end user.
below is my code, following is my problem:
<?php
//$search is the variable passed by form input...the query.
printf("<h2>Your Search Results for: \"$search\"</h2>");
//stick query words in array
$space = " ";
$search_words = split($space, $search);
$search_num = count($search_words);
//array containing table names
$table = array("main","ilinks");
$table_num = count($table);
//loop through this once for each table
for ($i = 0; $i < $table_num; ++$i)
{
//begin assembling SELECT query
$statement = "\"SELECT tbl, page, title, description FROM $table[$i] WHERE keywords LIKE '%$search_words[0]%' ";
//if multiple searchwords, append $statement
if ($search_num > 1){
for ($idx = 1; $idx < $search_num; ++$idx){
$statement .= " OR keywords LIKE ";
$statement .= "'%$search_words[$idx]%' ";
}
}
//end quote, end query
$statement .= "\"";
//echo query for troubleshooting puropses
echo ($statement);
//connect to mysql, begin search
$db = mysql_pconnect("server", "user", "pass");
mysql_select_db("database", $db);
//result to query
$result = mysql_query($statement, $db);
//if there is a result, run through rows
if($num_of_rows = mysql_num_rows($result)){
while ($row = mysql_fetch_row($result))
//spit out search reuslts, hyperlink, and URL to end user
{
print "<p><a href=http://www.phpbuilder.com/board/archive/index.php/\"$row[0].php3/$row[1].html\">
$row[2]</a><br>$row[3]<br><font color=#57A275>www.mbinet.org/$row[0].php3/$row[1].html</font></p>
";
}
}
//tell me what went wrong
else {echo "Error: " . mysql_error();
}}
//end loop through $table array
?>
okay...that's the code. simple, straightforward. i'm fairly new to this, and spewed out the code in an epiphonal frenzy at 2am, and have been poking at it all day.
now for my problem:
the select statment that is generated using the query modular+building is:
"SELECT tbl, page, title, description FROM main WHERE keywords LIKE '%modular%' OR keywords LIKE '%building%' "
when i submit the assembled SELECT statement through my mysql adminsitration utility (phpMyAdmin), everything goes fine, and i am returned my single expected row.
when i try to use my search engine above to return the same row using the same SELECT statement, i get an this troubleshooting information:
Warning: Supplied argument is not a valid MySQL result resource in /directory/search.php3 on line 346
(around where mysql_num_rows and mysql_fetch row exist)
Error: You have an error in your SQL syntax near '"SELECT tbl, page, title, description FROM main WHERE keywords LIKE '%modular%' ' at line 1
my brain will soon explode. i don't understand why the SELECT statement works fine in phpMyAdmin, and doesn't work in my script.
help!!!
thank you thank you thank you.
-DolphinSnot-
my search engine searches through multiple tables in mysql by using a couple of loops to properly generate mysql SELECT statements for multiple search words, and to return results for the end user.
below is my code, following is my problem:
<?php
//$search is the variable passed by form input...the query.
printf("<h2>Your Search Results for: \"$search\"</h2>");
//stick query words in array
$space = " ";
$search_words = split($space, $search);
$search_num = count($search_words);
//array containing table names
$table = array("main","ilinks");
$table_num = count($table);
//loop through this once for each table
for ($i = 0; $i < $table_num; ++$i)
{
//begin assembling SELECT query
$statement = "\"SELECT tbl, page, title, description FROM $table[$i] WHERE keywords LIKE '%$search_words[0]%' ";
//if multiple searchwords, append $statement
if ($search_num > 1){
for ($idx = 1; $idx < $search_num; ++$idx){
$statement .= " OR keywords LIKE ";
$statement .= "'%$search_words[$idx]%' ";
}
}
//end quote, end query
$statement .= "\"";
//echo query for troubleshooting puropses
echo ($statement);
//connect to mysql, begin search
$db = mysql_pconnect("server", "user", "pass");
mysql_select_db("database", $db);
//result to query
$result = mysql_query($statement, $db);
//if there is a result, run through rows
if($num_of_rows = mysql_num_rows($result)){
while ($row = mysql_fetch_row($result))
//spit out search reuslts, hyperlink, and URL to end user
{
print "<p><a href=http://www.phpbuilder.com/board/archive/index.php/\"$row[0].php3/$row[1].html\">
$row[2]</a><br>$row[3]<br><font color=#57A275>www.mbinet.org/$row[0].php3/$row[1].html</font></p>
";
}
}
//tell me what went wrong
else {echo "Error: " . mysql_error();
}}
//end loop through $table array
?>
okay...that's the code. simple, straightforward. i'm fairly new to this, and spewed out the code in an epiphonal frenzy at 2am, and have been poking at it all day.
now for my problem:
the select statment that is generated using the query modular+building is:
"SELECT tbl, page, title, description FROM main WHERE keywords LIKE '%modular%' OR keywords LIKE '%building%' "
when i submit the assembled SELECT statement through my mysql adminsitration utility (phpMyAdmin), everything goes fine, and i am returned my single expected row.
when i try to use my search engine above to return the same row using the same SELECT statement, i get an this troubleshooting information:
Warning: Supplied argument is not a valid MySQL result resource in /directory/search.php3 on line 346
(around where mysql_num_rows and mysql_fetch row exist)
Error: You have an error in your SQL syntax near '"SELECT tbl, page, title, description FROM main WHERE keywords LIKE '%modular%' ' at line 1
my brain will soon explode. i don't understand why the SELECT statement works fine in phpMyAdmin, and doesn't work in my script.
help!!!
thank you thank you thank you.
-DolphinSnot-