What is the correct syntax in LIKE statements when the value contains spaces

I am trying to select fields with this function

public function discussion (PDO $conn, $name) {

 		$relatedFallback = function ($limit) use ($name, $conn) {

 			$like = "%$name%";
 			
 			$augment = $conn->prepare('SELECT * FROM contents WHERE `type`= ? AND `name` LIKE ? LIMIT ?');

 			$augment->execute(['discussion', $like, $limit]);

 			return $augment->fetchAll(PDO::FETCH_ASSOC);
 		};
               $relatedFallback(2);    // do other stuff with this value
          
 		}

$this->($conn, 'Another topic but with a longer name');

This throws the error

Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘2’’

meaning nothing comes from the like variable hence the LIMIT keyword is exposed to the LIKE keyword. However, I have tried the sql query in phpmyadmin and it does return the intended row. The difference between both queries was, the $like used in phpmyadmin was coated with quotation marks–precisely

SELECT * FROM contents WHERE type= discussion AND name LIKE ‘%Another topic but with a longer name%’ LIMIT 2

How may I coat the variable with quotation marks before sending it in the execute method? I’ve tried using backticks but it doesn’t work.

Update: If I remove the LIMIT keyword and the last element in the execute array and it returns limitless rows. Why could the LIMIT clause be causing troubles?

So just out of curiosity, what does:

SELECT * FROM contents WHERE type = ‘Whatever’ AND name LIKE ‘%%’ LIMIT 2;

Return from phpmyadmin? I’m not sure how sql treats %% in a like clause and I’m too lazy to test myself.

I’m also curious on what you would expect LIKE ‘%%’ to do?

Of course when using prepared statements you never need to quote anything.

Because there are quotes coming into play, according to various things I’ve read. I used named parameters and bindparam for each parameter, which does give you an opportunity to force it to be whatever variable type you want.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.