Wild Card search not working...

[Phil++]

Hey, I need to create a search engine that searchs through a database but the wild card doesn't work for me:

<?php
   include ('header.php');
   $s = $_POST['search_term'];
   $by = $_POST['by'];
   
   if(!$s && $by) // Check that the details are entered correctly
   { 
      echo '<p>You have not entered the required fields</p>';
      exit();
   }
    //  LIKE '%$searchterm%' 
              $query = "SELECT * FROM products WHERE artist LIKE '% $s %'";
              $result = mysql_query($query) or trigger_error("Query: $query \n<br />Mysql error: 		 " . mysql_error());

              if(mysql_affected_rows() >= 1)
              {
                 while($row = mysql_fetch_array($result))
                 {
                     echo '<p>Your search result for ' .$search_term. '';
                     echo '<blockquote>';
                     echo '<img src="' .$row['pic']. '">';
                     echo '<p>' .$row['desc']. '<br />';
                     echo 'Price: ' .$row['price']. '<br />';
                     echo '</blockquote>';
                  }
                }else{
                  echo '<p>No artist found, did you spell it right?</p>';
                }
     
?>

Any help would be great. Thanks.

[Will Gresham]

Remove the spaces between the %s and the value.

If the user entered 'Google' then the search query would look like:

$query = "SELECT * FROM products WHERE artist LIKE '% Google %'";

This would return values where the products column had an entry of 'something Google something'

[Atli]

Hey.

Also, on line #15 you use the mysql_affected_rows function, where you should be using the mysql_num_rows function.

The mysql_affected_rows function only returns the number or rows affected by INSERT, UPDATE, REPLACE or DELETE queries. SELECT queries have no affect on any rows, and as such, the function doesn't return a valid result for them.

[network18]

if your query is failing try to echo the query and execute it directly in the mysql and post if any error you getting there. or else post the error you echoing in your php

[liamfriel]

Also addslashes(); to your search term somtimes users entering quotes can cause sql to fail.

[Atli]

Also addslashes(); to your search term somtimes users entering quotes can cause sql to fail.

The mysql_real_escape_string function would be better. It does more than just add slashes, it escapes any char that would mess up the query.

It should be used on any data that is to be inserted into a MySQL query, especially user-supplied data. (See SQL Injection)

[liamfriel]

The mysql_real_escape_string function would be better. It does more than just add slashes, it escapes any char that would mess up the query.

It should be used on any data that is to be inserted into a MySQL query, especially user-supplied data. (See SQL Injection)

Thanks - I have spent so long using a custom function(that uses mysql_real_escape_string()) to validate my data I forgot about it.

If anyone is interested:

function make_safe($string) {
    $string = preg_replace('#<!\[CDATA\[.*?\]\]>#s', '', $string);
    $string = strip_tags($string);
    $string = htmlentities($string, ENT_NOQUOTES, 'UTF-8', false);
    $string = stripslashes($string);
    $string = mysql_real_escape_string($string);
    return $string;
}

[liamfriel]

Double post please delete