PHP SQL - SQL Query Problem

Author

SQL Query Problem

Rob

2007-09-09, 7:02 pm

Hi,

I am having a problem with a search. Below is the code:

$colname_rs_product = "-1";
if (isset($_GET['search'])) {
$colname_rs_product = (get_magic_quotes_gpc()) ? $_GET['search'] :
addslashes($_GET['search']);
}
$colname2_rs_product = "-1";
if (isset($_GET['search'])) {
$colname2_rs_product = (get_magic_quotes_gpc()) ? $_GET['search'] :
addslashes($_GET['search']);
}
mysql_select_db($database_conn_nav, $conn_nav);
$query_rs_product = sprintf("SELECT * FROM tblProducts WHERE strModel
LIKE %s% or strDescription LIKE %s%", GetSQLValueString
($colname_rs_product, "text" ),GetSQLValueString($colname2_rs_product
,
"text"));
$rs_product = mysql_query($query_rs_product, $conn_nav) or die
(mysql_error());
$row_rs_product = mysql_fetch_assoc($rs_product);
$totalRows_rs_product = mysql_num_rows($rs_product);

So this is what is happening. I have a form on a page with a text box
named search. I am using that text box for the parameter that I want to
search on. The parameter is grabbed by the function GetSQLValueString,
(see code below)

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;
}
}

So what happens is this, I type in say 4500 and it will find in the
database the 4500 as long as it is the only item in the field. If I have
a field like 5400 Table with 4500 Chairs, this item won't be displayed in
the results.

I think I have narrowed it down to needing to put the "%" in the sprintif
statement, but I don't know how or where. Any help would be greatly
appreciated.

Thanks for your time.

Shaffer

2007-09-13, 8:01 am

On Sep 9, 7:58 pm, Rob <r...@to.com> wrote:
> Hi,
>
> I am having a problem with a search. Below is the code:
>
> $colname_rs_product = "-1";
> if (isset($_GET['search'])) {
> $colname_rs_product = (get_magic_quotes_gpc()) ? $_GET['search'] :
> addslashes($_GET['search']);}
>
> $colname2_rs_product = "-1";
> if (isset($_GET['search'])) {
> $colname2_rs_product = (get_magic_quotes_gpc()) ? $_GET['search'] :
> addslashes($_GET['search']);}
>
> mysql_select_db($database_conn_nav, $conn_nav);
> $query_rs_product = sprintf("SELECT * FROM tblProducts WHERE strModel
> LIKE %s% or strDescription LIKE %s%", GetSQLValueString
> ($colname_rs_product, "text" ),GetSQLValueString($colname2_rs_product
,
> "text"));
> $rs_product = mysql_query($query_rs_product, $conn_nav) or die
> (mysql_error());
> $row_rs_product = mysql_fetch_assoc($rs_product);
> $totalRows_rs_product = mysql_num_rows($rs_product);
>
> So this is what is happening. I have a form on a page with a text box
> named search. I am using that text box for the parameter that I want to
> search on. The parameter is grabbed by the function GetSQLValueString,
> (see code below)
>
> 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;
>
> }
> }
>
> So what happens is this, I type in say 4500 and it will find in the
> database the 4500 as long as it is the only item in the field. If I have
> a field like 5400 Table with 4500 Chairs, this item won't be displayed in
> the results.
>
> I think I have narrowed it down to needing to put the "%" in the sprintif
> statement, but I don't know how or where. Any help would be greatly
> appreciated.
>
> Thanks for your time.

Hello,

a. Why are you using %s% ??
b. Why are you using sprintf ??
c. Is there an error thrown-back from MySQL ?!

Why make complications, and why can't you post the error?

Shaffer.

Sponsored Links