Grabbing a snippet from a column?

reidme

I want to grab $x chars from a db field using a LIKE '%$string%' clause, then wrap the exact $string in a CSS class before dispaying the whole thing on a page.

Can I grab $x chars using a query or will I have to select the whole field and use string replacement in PHP? I suppose I will have to use string replacement for the tag wrap. I've seen a few similar string replacement commands, which one/s should I use? Can you give any pointers, warn of a pitfall?
Thank you in advance 😃

cgraz

I don't think you can use mysql to just return 400 characters (or however many characters you want to return). You'd probably need to pull out the entire field, then use substr() to display a certain amount of characters out of that.

Cgraz

reidme

Ok thanks 🙂
I was halfway through asking you how to use the function when i noticed it was a link 😃

reidme

Im trying to grab a snippet of code from db field:

$search="select ID, Heading, Content from Articles ".
		"where Content || Heading || Source like '%$string%' LIMIT 20";
$query = mysql_query($search);
while ($rows=mysql_fetch_array($query))
	{ 
	$ID = $rows["ID"]; $Heading = $rows["Heading"]; $Content = $rows["Content"];
	$length = strlen("$Content");
		if ($length >= ($padding * 2))
		{
		$start = ($length / 2) - $padding;
		$end = $start + ($padding *2);
		$Content = nl2br(substr("$Content", $start, $end));
		}
	echo "<tr><td>".$ID." - ".$Heading." - length = ".$length."<br>".$Content."<br></td></tr>";
	}

But I'm getting really weird results!
It just seems to choose one or another result for full word search strings: 'test', 'cheese' - and goes crazy when searching for 'a' 😃
Can anyone see what I'm doing wrong?

cgraz

I'm not sure I understand what is going wrong. Can you be more specific?

Cgraz

reidme

It 'seems' to be grabbing the snippet from a starting place inside the cloumn for the first row returned, then it just displays the full content of the field for every other row.
It is also returning search results that don't match.

cgraz

It 'seems' to be grabbing the snippet from a starting place inside the cloumn for the first row returned

Are you referring to your Content field? You wanted to display x amount of characters, right?

So you're saying if you had this in your database under Content:

"See Forum Rules (below) for
more information about what
codes you are allowed to use
in your posts."

It may return something like

"or more information abo"

?? If that's the problem, I'd say it's your variables that you're using with sub_str(), $start and end. If you want to grab the first 400 characters, you'd use sub_str($string, 0, 400)

I'm guessing you would never want to start at character 30 for example, your start variable would always be 0. So then why are you defining $start like this:

$start = ($length / 2) - $padding;

It's hard to help you exactly cause I don't know what you're tryign to do nor have I seen your site and you also don't give the value for $padding in the code you posted, but that doesn't matter, you want to get $start to equal 0, and $end to equal how many characters you want to display (IE: 400). Most news article sites have something liekt his, if the full article is (taken from mysql.com)

As of MySQL Version 3.23.12, you can give hints about which index MySQL should use when retrieving information from a table. This is useful if EXPLAIN shows that MySQL is using the wrong index from the list of possible indexes. By specifying USE INDEX (key_list), you can tell MySQL to use only one of the possible indexes to find rows in the table. The alternative syntax IGNORE INDEX (key_list) can be used to tell MySQL to not use some particular index. USE/IGNORE KEY are synonyms for USE/IGNORE INDEX.

Now that's too long. So what they do is display a portion of it (IE first 400 characters), then have a link that says read full article. The portion would look something like this:

(assuming thats 400 characters long). The sub_str function for that would look something like

$portion = substr($string, 0, 400);
echo $portion " . . . <a href=full_story.php?id=" . $ROWS["id"] . "READ MORE</a>\n";

Most news site show (I think) the same amount of characters, so it would always be 0, 400 -- so I don't think it would be necessary to even do all those calculations, but it really depends on what you're doing.

If the calculations are that necessary, then calculate them, then echo them so you know what sub_str is calling. Then, adjust your calculations; when they echo correctly, there's no need to echo them anymore and your code will be working properly.

IF you type 'a' in the search, ofcourse your results won't match. If you type web at yahoo, you won't get the best results assuming you were searching for web developers in oregon.

Instead of one word, search for more than one.

Also, looking at your query, I'm guessing you have a search field for content, heading, and source-- can the user only search by one of those, not all 3?

If they could search by all 3, your final query would look something like

SELECT ID, Heading, Content FROM articles WHERE Content LIKE '%$string%' AND Source LIKE '%$string%'  AND Heading LIKE '%$string%' LIMIT 0,20

and ofcourse you'd have to build that dynamically to check what fields they filled in, but if they searched for all 3, that's what your final query would look like. So lets say they filled out all 3. Your query checks for one or the other. Youd need to use AND. That may be why your search results don't match, but you didn't go into much detail on that so that's just a possibility.

Hope this helps, that was a lot of typing.

Cgraz

reidme

First off, thank you very much for all the time and effort you have put into helping me. I hope you will get reward from the fact that you have helped, because you have.
Thank you for showing me how to structure the SQL query, that has helped a great deal.
Here is what I am trying to do:
I have a table full of articles, each article having the following fields:
ID / Cat / Heading / Content / Source / Date.
I want to offer a search of the Articles table on the Heading / Content / Source fileds. If the search string is contained in either the Source or the Heading field then I want the results page to display the ID - Heading and say $snippet chars of the content, so that the user can have a quick scan of the article and see if it was what they were looking for. If the $string is contained in the Content field, then I want the results page to show the ID - Heading and again $snippet chars of the Content. But, I want the snippet of text to start $padding chars before the matched search $string and $padding chars after the matched search $string. I also want the exact matched $string$ to be enclosed in a class="$matched-string-class" CSS tag.

After taking a day off and looking at my code afresh I can see that I was missing something. My code is just finding the halfway point in the string: $start = ($length \ 2), then echoing ($padding*2) chars after that. That's wrong.

I'm not really sure how I would go about indexing a certain point ($string) in a string and grabbing $padding chars either side, though I can 'theoretically' see how explode() could be used to break the string into an array, reference the appropriate index point and then implode() it back into a string ready to be fed through a funtion that checks for the $string and encloses it in a CSS tag.

I would be interested in your thoughts, but wouldn't be offended if you threw the towel in 😃
Kind Regardless,
Reid.

cgraz

Ok for the first part, you could do something like this

-- form --

Heading: <input type="text" name="heading"><br>
Content: <input type="text" name="content"><br>
Source Fields: <input type="text" name="source">

-- end of form --

<?


   $query = "SELECT * FROM articles";

if ($heading) {
   $query .= " WHERE Heading LIKE '%$heading%'";
}

if ($content) {
   if ($query == 'SELECT * FROM articles') {
       $query .= " WHERE Content LIKE '%$content%'";
   } else {
       $query .= " AND Content LIKE '%$content%'";
   }
}

   / just show first 400 characters of article
   while ($row = mysql_fetch_array($query)) {
      $content = substr($row["Content"], 0, 400);

  print "ID: " . $row["ID"] . "<br>\n";
  print "Heading " . $row["Heading"] . "<br>\n";
  print $content . "<br><br>\n\n";
   }

?>

Now I did some searching at php.net and have an idea, although you'll have to work with it to get it to work the way you want.

http://www.php.net/manual/en/function.strpos.php

That function will find the first occurence of a string. What you could do is select from the database, then do something like

$string_found = strpos($row["Content"], $search_string);

$padding = "200";

if ($string_found >= $padding) {
   $start = $string_found - $padding;
} else {
   $start = 0;
}

// You said you wanted it to end $padding characters after
// so

$content = substr($row["content"], $start, $padding);

Something to that effect should work for you. You may have to play around with it as I haven't tested it, but I'm pretty sure the logic will work.

Cgraz

reidme

😃
Excuse the short reply, but I want to get at it.

cgraz

No problem, let me know how it goes.

reidme

Okay, getting somewhere now, thanks to your help 😃
You reap what you sow, you will be rewarded for your efforts, but I doubt it will be me 😃

$sql = mysql_query("Select ID, Content, Heading from Articles where Content LIKE '%$string%'");
while ($query = mysql_fetch_array($sql))
	{
	$ID = $query["ID"]; $Heading = $query["Heading"]; $Content = $query["Content"];
	//$Content = "one two three four five six seven eight nine ten";
	$str_len = strlen($string);
	$con_len = strlen($Content);
	$str_pos = strpos($Content, $string);
	$pad = 20;
		if ($con_len >= (($pad * 2) + str_len))
		{
		$start = $str_pos - $pad;
		$end = ($str_pos + $str_len) + $pad;
		$Content = substr($Content, $start, $end);
		echo "con_len: ".$con_len." | str_pos: ".$str_pos." | start: ".$start." | end: ".$end."\n<br>";
		}
	echo $ID." - ".$Heading."\n<br>".$Content."\n<br><br>";
	}

To see the results go here:
http://reidme.info/phpdev/test.php?string=love

The substr() seems to be having trouble with cutting off the text at the defined variables $start, $end ?
Also sometimes the start will show '-20', how can this happen when "if ($con_len >= (($pad * 2) + str_len))" is supposed to let it pass the loop?
Not really sure what to do now.

cgraz

Yeah, glad to see you're getting there.


    if ($con_len >= (($pad * 2) + str_len))
    {
    $start = $str_pos - $pad;
    $end = ($str_pos + $str_len) + $pad;
    $Content = substr($Content, $start, $end);
    echo "con_len: ".$con_len." | str_pos: ".$str_pos." | start: ".$start." | end: ".$end."\n<br>";
    }

I understand start. You want to get 20 characters before the actual string appears. I also understand end, you want to get the strings position, add the length of the string to it, then add the padding to that so you get 20 characters from the end of the string forward.

Now, if you're getting -20, seeing as though -20 is your smallest number, and your padding is set to 20, you're subtracting 20 from 0. If you look at the results that have -20, the strpos has no value. (something you'll want to look into and fix; I can't tell from my side but if you take a look I'm sure it would be easy to figure out).

But there's one where you're getting a -9, and the start is 11. It's successfully subtracting 20 like it should, but your if statement should basically make sure that your str_pos is greater than 20. If it's not, then your start will be 0. You don't have that in your code anywhere.

lets use an example:


$str_pos = "40";
$padding = "20";

if ($str_pos > $padding) { // if 40 is greater than 20
    $start = $str_pos - padding; // $start will now equal 20
} else {
    $start = "0";
}

Now lets take another possibility, where str_pos is less than the padding


$str_pos = "9";
$paddign = "20";

if ($str_pos > $padding) { // if 9 is greater than 20 (returns false)
    $start = $str_pos - padding; // this won't be executed
} else { // if 9 is not greater than 20 (or if 9 is less than 20)
    $start = "0"; // start will equal 0 in this example
}

I'm not exactly sure what this

if ($con_len >= (($pad * 2) + str_len))

is supposed to do, but simply change your code to run an if statement like the above example and it should work no problem. That should eliminate all negative numbers as a starting value.

Cgraz

reidme

Hmmmm, I've had enough of this 😃
I'm gonna crack on with the search functionality and just display heading for now with the matched string highlighted in the full article if they click on the headling link.
substr() is either getting misled by something in the Content or it's gone Pete Tong.
str_pos wasn't returning a few values either, which is beyond me. I don't want to stay on this problem anymore.
Thank you for your help cgraz, you da man! 😃