[RESOLVED] Use the LIKE staement

[dcjones]

Hi All.

I am having problems with a SQL statement. What I want to do is produce a statement that can retrieve records that meets a search criteria. I have a search page that contains a form with a text field where the user inserts a number or a text string.

I am using the following SELECT statement.

$currentPage = $_SERVER["PHP_SELF"];

$colname_search = "-1";
if (isset($_POST['search'])) {
  $colname_search = (get_magic_quotes_gpc()) ? $_POST['search'] : addslashes($_POST['search']);
}
$colname1_search = "-1";
if (isset($_POST['search'])) {
  $colname1_search = (get_magic_quotes_gpc()) ? $_POST['search'] : addslashes($_POST['search']);
}
$colname2_search = "-1";
if (isset($_POST['search'])) {
  $colname2_search = (get_magic_quotes_gpc()) ? $_POST['search'] : addslashes($_POST['search']);
}
$colname3_search = "-1";
if (isset($_POST['search'])) {
  $colname3_search = (get_magic_quotes_gpc()) ? $_POST['search'] : addslashes($_POST['search']);
}
$colname4_search = "-1";
if (isset($_POST['search'])) {
  $colname4_search = (get_magic_quotes_gpc()) ? $_POST['search'] : addslashes($_POST['search']);
}
mysql_select_db($database_Chandos, $Chandos);
$query_search = sprintf("SELECT * FROM chandos_books WHERE author_name LIKE  %s OR isbn_paperback = %s OR isbn_hardback = %s OR isbn_paperback_13 = %s OR isbn_hardback_13 = %s", GetSQLValueString($colname_search, "text"),GetSQLValueString($colname1_search, "int"),GetSQLValueString($colname2_search, "int"),GetSQLValueString($colname3_search, "int"),GetSQLValueString($colname4_search, "int"));
$search = mysql_query($query_search, $Chandos) or die(mysql_error());
$row_search = mysql_fetch_assoc($search);
$totalRows_search = mysql_num_rows($search);

The area I am having problems with is the SELECT statement.

"SELECT * FROM chandos_books WHERE author_name LIKE  %s OR isbn_paperback = %s OR isbn_hardback = %s OR isbn_paperback_13 = %s OR isbn_hardback_13 = %s",

Am I using the LIKE statement correctly.

[laserlight]

LIKE is useful when you use it with % to match zero or more characters. In your code, it is used as if it were just an = comparison.

You probably want to write: author_name LIKE '%%%s%%'. Likewise, you need to quote your other strings. Note that [man]mysql_real_escape_string/man should be used instead of addslashes().

[dcjones]

Hi LaserLight,

Thanks for your reply.

I have changes the SELECT statement to:

"SELECT * FROM chandos_books WHERE author_name LIKE '%%%s%%' OR book_title LIKE '%%%s%%' OR isbn_hardback = '%s'  OR isbn_paperback = '%s'"

but when the script is run I get an error.

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'wood'%' OR book_title LIKE '%0%' OR isbn_hardback = '0' OR isb

Many thanks if you can advise please.

[laserlight]

Show your sample input.

[dcjones]

Hi LaserLight.

The input could be a string, example "john woods" or a book title, example "SQL for beginners" or it could be the isbn number of a book, example "9992158107".

many thanks

[laserlight]

The input could be a string, example "john woods" or a book title, example "SQL for beginners" or it could be the isbn number of a book, example "9992158107".

Yes, of course, but I am asking for the specific input that you tested.

It looks like somehow the escaping was not done properly. What does GetSQLValueString() do? Have you changed to use mysql_real_escape_string()?

[dcjones]

Hi LaserLight.

The complete script is

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    

    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$currentPage = $_SERVER["PHP_SELF"];

$colname_search = "-1";
if (isset($_POST['search'])) {
  $colname_search = (get_magic_quotes_gpc()) ? $_POST['search'] : addslashes($_POST['search']);
}
$colname1_search = "-1";
if (isset($_POST['search'])) {
  $colname1_search = (get_magic_quotes_gpc()) ? $_POST['search'] : addslashes($_POST['search']);
}
$colname2_search = "-1";
if (isset($_POST['search'])) {
  $colname2_search = (get_magic_quotes_gpc()) ? $_POST['search'] : addslashes($_POST['search']);
}
$colname3_search = "-1";
if (isset($_POST['search'])) {
  $colname3_search = (get_magic_quotes_gpc()) ? $_POST['search'] : addslashes($_POST['search']);
}
$colname4_search = "-1";
if (isset($_POST['search'])) {
  $colname4_search = (get_magic_quotes_gpc()) ? $_POST['search'] : addslashes($_POST['search']);
}
mysql_select_db($database_Chandos, $Chandos);
$query_search = sprintf("SELECT * FROM chandos_books WHERE author_name LIKE '%%%s%%' OR book_title LIKE '%%%s%%' OR isbn_hardback = '%s'  OR isbn_paperback = '%s'", GetSQLValueString($colname_search, "text"),GetSQLValueString($colname1_search, "int"),GetSQLValueString($colname2_search, "int"),GetSQLValueString($colname3_search, "int"),GetSQLValueString($colname4_search, "int"));
$search = mysql_query($query_search, $Chandos) or die(mysql_error());
$row_search = mysql_fetch_assoc($search);
$totalRows_search = mysql_num_rows($search);

The test data I used was an author name "wood".

[laserlight]

Ah, I see. GetSQLValueString() is intended to perform the escaping and quoting. This means that you can just write:

if (isset($_POST['search'])) {
    $colname_search = GetSQLValueString($_POST['search'], "text");
}

Then later on you should not call the function again.

By the way, you are using GetSQLValueString() with "int" for the second argument for book_title. You should use "text" instead.

[dcjones]

Hi LaserLight.

Thanks to your help yet again you have resolved the problem.

I can now search on all the areas I need.

I do learn from you and for that I thank you.

[dcjones]

Hi LaserLight,

Sorry to come back again but I had this working but now it is not and I can not see what I have done. This is the script

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    

    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$currentPage = $_SERVER["PHP_SELF"];


$colname_search = "-1";
if (isset($_POST['search'])) {
  $colname_search = (get_magic_quotes_gpc()) ? $_POST['search'] : addslashes($_POST['search']);
}
$colname1_search = "-1";
if (isset($_POST['search'])) {
  $colname1_search = (get_magic_quotes_gpc()) ? $_POST['search'] : addslashes($_POST['search']);
}
$colname2_search = "-1";
if (isset($_POST['search'])) {
  $colname2_search = (get_magic_quotes_gpc()) ? $_POST['search'] : addslashes($_POST['search']);
}
$colname3_search = "-1";
if (isset($_POST['search'])) {
  $colname3_search = (get_magic_quotes_gpc()) ? $_POST['search'] : addslashes($_POST['search']);
}
mysql_select_db($database_Chandos, $Chandos);
$query_search = sprintf("SELECT * FROM chandos_books WHERE author_name LIKE '%%%s%%' OR book_title LIKE '%%%s%%' OR isbn_hardback = '%s'  OR isbn_paperback = '%s'" , GetSQLValueString($colname_search, "text"),GetSQLValueString($colname1_search, "text"),GetSQLValueString($colname2_search, "int"),GetSQLValueString($colname3_search, "int"));
$search = mysql_query($query_search, $Chandos) or die(mysql_error());
$row_search = mysql_fetch_assoc($search);
$totalRows_search = mysql_num_rows($search);

an you spot what is wrong, sorry to be a pain.