PHP / Mysql - site search problem

[illumi]

Hello, i have a dilemma with my search feature on my site. Everything works ok, but I can't figure out how to show the search result in a excerpt of the database info. When i perform the search the whole text from the database field shows up as result.

 

I know that i can use substr($result['field']0, 100) for example, but i don't wanna show a static part.

 

So, what i want, is to have it like this:

 

text text text text text keyword text text text text.

 

Beneth is my script today:

 

<?php

 

//Lets start the MySQL Connection.

mysql_connect("localhost", "root", "password") or die(mysql_error());

mysql_select_db("db") or die(mysql_error());

 

//Lets check to see if they have started a search.

if(isset($_GET['s'])){

 

//They have started a search. Lets see if they have any keywords.

if($_GET['s'] == " " OR $_GET['s'] == ""){

echo "<form method=get action=search.php><input type=text name=s><input type=submit value=\"Search\"></form><br><hr>Inget sökord angivet";

echo "</td></table></div>";

include 'bottom.htm';

//They have no keywords.

die;

}

 

//They have started a search so lets search!

echo "<form method=get action=search.php><input type=text name=s value=\"".$_GET['s']."\"><input type=submit value=\"Search\"></form>";

echo "<title>My Search - Searching for \"".$_GET['s']."\"</title>Searching for \"".$_GET['s']."\"<br><hr><BR>";

$q = mysql_query("SELECT * FROM `tbl` WHERE `title` LIKE '%$_GET%' OR `conts` LIKE '%$_GET%'") or die(mysql_error());

 

//Lets check to see if there are any results.

if(!is_array(mysql_fetch_array($q))){

 

//There are no results.

echo "<br><BR>No results found";

}else{

 

//There are some results! Lets show the user the results.

$q = mysql_query("SELECT * FROM `tbl` WHERE `title` LIKE '%$_GET%' OR `conts` LIKE '%$_GET%'") or die(mysql_error());

while($r = mysql_fetch_array($q)){

echo "<a target=_new href=\"".$r['url']."\">".$r['title']."</a><br>

<p>".str_replace($_GET['s'], "<span style=\"color:#FF0000; font-weight: bold;\">".$_GET['s']."</span>", $r['conts'])."</p>";

}}

}else{

 

//They have not started a search.

echo "<form method=get action=search.php><input type=text name=s><input type=submit value=\"Search\"></form><br><hr><br>";

echo "No Search terms set.";

}

?>

 

 

I know the solution to this is to use the strpos and strlen, like this:

 

<?php

 

// Number of chars to display before and after the keyword

$lettercount = 50;

 

$keyword = "$_GET";

 

//.....

// The text has been retrieved from the database and stored as $test

$test = "lorem ipsum sit dolor lorem ipsum sit dolor lorem ipsum sit dolor lorem ipsum sit dolor lorem ipsum sit dolor apple lorem ipsum sit dolor lorem ipsum sit dolor lorem ipsum sit dolor lorem ipsum sit dolor lorem ipsum sit dolor ";

 

// The position of the keyword within our text

$iPos = strpos($test, $keyword);

 

// The string we're going to output

$strDisplay = " ... ";

$strDisplay .= substr($test,$iPos-$lettercount,$lettercount * 2 + strlen($keyword));

$strDisplay .= " ... ";

$strDisplay = str_replace($keyword, "<b>$keyword</b>",$strDisplay);

 

 

//output the result

echo $strDisplay;

?>

 

 

BUT! I can't fuse those two toghether, and make it work :/ Also, I'm far from good with php. So does anyone see the problem, and can guide me through this matter?

 

Best regards.

[phpSensei]

Its really hard to read your code, use the tags.

<?php

//Lets start the MySQL Connection.
mysql_connect("localhost", "root", "password") or die(mysql_error());
mysql_select_db("db") or die(mysql_error());

//Lets check to see if they have started a search.
if(isset($_GET['s'])){

//They have started a search. Lets see if they have any keywords.
if($_GET['s'] == " " OR $_GET['s'] == ""){
echo "<form method=get action=search.php><input type=text name=s><input type=submit value=\"Search\"></form><br><hr>Inget sökord angivet";
echo "</td></table></div>";
include 'bottom.htm';
//They have no keywords.
die;
}

//They have started a search so lets search!
echo "<form method=get action=search.php><input type=text name=s value=\"".$_GET['s']."\"><input type=submit value=\"Search\"></form>";
echo "<title>My Search - Searching for \"".$_GET['s']."\"</title>Searching for \"".$_GET['s']."\"<br><hr><BR>";
$q = mysql_query("SELECT * FROM `tbl` WHERE `title` LIKE '%$_GET[s]%' OR `conts` LIKE '%$_GET[s]%'") or die(mysql_error());

//Lets check to see if there are any results.
if(!is_array(mysql_fetch_array($q))){

//There are no results.
echo "<br><BR>No results found";
}else{

//There are some results! Lets show the user the results.
$q = mysql_query("SELECT * FROM `tbl` WHERE `title` LIKE '%$_GET[s]%' OR `conts` LIKE '%$_GET[s]%'") or die(mysql_error());
while($r = mysql_fetch_array($q)){
echo "<a target=_new href=\"".$r['url']."\">".$r['title']."</a><br>
<p>".str_replace($_GET['s'], "<span style=\"color:#FF0000; font-weight: bold;\">".$_GET['s']."</span>", $r['conts'])."</p>";
}}
}else{

//They have not started a search.
echo "<form method=get action=search.php><input type=text name=s><input type=submit value=\"Search\"></form><br><hr><br>";
echo "No Search terms set.";
}
?>


I know the solution to this is to use the strpos and strlen, like this:

<?php

// Number of chars to display before and after the keyword
$lettercount = 50;

$keyword = "$_GET[s]";

//.....
// The text has been retrieved from the database and stored as $test
$test = "lorem ipsum sit dolor lorem ipsum sit dolor lorem ipsum sit dolor lorem ipsum sit dolor lorem ipsum sit dolor apple lorem ipsum sit dolor lorem ipsum sit dolor lorem ipsum sit dolor lorem ipsum sit dolor lorem ipsum sit dolor ";

// The position of the keyword within our text
$iPos = strpos($test, $keyword);

// The string we're going to output
$strDisplay = " ... ";
$strDisplay .= substr($test,$iPos-$lettercount,$lettercount * 2 + strlen($keyword));
$strDisplay .= " ... ";
$strDisplay = str_replace($keyword, "<b>$keyword</b>",$strDisplay);


//output the result
echo $strDisplay;
?>

 

[lemmin]

$found = substr($result['field'], strpos($result['field'], $searchstring)-50, 100)

 

There is no efficient way of getting MySQL to return only a portion of the field so you have to use php to do it.

[corbin]

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr

[illumi]

Thanks!

 

But now I'm standing for the problem that when the keyword is in the beginning of the text to be searched, I get the last part of the text showing. Any suggestions?

[illumi]

Anyone with a solution to my last problem? Going to sleep now, so hoping for a reply in the morning

[corbin]

You could use POSITION in mysql in combination with SUBSTR to get the X chars to the left and the X chars to the right of the search string.

[Jessica]

You can use a combination of substr and strpos.