PDO binding MYSQL LIKE

[nichemtktg]

This works:

try {	
			$sql = '
				SELECT 
					id
				FROM 
					nconnectz
				WHERE
					itemandsize LIKE \'%FLOUR%\'
				AND 
					price > 0	
				ORDER BY
					price ASC				
			';
			$stmt2 = $db2->prepare($sql);
		} catch (PDOException $e) {
			echo $e->getMessage() . '<br>';
		}	
		
		try {	
			$stmt2->execute();
		} catch (PDOException $e) {
			echo $e->getMessage() . '<br>';
		}	
		

This Doesn’t. I get this error: Parse error: syntax error, unexpected ‘:’, expecting ‘)’

try {	
			$sql = '
				SELECT 
					id
				FROM 
					nconnectz
				WHERE
					itemandsize LIKE :itemandsize
				AND 
					price > 0	
				ORDER BY
					price ASC				
			';
			$stmt2 = $db2->prepare($sql);
		} catch (PDOException $e) {
			echo $e->getMessage() . '<br>';
		}	
				
		$var = 'Flour';
		
try {	
			$stmt2->execute(array(				
				:itemandsize => '%' . $var . '%'
			));
		} catch (PDOException $e) {
			echo $e->getMessage() . '<br>';
		}	

How do I bind for LIKE using an array?

[nichemtktg]

OK. This worked, but can someone tell me how I can use an array instead of bindParam

This worked:

try {	
			$sql = '
				SELECT 
					*
				FROM 
					nconnectz
				WHERE
					itemandsize LIKE :itemandsize
				AND 
					price > 0	
				ORDER BY
					price ASC				
			';
			$stmt2 = $db2->prepare($sql);
		} catch (PDOException $e) {
			echo $e->getMessage() . '<br>';
		}	
		 		
		$var = "%Flour%";
		 
		try {	
			$stmt2->bindParam(':itemandsize',$var);
			$stmt2->execute();
		} catch (PDOException $e) {
			echo $e->getMessage() . '<br>';
		}	

[ahundiak]

Are you getting an error for your array attempt or just the wrong results? It looks right.

It’s a bit off topic but trying to catch every exception gets old really fast and can make your code hard to follow. A master level try/catch will usually do the trick just fine.

[SamA74]

@nichemtktg

$stmt2->execute(array( :itemandsize => ‘%’ . $var . ‘%’ ));

Did you try:-

$stmt2->execute([':itemandsize' => '%' . $var . '%']);

It may be simpler to not use named placeholders.

[nichemtktg]

Interesting.

When I strip-out all the white space it works:

$stmt2->execute(array(‘:itemandsize’ => ‘%’ . $var . ‘%’));

I’ve heard of white space being a problem, but haven’t knowingly experienced it.

Any thoughts? Urban legend?

[SamA74]

@nichemtktg

Any thoughts?

The main difference with what I posted (to my mind) was the inclusion of quotes around the array key.
Whitespace should not change anything.