1

I'm having trouble with my search script.

Basically, all is fine with the search if the result is found but if there are no matches in the DB(MySQL) then my error doesn't display.. am i missing something? heres the code:

<?php
$term = $_GET['term'];
$sql = mysql_query("select * from search_e where content like '%$term%'");
while ($row = mysql_fetch_array($sql)){ 
$data = $row['content'];
$first_pos = strpos($data,$term);
if ($first_pos !== false) {
                  $output = substr($data,max(0,$first_pos - 100),200 + strlen($term));?>


<div>
<p class="ptitle"><?php echo $row["fn"]; ?></p><hr>
            Brief summary of contents:
            <hr class="hr">
            <p style="padding: 5px;">
        <i>"<?php echo $output; ?>" </i>..
            </p>


</div><br><br>
<?php
}
else  {?>
<div><?php echo "Sorry! No results were found using term: ".$_GET['term']."<br>Try using fewer Keywords"; ?></div>
<?php }?>
<?php
}
//close
    mysql_close();

?>

This may be something simple im doing wrong but i just cant figure it out. Also i know the code is dirty but its how i work.

I was also hoping to implement a little snippet i found browsing the net, which higlights specific words in a phrase. 

function highlight($sString, $aWords) {
    if (!is_array ($aWords) || empty ($aWords) || !is_string ($sString)) {
        return false;
    }

    $sWords = implode ('|', $aWords);
    return preg_replace ('@\b('.$sWords.')\b@si', '<strong style="background-color:yellow">$1</strong>', $sString);
}

Is this possible to work into my script??

Lotus Notes
  • 6,302
  • 7
  • 32
  • 47
Ricki
  • 933
  • 2
  • 15
  • 33
  • 3
    It amazes me on here how many people put $_GET and $_POST values right into their queries.... you should look into some escaping or using prepared statements. – barfoon May 19 '11 at 20:11
  • i have, i know this. this however is my barebones – Ricki May 19 '11 at 20:13
  • 1
    @Barfoon: which is why, in days thankfully gone by, magic_quotes was enabled by default. It kept all the younglings from blowing off their legs. Nowadays, it's thought to be better to let them blow off a limb and learn from the experience. – Marc B May 19 '11 at 20:13
  • http://stackoverflow.com/questions/5735948/is-this-safe-in-terms-of-sql-injection – Ricki May 19 '11 at 20:14
  • When there are no matches is there any output at all? – Jared May 19 '11 at 20:14

2 Answers2

2

If I'm clear about what you're trying to accomplish, I would change it like so:

if(mysql_num_rows($sql) > 0) {
    while ($row = mysql_fetch_array($sql)) { 
        ...
    }
} else {
    echo("No Records!");
}

And barfoon is correct. Protect your web site and backend database from malicious users.

$term = mysql_real_escape_string($_GET['term']);

Edit

For completeness, after looking back over what you posted the reason you are getting no output is because if no matches are found anything inside of the while loop will not be executed, so your if($first_pos !== false) check is meaningless, except as a sort of 'sanity check' for records that did match.

To highlight the words using the function you posted, change:

<i>"<?php echo $output; ?>" </i>

To:

<i>"<?php echo highlight($output, array( $term )); ?>" </i>
Jeff Lambert
  • 24,395
  • 4
  • 69
  • 96
  • ill try this now. and for the record look at the link i provided please. im not stupid. i know what SQL injection is – Ricki May 19 '11 at 20:18
  • ...and make sure to escape your output as well: `` – Wesley Murch May 19 '11 at 20:18
  • 1
    @Ricki: Plenty of smart people out there don't know what SQL Injection is, so please don't take it personally. This is a question/answer help site after all. I'd rather be overly thorough than leave something out. Plus, if people come and look at this question later it's good to be explicit for posterity. – Jeff Lambert May 19 '11 at 20:19
  • @Ricki: You will find that people will sometimes even ***ignore*** the actual relevant content of your question when there is a blatant security hole. I always leave a disclaimer or use a dummy function like `my_sanitizing_function()` to show people that I'm aware of the risks. It's annoying, but it's the way folks are here (at least in my experience). – Wesley Murch May 19 '11 at 20:20
  • i see, so what.. i remove `if($first_pos !== false)` and it should work? – Ricki May 19 '11 at 20:32
  • Not the way you have it posted above. You will need to check whether there were any results returned _before_ you enter the while loop. – Jeff Lambert May 19 '11 at 20:35
  • ok so i used a `if(!$sql)` call and nothing happened. what am i doing wrong? – Ricki May 19 '11 at 20:42
  • 1
    `$sql` will contain a Result Resource from the database, even if no rows were returned. Use `mysql_num_rows` to see if any rows were actually returned. – Jeff Lambert May 19 '11 at 20:43
2

Your logic is flawed:

  1. retrieve all rows in the database containing your search term
  2. loop over those rows:
    2a. retrieve a row
    2b. search the row AGAIN, using PHP, for the search term
    2c. display the content if it's found, or display an error if it's not. Do this for every row

Why have PHP re-search the content, when it's already been filtered by the database? As well, since the database query will not return any of the rows where you content does NOT appear, you'll never see the error message, as your php search will succeed each time.

the flow should basically be like this:

$term = mysql_real_escape_string($_GET['term']);

$sql = "SELECT .... WHERE content LIKE '%$term%'";
$result = mysql_query($sql) or die(mysql_error());

if (mysql_num_rows($result) == 0) {
   echo "Sorry, no results found"
} else {
   while ($row = mysql_fetch_assoc($result)) {
       ... do your output processing here ...
   }
}
Marc B
  • 356,200
  • 43
  • 426
  • 500