where array values in multi value field

Hello again,

I think, i started to make some progress on my problem:

$searchString = implode(',',@$_REQUEST['s_therapy']);
$where ="(process IN($searchString))";

@$_REQUEST['s_therapy'] is an array that i get from the search form,
'process' is multi value field in my table where i need to find the array values.

Now its working with no errors but i'am not sure if the OR statement is working as i described in the main post (i have too many records and values to check in the real data)

Please give some directions... :)
Karls

Hello,

Any comment/advise on the above problem?

Thank you,
Karls

Hi Karls,

Yes, you can use "IN" clause to replace many "OR" conditions but I think it will not work properly because a multi field values are stored as tab-separated values.

If I understand correctly, you need something like this:

"where process LIKE '%\t" . @$_REQUEST['s_therapy'][0] . "\t%' OR process LIKE '%\t" . @$_REQUEST['s_therapy'][1] . "\t%' OR process LIKE '%\t" . @$_REQUEST['s_therapy'][2] . "\t%'"

In that case, what I would do is use mysql REGEXP statement. Example:

$searchString = implode('|',@$_REQUEST['s_therapy']);

list($resultRecords, $resultMetaData) = getRecords(array(
    'tableName'      => 'hospitals',
    'where'          => "process REGEXP '{$searchString }'",
    'loadUploads'    => true,
    'allowSearch'    => false,
    'orderBy'        => "createdDate DESC",
    'perPage'        => '6',
    'debugSql'       => true,
));


Hope this works for you.

Cheers,

Hi again Karls,

I just realized that we should also "escape" the $searchString variable in the "where" statement by using mysql_escape() function to add more security. The red-colored parts of the code is where I added the mysql_escape function:

$searchString = implode('|',@$_REQUEST['s_therapy']);

list($resultRecords, $resultMetaData) = getRecords(array(
    'tableName'      => 'hospitals',
    'where'          => "process REGEXP '" . mysql_escape($searchString). "'",
    'loadUploads'    => true,
    'allowSearch'    => false,
    'orderBy'        => "createdDate DESC",
    'perPage'        => '6',
    'debugSql'       => true,
));

Cheers,

Hi Daryl,

Thank you for your reply and follow-up on my issue.
I running some test with the suggested code and will back to you with results shortly.

Karls

Hi Daryl,

I thought the getRecords, mysql_get or mysql_select functions validate and escape all strings before being added to a MySQL statement, so the added mysql_escape there isn't really needed?

Steve

Hi Steve,

We need to use mysql_escape() function to escape a string if we're adding it as a "string" in the mysql statement. The getRecords, mysql_select, mysql_get, and mysql_insert automatically escape the strings if they are in a form of an array.

The example below doesn't need to escape the string:

$productsRecords = mysql_select("products_listing", array('category' => @$_REQUEST['categoryNum']));

But if we're adding a string as "string", we need to escape it:

$productsRecords = mysql_select("products_listing", 'category = "'.mysql_escape(@$_REQUEST['categoryNum']).'"'));

Cheers,