CODEWITHSUNDEEP
phpmysql
trainoasis
trainoasis

Reputation: 6720

MySQL - search for a word in a text field, display whole line?

I'm searching for a certain word:

$sql = "select a, b from lala where dudu=1 and b LIKE \"%".$str."%\"";

This works just fine. Now I want to display the whole line where $str was found, or more lines for that matter. How can I do that?

'b' in this case is a column of type text and contains let's say more than 100 lines.
For instance: 

This is some random text, I want
to perform a search on it. 
It has only three lines.

My $str could be "random", and I wanna get the whole line, so in this case
"This is some random text, I want"

Upvotes: 0

Views: 277

Answers (2)

DaveRandom
DaveRandom

Reputation: 88657

This horrible looking piece of SQL will get the first line with an occurrence of $search on it, assuming the lines are new-line seperated:

$search = 'random'; // Sanitize me!

$sql = "
  SELECT a,
  SUBSTR(
    b,
    IF(
      LOCATE('\n', REVERSE(b), LENGTH(b) - LOCATE('".$search."', b)) > 0,
      LENGTH(b) - LOCATE('\n', REVERSE(b), LENGTH(b) - LOCATE('".$search."', b)),
      1
    ),
    IF(
      LOCATE('\n', b, LOCATE('".$search."', b)) > 0,
      LOCATE('\n', b, LOCATE('".$search."', b)) - IF(
        LOCATE('\n', REVERSE(b), LENGTH(b) - LOCATE('".$search."', b)) > 0,
        LENGTH(b) - LOCATE('\n', REVERSE(b), LENGTH(b) - LOCATE('".$search."', b)),
        1
      ),
      LENGTH(b)
    )
  ) AS line
  FROM lala
  WHERE
    dudu = 1
    AND b LIKE '%".$search."%'
";

I can't say that I recommend it though, I suspect it will be slooooooow.

Upvotes: 1

trainoasis
trainoasis

Reputation: 6720

I know what I must do. When going through results, I need another loop for getting the actual lines into php variables.

Thanks for all answers anyway.

while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
    $hostname = $row["hostname"]; 
    $config = $row["config"];

            # searching through $config separately with foreach 
}

Upvotes: 0

Related Questions