Display only part of a MYSQL field

[zentraedi]

Hi,

Wondering if anyone can please help me with this problem?

I'm using a MYSQL mediumtext field to store a bunch of stories and I've also developed a full text search, which works perfectly. However, on searching the database I would like it to display only part the part of the story, the sentence, where the searched keywords lie. I guess kinda visually like Google.

Is this possible with MYSQL of PHP?

Thanks!

[eelixduppy]

Welcome to WebmasterWorld, zentraedi!

This can be done using MySQL's SUBSTRING [dev.mysql.com] function.

Syntax:

SUBSTRING(str,pos,len)

Use:

$query = "SELECT SUBSTRING(col_name,0,200) FROM table";

This will allow you to fetch only a certain amount of text, but then you have the problem of cutting off words.

Here a related thread [webmasterworld.com] on the issue. You can use SUBSTRING to get the text and then cut it off at the last word in that string.

Good luck! :)

[zentraedi]

Thanks for the quick response eelixduppy. Apologies if this is an obvious question, but instead of using a numerical starting point, can I do it by the first instance of a keyword parsed in a search?

I am using something similar to what you suggested, but in php with the implode function:

echo implode(" ", array_slice(preg_split("/\s+/", $text), 0, 200))."... ";

So, if possible, I'd like the '0' to be $keyword.

[eelixduppy]

You can always take the whole string from the database, then use strpos [us3.php.net] to find the first occurrence of the keyword and then use substr [us3.php.net] to extra a certain amount of text after that.

I'm not extremely familiar with all the mysql string functions so there may be a way or returning the location of the keyword as an integer as SUBSTRING takes an int as the position.

Hope this helps ;)

[zentraedi]

Thanks again eelixduppy!

You were right on the first reply and I think I've found an answer to my problem:

SELECT SUBSTRING(yourTextField, LOCATE(yourTextField, 'your search text')-10, 30)
FROM
...

[coopster]

Yeah, there is a standard SQL string operator of

POSITION

that might prove handy here. Here are some threads that shows it's use ...

[webmasterworld.com...]
[webmasterworld.com...]

<added>
Nice work, zentraedi

LOCATE

is a synonym for

POSITION

</added>

[zentraedi]

Thanks coopster,

It that makes perfect sense to me now, but I don't think I've got the syntax quite right as the text isn't displaying...

$query = "SELECT SUBSTRING(sectionContent, LOCATE(sectionContent, '$kewords')-10, 30),
MATCH(sectionContent)
AGAINST ('$keywords') AS score FROM $tableName
WHERE MATCH(sectionContent)
AGAINST ('$keywords') ORDER BY score DESC LIMIT $start, $display";
$result = mysql_query($query);

[eelixduppy]

Try using this to give any mysql errors:

$result = mysql_query($query) [b]or die(mysql_error());[/b]

You may also just not be returning any results.

And thanks coop ;)

[coopster]

What is in $keywords, multiple words? Dump your query to the browser to see what your SQL syntax looks like.

[zentraedi]

Hi coopster,

No errors, in fact I get a confirmed counted result, one record returned. The text doesn't display if I include the substring, but if I change that to SELECT *, instead of SELECT SUBSTRING, the record displays. That's why I thought it may have been a syntax thing.

Here's the code for the section (ps I notcied I misspelled a variable in my previous entry, which is now fixed):

$query = "SELECT SUBSTRING(sectionContent, LOCATE(sectionContent, '$keywords')-10, 30),
MATCH(sectionContent)
AGAINST ('$keywords') AS score FROM $tableName
WHERE MATCH(sectionContent)
AGAINST ('$keywords') ORDER BY score DESC LIMIT $start, $display";
$result = mysql_query($query) or die(mysql_error());

if ($num_records == 0)
{
echo'<p>Your search for <b>'.stripslashes($_GET['q']).'</b> returned 0 results.</p>';
exit;
}

//Display records
echo'<h1>Search Results</h1>
<p>Your search for <b>'.stripslashes($_GET['q']).'</b> returned '.$num_records.' results.
<br />Results are ordered by relevance</p>';

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
//Bolds keyword
$text = preg_replace ( "'($keywords)'si" , "<b id='search'>\\1</b>" , $row[ 'sectionContent' ] );
//Section
echo'<p><b>Section:</b> <a href="display.php?q='.$keywords.'">'.$row['section'].'</a></p>';
//Display text
echo'<p>'.$text.'</p>';
}

[coopster]

I see you refer to these indexes in your fetched $row array ...

$row['sectionContent'] 
$row['section']

... so you are fetching the row indexes by associative reference, but in your query you are not using any aliases. Does this make sense? Can you see your issue?

[zentraedi]

Sorry coopster,

This is where I fess up and mention I'm obviously pretty new to this and I'm not 100% sure what you mean. I do have those two rows you've mentioned which I've indexed together section and sectionContent.

$query = "SELECT section, SUBSTRING(sectionContent, LOCATE(sectionContent, '$keywords')-10, 30) AS sectionContent,
MATCH (section,sectionContent)
AGAINST ('$keywords') AS score FROM $tableName
WHERE MATCH(section,sectionContent)
AGAINST ('$keywords') ORDER BY score DESC LIMIT $start, $display";
$result = mysql_query($query) or die(mysql_error());

[coopster]

Well, for being new, you got the syntax down quick enough, nice work! You have now established the aliases needed for PHP to retrieve the correct indexes from the returned fetch. For example,

$sql = "SELECT myColumn AS renamedColumn FROM myTable";

... is going to return a result set where the associative index in the fetched array is going to be 'renamedColumn'. So, you have figured that part out.

I think you may have your arguments mixed up for the LOCATE function. The substring should be first, then the string. I believe this is what you are looking for ...

SUBSTRING(sectionContent, LOCATE('$keywords', sectionContent)-10, 30) AS sectionContent,

Another possible issue is that perhaps that "minus 10" part of the calculation is throwing things. What happens if the keyword is at the very beginning of the string? For example:

SELECT SUBSTRING('What in the world is wrong here?', LOCATE('What', 'What in the world is wrong here?') - 10) AS myString;

... returns ...

+-----------+ 
¦ myString  ¦ 
+-----------+ 
¦ ong here? ¦ 
+-----------+

... at MySQL version 5.0.

Lastly, I have seen issues when using an ALIAS of the same column name as the column name itself in previous releases of MySQL, so you might also want to try a different column name for the alias.

Lots of things to look at ;)

[zentraedi]

thanks coopster. will let you know how i get on.

[zentraedi]

Still struggling...

I can get get this to work:
$query = "SELECT SUBSTRING(col_name,0,200) FROM table";

And, using php this work's:
echo implode(" ", array_slice(preg_split("/\s+/", $text), 0, 200))."... ";

But, when I try and use the LOCATE/POSITION with the $keywords I get no printed result. In fact if I echo 'display' in the following query I get '0'. But I get '1' returned records in the rest if the query?

$query = "SELECT section, LOCATE('$keywords',sectionContent) AS display FROM $tableName WHERE MATCH(section,sectionContent) AGAINST ('$keywords') LIMIT $start, $display";

I'm unsure of the correct syntax for using strpos/substr.

Any other pointers?

[zentraedi]

ie...

Search Results

Your search for 'Banks' returned 1 results.
Results are ordered by relevance

Section: accounting

0

[coopster]

Yes, some other pointers/observations.

First, let's address this issue:

I'm unsure of the correct syntax for using strpos/substr.

The manual pages for any PHP functions [php.net] are your starting place to verify correct syntax. Each function can be located (there is a little search tool on the top of the page, accessible throughout the entire site) and you will get a full description of the function, it's syntax, etc. There is also a page that tells you how to How to read a function definition [php.net] for any clarification you might need. Don't forget, WebmasterWorld has a PHP Forum [webmasterworld.com] for PHP-specific questions too.

Also, eelixduppy posted a link to the authoritative site for MySQL functions. That is where you can find MySQL-specific syntax and information for the MySQL functions such as SUBSTRING and POSITION.

OK, now back to your code ...

I would dump the query to the browser and also print out your $row data before attempting any type of html display (including that preg_replace). See if you notice anything incorrect.

Lastly, you are searching across two columns in your query, but only trying to LOCATE keywords in only one of those columns. You might need to figure out which column actually contains the keywords before you attempt to SUBSTRING.