MYSQLi Select - PHP echo every query term occurrence within row

Question

I have a table called pages that stores the id, url address, title, and content of webpages on my site. Here's an example of the table:

 ID |         address       |  title  |                      content               |
------------------------------------------------------------------------------------
 1  | www.example.com/page1 | Page 1  | The quick dog jumps over the lazy dog.     |
 2  | www.example.com/page2 | Page 2  | The best thing about morning is breakfast. |
 3  | www.example.com/page3 | Page 3  | Hotdogs are great ballpark food.           |

I would like to SELECT all occurrences of a query term, and echo them in PHP. For example, if I want to display search results for the word "dog" my SELECT statement would look like this:

SELECT * FROM pages WHERE (`content` LIKE '%dog%')

and my full PHP statement with the search term = $query looks like this:

if ($result = $mysqli->query("SELECT * FROM pages WHERE (`content` LIKE '%".$query."%')")) { 
  // if one or more rows are returned do following
  while($results = $result->fetch_array(MYSQLI_ASSOC)){
// $results = mysql_fetch_array($raw_results) puts data from database into array, while it's valid it does the loop
  $content = $results['content'];
  $contentItems = $results['contentSearch'];

  // Count number of times term in content
  $count = substr_count($contentItems, $originalQuery);



  $content = substr($content,strpos($content,$firstTerm)-25,160);
  $content = (strlen($content) > 55 ? '...'.$content.'...' : substr($results['description'],0,160).'...');

  foreach($querySplit as $term){
    $content = str_ireplace($term, '<strong>'.$term.'</strong>', $content);
  }
  // highlight search terms

  $chars = array("\'", "&amp;");
  $charReplace = array("'", "&");
  $content = str_replace($chars,$charReplace,$content);

  // set $image if not empty
  $image = (!empty($results['image']) ? 'http://www.example.com/'.$results['image'] : '');


  /* ------------------
  ------ echo statement
  --------------------*/
  echo '
  <li class="media">';
// if image is not empty
if(!empty($image)): 
echo '<a class="pull-left span2 hidden-phone" href="http://www.example.com/'.$results['address'].'"> <img class="media-object thumbnail" src="'.$image.'" alt="'.$results['description'].'"/> </a>';
endif;
echo '
<div class="media-body">
      <h4 class="media-heading"><a href="http://www.example.com/'.$results['address'].'">'.htmlspecialchars_decode($results['title']).'</a></h4>
      <p class="result-address"><small><a href="http://www.example.com/'.$results['address'].'">http://www.example.com/'.$results['address'].'</a></small></p>
      ';
      /*if(!empty($image)): 
      echo '<a class="visible-phone" href="'.$results['address'].'"> <img class="thumbnail phone-search-thumb" src="'.$image.'" alt="'.$results['description'].'"/> </a>';
      endif;*/
      echo '
      <p class="result-content">'.$content.'</p>
    </div>
</li>
  ';
  /* ------------------
  ---end echo statement
  --------------------*/


} $result->close();
}

This PHP and SELECT statement returns two results:

The quick dog jumps over the...
Hot dogs are great...

DESIRED RESULT (please help)

What I would prefer, is for my statement to echo one result for every occurrence of the $query term, even within the same row, as shown here:

The quick dog jumps over the... (from Row 1)
...over the lazy dog. (from Row 1)
Hot dogs are great... (from Row 3)

QUESTION

How can I edit my PHP and/or my SELECT statement so that I may echo one result for every occurrence of the $query term?

Parse the content from database by PHP and see how many dog present in that sentence. — Yogesh Suthar, Jun 5 '13 at 12:53
@YogeshSuthar - can you explain what you mean? I'm not sure I understand. — adamdehaven, Jun 5 '13 at 12:55
Side-note, you should be using a prepared statement when querying the db with user-input. Also, you're mixing OOP and procedural-style mysqli. Is that present only in your example, or literally in your code? — newfurniturey, Jun 5 '13 at 12:55
@newfurniturey - I'm cleaning the user input before it goes to the DB. Left this out for simplicity. — adamdehaven, Jun 5 '13 at 12:58

Sharky · Accepted Answer · 2013-06-10 13:10:45Z

Here is your answer.

<?php

$query="dog";

if ($result = $mysqli->query("SELECT * FROM pages WHERE (`content` LIKE '%".$query."%')"))
{ 
    $inc = 0;
    $mysql_result = array();
    // if one or more rows are returned do following
    while($a_result = $result->fetch_array(MYSQLI_ASSOC))
    {
        $mysql_result[$inc]=$a_result;
        $inc++;
    }

    $chars_to_show_before = 10;  // ...1234567890dog
    $chars_to_show_after = 10;  // dog1234567890...

    foreach($mysql_result as $results)
    {

        $content = str_replace($query,"my_un1qu3_r3pl4c3m3nt_".$query,$results['content']);

        $occurences = explode("my_un1qu3_r3pl4c3m3nt_",$content);

        $position_in_original_content = 0;

        foreach($occurences as $an_occurence)
        {

            if (strpos($an_occurence,$query) !== false)
            { 
                $content=$an_occurence;

                echo '<li class="media">
        <a class="pull-left" href="'.$results['address'].'">
        <img class="media-object thumbnail" src="'.$results['image'].'" style="height:100px !important">
        </a>
        <div class="media-body">
          <h4 class="media-heading"><a href="'.$results['address'].'">'.$results['title'].'</a></h4>
          <p>...'.str_replace($query,'<strong>'.$query.'</strong>', substr($results['content'],max(0,($position_in_original_content-$chars_to_show_before)),strlen(strlen($query))+$chars_to_show_after+$chars_to_show_before)).'...</p>
        </div>
       </li>';
            }

            $position_in_original_content = $position_in_original_content + strlen($an_occurence);

         }
    }
}
else
{
    echo "Sorry, no results found!";
}
?>

working demo here: http://3v4l.org/BTeCU

Forgive me if I'm misunderstanding this, but how would I initialize my results using my existing mysqli query so that the results are $mysql_result[0]['address']="www.example.com/page1"; ? — adamdehaven, Jun 10 '13 at 12:27
@AdamD see updated answer (havent tested this but it should be ok) :) — Sharky, Jun 10 '13 at 13:11
It partially works with my code, but if the user has a compound term, like "dog park", it returns no results. I'm playing with it... — adamdehaven, Jun 10 '13 at 15:11
@AdamD make sure DB returns results on that occasion. do a echo "<pre>".print_r($mysql_result,true)."</pre>"; after the while { ... } to see if you actually getting data from DB — Sharky, Jun 10 '13 at 15:27

furas · Answer 2 · 2013-06-07 18:39:14Z

up vote 0 down vote

as Yogesh Suthar said: use PHP.

here quick and dirty example

$count = substr_count($content, 'dog');

$offset = 0;

for( $i = 0 ; $i < $count ; $i++ ) {

    echo $content; // put you code here with strpos($content,$query,$offset)

    $offset = strpos($content, 'dog', $offset) + strlen('dog');
}

Edit:

I add $offset

Edit:

while($results = $result->fetch_array(MYSQLI_ASSOC)){

    $content = $results['content'];

    $full_content = $content;

    $contentItems = $results['contentSearch'];

    // Count number of times term in content
    $count = substr_count($contentItems, $full_content);

    $offset = 0;

    for( $i = 0 ; $i < $count ; $i++ ) {

        $content = substr($full_content, strpos($full_content, $firstTerm, $offset )-25,160);


        // rest of your code

        /* ------------------
        ---end echo statement
        --------------------*/

        $offset = strpos($full_content, 'dog', $offset) + strlen('dog');
    }
}

$result->close();

edited Jun 7 '13 at 18:39

answered Jun 7 '13 at 14:23

furas
9,8631821

This just shows the same result with highlight over and over and over for the number = $count. I need to move to the next occurrence of the term in the for each. – adamdehaven Jun 7 '13 at 15:53

You can use 3th argument in strpos to search next 'dog' in string. – furas Jun 7 '13 at 15:58

See example now - I add $offset. Use $offset as 3th argument in strpos() in your code. – furas Jun 7 '13 at 16:05

With the updated code above, $offset is not actually doing anything, is it? – adamdehaven Jun 7 '13 at 16:20

There is strpos($content,$query) in your code. It seach first dog and always start from begining of $content. Use 3th argument in strpos() to change it - use strpos($content,$query,$offset). And use my $offset = ... to calculate new offset. – furas Jun 7 '13 at 16:28

| show 2 more comments

asked	1 year ago
viewed	458 times
active	1 year ago

current community

your communities

more stack exchange communities

MYSQLi Select - PHP echo every query term occurrence within row

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged php select mysqli or ask your own question.

Hot Network Questions

current community

your communities

more stack exchange communities

MYSQLi Select - PHP echo every query term occurrence within row

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged php select mysqli or ask your own question.

Related

Hot Network Questions