How to set up a basic URL search with an OR statement?

Hi, 

There currently isn't a way to do an OR statements using the URL search system. But it's fairly straight forward to create something manually, I've given an example of how I would do it below:

$whereString = '';

//If there are colours in the URL
if(@$_REQUEST['colors']){
  $colors = explode('|',htmlencode(mysql_escape($_REQUEST['colors']));
  //Add commas to each item in the array
  for($i=0;$i<count($colors);$i++) $colors[$i] = "'".$colors[$i]."'";
  //create the search string
  $colors = implode(" OR colors = ", $colors);
  //Create the where statement
  $whereString = $colors;
}

// load record from 'blog'
list($blog, $blogMetaData) = getRecords(array(
  'tableName' => 'blog',
  'where' => $whereString,
  'allowSearch' => false,
  'debugSql' => true
));

Using this system would mean that you would have to manually add to the where statement if you had any other fields you wanted to search as well.

I've added the ability to have OR statements in URL searches to our feature request list as.

Let me know if you have any questions, or need any help integrating the system. 

Thanks!

Greg

Thanks, Greg. That explains why, after a fairly exhaustive search through the forums and the online documentation, I was not getting anywhere. How about this inability be mentioned in the Online Documentation to (for neophyte programmers like me) help avoid a fruitless search thinking "surely, some one else must have considered this issue".

In the interim, I solved the issue temporarily by creating a duplicate listings page and adding in a combined MySQL where statement at the top. But that, or a solution like yours, requires a hand crafted case-by-case basis, which is not conducive for the site owner (and wouldn't be, I imagine, for most site owners). This is definitely something that should be addressed on the Feature Request list, for sure. Thanks.

You're right, equinox. I'm going to add this to our feature request list. I think the multiple values approach makes the most sense, because you can drive it wish a <select multiple>.

For now, for OR queries, mysql_escapeCSV() in combination with MySQL's IN is great.

If you're sending multiple values (e.g. ?color=blue&color=red), you can do this:

$whereString = "TRUE";
if (is_array(@$_REQUEST['colors'])) {
  $values      = $_REQUEST['colors'];
  $whereString = "colors IN (" . mysql_escapeCSV($values) . ")";
}

Otherwise, if you're sending a comma-separated list (e.g. ?color=blue,red), you can do this:

$whereString = "TRUE";
if (@$_REQUEST['colors']) {
  $values = explode(',', $_REQUEST['colors']);
  $whereString = "colors IN (" . mysql_escapeCSV($values) . ")";
}

Then add it to your getRecords() call:

// load record from 'blog'
list($blog, $blogMetaData) = getRecords(array(
  'tableName'   => 'blog',
  'where'       => $whereString,
  'allowSearch' => false,
  'debugSql'    => true,
));

This all assumes that the field you're searching is not a multi-value list field. Let us know if that's what you need to work with.

It's a multi-value field list. "Equipment Type", or equipment_type, is the field name...and it's a multi-choice dropdown. ;-)

Hi equinox,

This should work for you, after you paste in your section name:

$whereString = "TRUE";
if (is_array(@$_REQUEST['equipment_type'])) {
  $values      = $_REQUEST['equipment_type'];
  $whereString = "equipment_type IN (" . mysql_escapeCSV($values) . ")";
}

// load record from 'blog'
list($records, $recordsMetaData) = getRecords(array(
  'tableName'   => 'MY_SECTION_NAME',
  'where'       => $whereString,
));


Hi Chris,

That last code you sent is not working for multi-values. It's only returning results for the last equipment_type requested in the url. Example:

if the URL is http://www.mysitename.com/inventoryList.php?equipment_type=40&equipment_type=106  then results only show for equipment type 106.

if the URL is http://www.mysitename.com/inventoryList.php?equipment_type=40&equipment_type=106&equipment_type=130 then results only show for equipment type 130

Just to verify, here's the code I put in near the top of the list page:

$whereString = "TRUE";
if (is_array(@$_REQUEST['equipment_type'])) {
  $values      = $_REQUEST['equipment_type'];
  $whereString = "equipment_type IN (" . mysql_escapeCSV($values) . ")";
}

// load record from 'inventory'
list($inventoryRecords, $inventoryMetaData) = getRecords(array(
  'tableName'   => 'inventory',
  'perPage'     => '20',
  'loadUploads' => true,
  'where'       => $whereString,
));

I am reading back across the posts here and I see where you mentioned multiple OR queries...so I want to clarify. I want to get search results combined and presented for any and all inventory records which the equipment type list dropdown has been selected as graders, compactors or rollers. (which are equipment type #'s 40, 106, and 130 in the databse). So, it's not just a "this OR that" statement, it's an "All that have this, OR that, OR that, etc." statement.

Hi,

Using the mysql IN() function produces the same result as using multiple or statements. So "num IN(1,5,7,8)" is the same as saying "num = 1 OR num = 5 OR num = 7 OR num = 8". So in this case records 1,5,7 and 8 would be returned. 

There needs to be a slight change to the URL to get the example Chris gave working, as the search colors need to be sent over as an array. If you were to produce a form to search the different colors, it would need to look like this:

<form method="get" action="test.php" >
  <input type="checkbox" name="colors[]" value="blue" />
  <input type="checkbox" name="colors[]" value="red" />
  <input type="checkbox" name="colors[]" value="green" />
  <input type="submit" name="Submit" value="Submit" />
</form>

The equivalent search string would look like this:

test.php?colors%5B%5D=red&colors%5B%5D=green

with %5B being url encoding for '[' and %5D being url encoding for ']'.

This sends the color items over in an array

Thanks!

Greg