By MattBridger777 on

Hi guys,

Having just about gotten the hang of dynamic queries, I have a query that appears to require the use of ->where rather than ->condition, since I'm pattern-matching on numerous fields. 

        $snippet = "(ft.field_town_value LIKE '%:location%' OR ".
                    "fpc.field_postcode_value LIKE '%:location%' OR ".
                    "fa.field_area_value LIKE '%:location%' OR ".
                    "fc.field_county_value LIKE '%:location%' OR ".
                    "fd.field_district_value LIKE '%:location%' OR ".
                    "fs.field_street_value LIKE '%:location%') ";
        $args = array(':location'=>$_POST['location']);
        $query->where($snippet, $args);

Whenever the query is run however with this ->where condition included, it produces the following error :-

PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {node} n INNER JOIN {field_data_field_picture} fp ON fp.entity_id = n.nid AND fp.delta = 0 INNER JOIN {field_data_field_town} ft ON ft.entity_id = n.nid INNER JOIN {field_data_field_postcode} fpc ON fpc.entity_id = n.nid INNER JOIN {field_data_field_price_text} fpt ON fpt.entity_id = n.nid INNER JOIN {field_data_field_advert_heading} ah ON ah.entity_id = n.nid INNER JOIN {field_data_field_area} fa ON fa.entity_id = n.nid INNER JOIN {field_data_field_county} fc ON fc.entity_id = n.nid INNER JOIN {field_data_field_district} fd ON fd.entity_id = n.nid INNER JOIN {field_data_field_street} fs ON fs.entity_id = n.nid INNER JOIN {field_data_field_type} ftp ON ftp.entity_id = n.nid INNER JOIN {field_data_field_numeric_price} np ON np.entity_id = n.nid INNER JOIN {field_data_field_beds} fb ON fb.entity_id = n.nid WHERE (n.type = :db_condition_placeholder_0) AND (n.status = :db_condition_placeholder_1) AND ((ft.field_town_value LIKE '%:location%' OR fpc.field_postcode_value LIKE '%:location%' OR fa.field_area_value LIKE '%:location%' OR fc.field_county_value LIKE '%:location%' OR fd.field_district_value LIKE '%:location%' OR fs.field_street_value LIKE '%:location%') ) ) subquery; Array ( [:db_condition_placeholder_0] => property [:db_condition_placeholder_1] => 1 [:location] => BA2 )

Can anyone help me identify what I've done wrong here please, because I just can't see what the problem appears to be?

My thanks for any and all help given,
~Matt

⋅ Categories: Drupal 7.x

Comments

paulhudson’s picture

paulhudson commented

Your query has 6 tokens (yes they are the same) but you only declared 1 variable in your $args array :-)

For example, this would cause the same error:

$snippet = 'nid = :nid AND vid = :nid';
$args = array(':nid' => 1);
$query->where($snippet, $args);

Whereas this is what the where() function is expecting:

$snippet = 'nid = :nid AND vid = :vid';
$args = array(':nid' => 1, ':vid' => 1);
$query->where($snippet, $args);

I understand why you have done it the way you have, but you'll need to work around that limitation of where()

MattBridger777’s picture

MattBridger777 commented

On "work around" I assume you meant put one element into the args array for every use of :location in the query?

If so, then I did rework the where query to look like this :

        $snippet = "(ft.field_town_value LIKE '%:location_0%' OR ".
                    "fpc.field_postcode_value LIKE '%:location_1%' OR ".
                    "fa.field_area_value LIKE '%:location_2%' OR ".
                    "fc.field_county_value LIKE '%:location_3%' OR ".
                    "fd.field_district_value LIKE '%:location_4%' OR ".
                    "fs.field_street_value LIKE '%:location_5%') ";
        $args = array(':location_0'=>$_POST['location'],
                        ':location_1'=>$_POST['location'],
                        ':location_2'=>$_POST['location'],
                        ':location_3'=>$_POST['location'],
                        ':location_4'=>$_POST['location'],
                        ':location_5'=>$_POST['location']);

And yet, even when I put one item in args per use of :location, it *still* complains that the number of variables is not equal to the number of tokens.
Was this what you meant or did I miss the point entirely?

My thanks for all help given,
~Matt

MattBridger777’s picture

MattBridger777 commented

Turns out the answer was to use db_or instead. 

$query->condition(db_or()
                ->condition('ft.field_town_value','%'.$_POST['location'].'%','LIKE')
                ->condition('fpc.field_postcode_value','%'.$_POST['location'].'%','LIKE')
                ->condition('fa.field_area_value','%'.$_POST['location'].'%','LIKE')
                ->condition('fc.field_county_value','%'.$_POST['location'].'%','LIKE')
                ->condition('fd.field_district_value','%'.$_POST['location'].'%','LIKE')
                ->condition('fs.field_street_value','%'.$_POST['location'].'%','LIKE'));

Works much better.

paulhudson’s picture

paulhudson commented

Ah that sounds good!

You had actually interpreted me correctly in your first workaround. Which means either something else was going on or I've exposed my ineptitude as apparently it didn't work! ;-)