Using Variable in Like Operator

304
May 08, 2017, at 07:19 AM

I'm trying to call a random row from a table where the column "activePages" features a certain id number.

For example, activePages might contain "123 456" on one row, "456 789" on another and I use the following code:

SELECT * FROM advertising WHERE activePages LIKE %456% ORDER BY rand() LIMIT 1

...To call one of those two rows at random.

I want to do this same thing, but with a variable in place of "456". Where do I need to put the % for this to work? Or am I missing another problem entirely?

This is what I have right now:

$getad = $pdo->prepare('SELECT * FROM advertising WHERE activePages LIKE :id ORDER BY rand() LIMIT 1');
$getad->execute(['id' => $id]);
Answer 1

Wrote a quick test to verify some other posts on SO. Adding the % to the PHP variable works for the like comparison.

$id = "%searchTerm%";
$sql  = "SELECT * FROM TABLE
         WHERE COLUMN LIKE :id";
$stmt->execute([':id'=> $id]);
Answer 2

You can use this sort of CONCAT() construct.

 WHERE COLUMN LIKE concat('%', :id, '%')

You can put anything you need into CONCAT(), so it's quite flexible.

Rent Charter Buses Company
READ ALSO
php multiple files for site

php multiple files for site

Currently I'm making my first website, it is very basicWhat I have are add and delete functions for my external JSON file

248
class wrapper not working php

class wrapper not working php

I have run into a small issue in my coding adventures, and am hoping someone can help me outIn my web application, I have 3rd party libraries (in the form of including the main file of the library), and I am attempting to dynamically load them when required

262
Validate data inside custom repository Symfony2 [on hold]

Validate data inside custom repository Symfony2 [on hold]

I'm making REST API, i have custom repository that extends EntityRepositoryphp

230
How can I get PHPMailer to send an email using Gmail's SMTP?

How can I get PHPMailer to send an email using Gmail's SMTP?

I've installed PHPMailer onto Windows Server 2012 R2 (using IIS) and I'm trying to use it to send an email, here is my code:

379