6 Replies - 8029 Views - Last Post: 19 February 2013 - 06:18 PM Rate Topic: -----

#1 jeansymolanza   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 77
  • Joined: 20-February 08

Number of bound variables does not match number of tokens

Posted 19 February 2013 - 05:42 PM

I keep getting the following error:

Quote

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
when trying to build a dynamic SQL query. Any idea where the damage is being done? I've searched for hours and still no luck :stupid:

$park = $_POST["park"];
$lecturestyle_id = $_POST["lecturestyle"];
$group_size = $_POST["groupsize"];
$roomstructure_id = $_POST["roomstructure"];
$array = explode(",", $_POST["facilities"]);

// change Mon here

echo '<td class="gridSide">
Mon
';

// build facilities search

for($i = 0; $i < count($array); $i++){	
	if ($array[$i]!=0) {
	$fac .= ' AND facilities_id='.$array[$i].'';
	}
	else
	$fac .= '';
}

echo '</td>';


for ($i = 1; $i <= 9; $i++) 
{

		// change mon here
		
		echo '<td class="box" id="mon'.$i.'">';
		
		// dynamically build sql query
		
		$sql = 
		"
		SELECT DISTINCT COUNT(*) FROM ts_room rm
		LEFT JOIN ts_roomfacilities rf
		ON rm.id = rf.room_id
		LEFT join ts_facilities f
		ON f.id = rf.facilities_id
		LEFT JOIN ts_building b
		ON rm.building_id=b.id
		WHERE capacity>=:group_size";
		
		$sql .= $fac;
		
		if($park!="Any") {
		$sql .= " AND b.park_id=:park";
		}
		
		if($lecturestyle_id!="Any") {
		$sql .= " AND lecturestyle_id=:lecturestyle_id";
		}
		
		if($roomstructure_id!="Any") {
		$sql .= " AND roomstructure_id=:roomstructure_id";
		}
		
		$sql .= " AND rm.id NOT IN
		(SELECT COUNT(*)
		 FROM ts_request rq
		 LEFT JOIN ts_allocation a ON a.request_id = rq.id
		 WHERE 
		 day_id=1 AND period_id=:period
		 OR a.status IS NOT NULL
		 AND a.status IN ('Pending','Declined','Failed'))";
				
		 $stm = $pdo->prepare( $sql );
		 $stm->execute( array( ':roomstructure_id' => $roomstructure_id, ':lecturestyle_id' => $lecturestyle_id, 
		 ':group_size' => $group_size, ':park' => $park, ':period' => $i));
		 $rows = $stm->fetchColumn();
	
		 echo $rows.'<br>free</td>';			
		 echo '</td>';

}


Is This A Good Question/Topic? 0
  • +

Replies To: Number of bound variables does not match number of tokens

#2 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4241
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Number of bound variables does not match number of tokens

Posted 19 February 2013 - 05:57 PM

You aren't taking into account whether or not the park, lecturestyle_id or roomstructure_id placeholders are actually set when you execute the query. If you don't include those placeholders in the query, trying to bind values to them will fail with that error.

I suggest you build the array you pass to the execute function dynamically as well, adding the values to them in the same IF clauses that insert the placeholders.
Was This Post Helpful? 0
  • +
  • -

#3 jeansymolanza   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 77
  • Joined: 20-February 08

Re: Number of bound variables does not match number of tokens

Posted 19 February 2013 - 06:02 PM

Thanks for the reply.

This is what I've tried - http://pastebin.com/gjdz3Rpq

Still no luck :-(
Was This Post Helpful? 0
  • +
  • -

#4 CTphpnwb   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3847
  • View blog
  • Posts: 14,027
  • Joined: 08-August 08

Re: Number of bound variables does not match number of tokens

Posted 19 February 2013 - 06:05 PM

  • $x = $_POST['x'] is always a bad thing. It's a waste of processor cycles and it can lead to security issues.
  • Lines 15 - 21 will result in something like:

    ... AND facilities_id='a value'  AND facilities_id='another value''';
    
    This will return no result.
  • I'd build the execute array while building the query:
    		if($lecturestyle_id!="Any") {
    			$sql .= " AND lecturestyle_id=:lecturestyle_id";
    			$execute_array[':lecturestyle_id'] = $_POST["lecturestyle"];
    		}
    
    

Was This Post Helpful? 0
  • +
  • -

#5 jeansymolanza   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 77
  • Joined: 20-February 08

Re: Number of bound variables does not match number of tokens

Posted 19 February 2013 - 06:07 PM

Thanks its somewhat makes sense now - how would I build lines 15-21?

This post has been edited by Dormilich: 19 February 2013 - 11:31 PM
Reason for edit:: removed unnecessary quote

Was This Post Helpful? 0
  • +
  • -

#6 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4241
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Number of bound variables does not match number of tokens

Posted 19 February 2013 - 06:14 PM

View PostCTphpnwb, on 20 February 2013 - 01:05 AM, said:

  • Lines 15 - 21 will result in something like:

    ... AND facilities_id='a value'  AND facilities_id='another value''';
    
    This will return no result.

Not sure you're reading that right. He's only using single-quotes, so there would be no quotes in his output. It would be more like:
... AND facilities_id=a value AND facilities_id=another value;


Which is not necessarily invalid syntax wise, given that the values are all numbers.

But yea, given that the values are all numbers, and that they aren't all the same numbers, then this won't return anything. Assuming that this part is meant to be searching for one of the numbers, as opposed to all of them, then an IN clause seems in order.
$fac = "AND facilities_id IN(" . implode(",", $array) . ")"


Though, seeing as how the $array was originally exploded from a comma-separated list, that whole part may as well be removed. - But, on the other hand, it would be good to have the values exploded, so they can be validated before put into the query.
Was This Post Helpful? 0
  • +
  • -

#7 jeansymolanza   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 77
  • Joined: 20-February 08

Re: Number of bound variables does not match number of tokens

Posted 19 February 2013 - 06:18 PM

I've managed to get this running but its returning some funny results - http://pastebin.com/U4WK7gcn

Not sure how to go about creating the arrays...

This post has been edited by Dormilich: 19 February 2013 - 11:32 PM
Reason for edit:: removed unnecessary quote

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1