I was confused by the syntax of '%{...}%' as I had never seen that before. Researched a little, saw some folks using it, could not find anything official so I set up a little test to see what would happen.
I first run the query using the routine I did above.
I then run it using the original syntax.
My test database looks like this:
`test_table` (`id`, `url`, `title`)
(1, 'test url 1', 'transvestite threesome tea party'),
(2, 'test url 2', 'threesome tea party'),
(3, 'test url 3', 'tea party'),
(4, 'test url 4', 'party'),
(5, 'test url 5', 'tea for me'),
(6, 'test url 6', 'part time for transvestites');
Mo code (at
http://madspiders.com/gfy_mysql_test/test.php) looks like this:
Code:
<?php
echo "start of program<br><br>\n";
//error_reporting(0);
echo "In new code<br>\n";
require_once('srchdbfunctions.php');
echo "hooking up<br>\n";
$db=hookup('gfy_test');
if($db)
{
echo "got db <br>\n";
}
echo "past connect<br>\n";
$searchstring='transvestite threesome tea party';
echo "searchstring=" . $searchstring . "<br>\n";
$terms=explode(' ',$searchstring);
echo "Constructing conditions<br>\n";
$conditions=array();
for($i=0;$i<count($terms);$i++)
{
if(!empty($terms[$i]))
{
$conditions[$i]="title like '%" . trim($terms[$i]) . "%'";
}
}
echo "Constructing sql str<br>\n";
$sql_str ="select id, url, title from test_table where ";
for($i=0;$i<count($conditions);$i++)
{
if($i>0)
{
$sql_str .=" or ";
}
$sql_str .=$conditions[$i] . " ";
}
echo "sql string=" . $sql_str . "<br>";
$stmnt=$db->query($sql_str);
echo "Sql returned " . $stmnt->rowcount() . " rows<br><br>\n";
while($result=$stmnt->fetch(PDO::FETCH_ASSOC))
{
echo "Rec " . $result['id'] . " Title: " . $result['title'] . " matched <br>\n";
}
echo "<br><br>";
//version 2
?>
Broke this out of html to show how the sql str is being constructed:
$sql_str ="Select id, url, title from test_table ";
$sql_str .="where title LIKE '%{$searchstring}%'";
<br><br>
As soons as we show it in php the brackets {} disappear:
<br><br>
<?php
echo "sql starts back here:<br>\n";
$sql_str ="Select id, url, title from test_table ";
$sql_str .="where title LIKE '%{$searchstring}%'";
echo "sql=" . $sql_str . "<br><br>\n";
echo "when we go to execute that we get back:<br><br>";
$stmnt=$db->query($sql_str);
echo "Sql returned " . $stmnt->rowcount() . " rows<br><br>\n";
while($result=$stmnt->fetch(PDO::FETCH_ASSOC))
{
echo "Rec " . $result['id'] . " Title: " . $result['title'] . " matched " . $result['title'] . "<br>\n";
}
echo "End of program<br>\n";
?>
This is the oputput:
start of program
In new code
hooking up
got db
past connect
searchstring=transvestite threesome tea party
Constructing conditions
Constructing sql str
sql string=select id, url, title from test_table where title like '%transvestite%' or title like '%threesome%' or title like '%tea%' or title like '%party%'
Sql returned 6 rows
Rec 1 Title: transvestite threesome tea party matched
Rec 2 Title: threesome tea party matched
Rec 3 Title: tea party matched
Rec 4 Title: party matched
Rec 5 Title: tea for me matched
Rec 6 Title: part time for transvestites matched
Broke this out of html to show how the sql str is being constructed: $sql_str ="Select id, url, title from test_table "; $sql_str .="where title LIKE '%{$searchstring}%'";
As soons as we show it in php the brackets {} disappear:
sql starts back here:
sql=Select id, url, title from test_table where title LIKE '%transvestite threesome tea party%'
when we go to execute that we get back a:
Sql returned 1 rows
Rec 1 Title: transvestite threesome tea party matched
End of program