where array values in multi value field

8 posts by 3 authors in: Forums > CMS Builder
Last Post: March 19, 2014   (RSS)

By ht1080z - December 17, 2013 - edited: December 17, 2013

Hi,

I' am trying to build a results page from a search form.
The table i need to search contain a multi value field where stored some numbers.
In this field i need to search a multi value record that i get from the search form as array.

Example search form:

<form method="post" action="results.php?">
    <select name="s_therapy[]" multiple="multiple">
        <?php foreach($therapyRecords as $therapy) { ?>
        <option value="<?php echo $therapy['num']; ?>"><?php echo $therapy['name_'.$_SESSION['language']]; ?></option>
        <?php } ?>
    </select>
    <input type="submit" value="submit" class="button">
</form>

result page (without error checking in this exapmle):

$searchString = implode(', ',@$_REQUEST['s_therapy']);
$where = "process LIKE '%\t$searchString\t%'";

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

The 'process' field is multi value field.

i need to find 'therapies' with OR operators in the 'process' field: where 'process' like 1 OR 5 OR 7...

How can i search multi value records in a multi value field and get results in this situation?

Please advise or give some directions...
Karls

Hello,

Any comment/advise on the above problem?

Thank you,
Karls

By Daryl - January 3, 2014

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,

Daryl Maximo
PHP Programmer - interactivetools.com

By Daryl - January 7, 2014 - edited: January 7, 2014

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,

Daryl Maximo
PHP Programmer - interactivetools.com

By ht1080z - January 8, 2014

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

By Steve99 - March 19, 2014

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

By Daryl - March 19, 2014

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,

Daryl Maximo
PHP Programmer - interactivetools.com