I'm not seeing why your code wouldn't work. You are adding one value for every ? so that should be ok. Could you paste the error and the data in the $_GET array that causes it to fail?
Sorry ... I wanted to get this out of my project and into a more isolated testing setup before I responded, and I never got around to it.
Anyway, it should be more clear in this updated example.
The following code:
$clause = '';
$glue = '';
$cond_values = array();
$query = array(
'style' => 'cat',
'color' => 'orange'
);
if (!empty($query['style'])) {
$clause .= $glue . ' style = ?';
$cond_values[] = $query['style'];
$glue = ' AND';
}
if (!empty($query['color'])) {
$clause .= $glue . ' color LIKE %?%';
$cond_values[] = $query['color'];
$glue = ' AND';
}
if (!empty($query['size'])) {
$clause .= $glue . ' size LIKE %?%';
$cond_values[] = $query['size'];
$glue = ' AND';
}
$animals = Animal::all(array('conditions' => array($clause, $cond_values)));
Outputs this:
Fatal error: Uncaught exception 'ActiveRecord\ExpressionsException' with message 'No bound parameter for index 1' in /var/www/shared/test/lib/php-activerecord/lib/Expressions.php:111 Stack trace: #0 /var/www/shared/test/lib/php-activerecord/lib/SQLBuilder.php(310): ActiveRecord\Expressions->to_s() #1 /var/www/shared/test/lib/php-activerecord/lib/SQLBuilder.php(97): ActiveRecord\SQLBuilder->apply_where_conditions(Array) #2 [internal function]: ActiveRecord\SQLBuilder->where(' style = ? AND ...', Array) #3 /var/www/shared/test/lib/php-activerecord/lib/Table.php(164): call_user_func_array(Array, Array) #4 /var/www/shared/test/lib/php-activerecord/lib/Table.php(195): ActiveRecord\Table->options_to_sql(Array) #5 /var/www/shared/test/lib/php-activerecord/lib/Model.php(1428): ActiveRecord\Table->find(Array) #6 [internal function]: ActiveRecord\Model::find('all', Array) #7 /var/www/shared/test/lib/php-activerecord/lib/Model.php(1256): call_user_func_array('static::find', Array) #8 /var/www/crcna.org/datatest/html/ar_test/index.ph in /var/www/shared/test/lib/php-activerecord/lib/Expressions.php on line 111
But it works fine if $query has 0 or 1 items in it.
Edit: forgot the line of code where I actually call the finder.
Shouldn't $glue = "," ?
Keith Thibodeaux wrote:
Shouldn't $glue = "," ?
No, it's building the WHERE clause separated by AND. The error doesn't appear to have anything to do with the actual SQL anyway.
(1-4/4)
Subject: complex conditions
How do you form a finder call when the number of conditions can vary?
For example, let's say I'm building a search function for articles. You can search by author, category, and keyword. If any one of those is not specified in the search, then obviously I won't want it in the WHERE clause.
I'm really struggling to think of a way to make this work with the phpAR finder. Normally I'd start with an empty WHERE clause and just add pieces to it as needed. But the problem comes in when using phpAR's "prepared queries" where you pass in the sql and the variables separately. At first I'd hoped this would work:
if ($_GET['category_id']) { $clause .= $glue . ' category_id = ?'; $cond_values[] = $_GET['category_id']; $glue = ' AND'; } if ($_GET['author']) { $clause .= $glue . ' author like %?%'; $cond_values[] = $_GET['author']; $glue = ' AND'; } if ($_GET['keyword']) { $clause .= $glue . ' keywords like %?%'; $cond_values[] = $_GET['keyword']; $glue = ' AND'; } $item = Item::all(array('conditions' => array($clause, $cond_values)));In other words, pass in one single array for all of the substituted values in the clause. I'd hoped that the function would realize that there weren't enough values to cover the conditions, and look inside the array I'd passed in. Obviously it was not designed to work this way, as it just spits out a fatal error.
I'm guessing this is really easy and I'm just missing it?
Thanks.