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
By ht1080z - December 17, 2013
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
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,
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,
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,
PHP Programmer - interactivetools.com