How to call the SQL LIKE function in Yii2?

Question

Receiving NULL as value when I called this SQL statement (the record exists in the MySQL DB). How can use the LIKE function in Yii2? Thanks.

$command = $connection->createCommand("SELECT user_id
                         FROM User
                            WHERE name LIKE '%:_username%'");

$command->bindParam(':_username',$this->username);
$this->id_user = $command->queryScalar();
$command->execute();

Have you looked into using the Query Builder instead of creating the command yourself? It might be more flexible for this type of call. It also has the benefit of handling where cases in different ways. — M Sost
– M Sost, Commented May 10, 2016 at 23:56

ScaisEdge · Accepted Answer · 2016-05-12 06:31:18Z

1

In Yii2 you can use also a activeQuery Notation this way

  $query = new Query;
  $query->select('userid')
      ->from('user')
      ->where(['like', 'user', $this->username );

  $command = $query->createCommand();

  $this->id_user = $command->queryScalar();

edited May 12, 2016 at 6:31

answered May 11, 2016 at 7:17

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jan Beeck Over a year ago

It works modifying the following line: ->where(['like', 'user','%'.$this->username.'%', false]);

user229044 · Accepted Answer · 2016-05-17 13:23:16Z

1

Just wrap "%value%" in bindValue

 $command = $connection->createCommand("SELECT user_id
                             FROM User
                                WHERE name LIKE ':_username'");

    $command->bindParam(':_username',"%".$this->username."%");
    $this->id_user = $command->queryScalar();
    $command->execute();

Correct Way to bind value with %% is below

 $command->bindParam(':_username',"%".$this->username."%");

just like as PDO we use

edited May 17, 2016 at 13:23

user229044♦

241k41 gold badges347 silver badges350 bronze badges

answered May 11, 2016 at 5:14

Parth Chavda

1,8291 gold badge24 silver badges31 bronze badges

Comments

Severino Lorilla Jr. · Accepted Answer · 2016-05-10 23:47:01Z

0

I think the issue here is with the binding.

Try, using this instead.

$command->bindParam('_username',$this->username);

Also not so sure what's the acceptable syntax for the variables, so if it's still not working try removing the _ and use username on binding... and :username on the query.

answered May 10, 2016 at 23:47

Severino Lorilla Jr.

1,6474 gold badges20 silver badges34 bronze badges

Comments

Lawrence Cooke · Accepted Answer · 2016-05-11 02:05:50Z

0

Binding needs to be done on the entire data not just part of it.

$command = $connection->createCommand("SELECT user_id
                     FROM User
                        WHERE name LIKE :username");

$user = '%' . $this->username . '%';

$command->bindParam(':username',$user);
$this->id_user = $command->queryScalar();
$command->execute();

edited May 11, 2016 at 2:05

answered May 10, 2016 at 23:59

Lawrence Cooke

1,6175 gold badges27 silver badges55 bronze badges

2 Comments

Jan Beeck Over a year ago

I have the following error after that statement: PHP Fatal Error – yii\base\ErrorException Cannot pass parameter 2 by reference

Lawrence Cooke Over a year ago

You might need to turn it into a string first: eg. $user = '%' . $this->username . '%'; $command->bindParam(':username',$user);

Elson Costa · Accepted Answer · 2017-04-06 13:44:39Z

0

I'm using bindValue. You can use like this.. I have a query in my model, and calls the functions on my Controller.

Inside Model:

$query = "SELECT u.id as idUser
FROM vagas_x_candidato vxc
INNER JOIN usuario u ON(u.id = vxc.id_candidato)
WHERE u.nome LIKE :nameUser";

$connection = \Yii::$app->db;
$model = $connection->createCommand($query);
$model->bindValue(":nameUser", '%' . $filter->nameU . '%');
return $model->queryAll();

Inside Controller:

$vxc = new VagasXCandidatoSearch();
$vxc = $vxc->candidatosPesquisaAvancada(); //calls my method on model.

answered Apr 6, 2017 at 13:44

Elson Costa

4944 silver badges9 bronze badges

Comments

Skatox · Accepted Answer · 2022-03-16 21:05:20Z

0

I'm not sure, but try to get the first % before the single quote and the last % after the last single quote.

$command = $connection->createCommand(
       "SELECT user_id
        FROM User
        WHERE name LIKE %':_username'%"
);

edited Mar 16, 2022 at 21:05

Skatox

4,27412 gold badges47 silver badges50 bronze badges

answered May 10, 2016 at 23:48

Marc Giroux

1921 gold badge7 silver badges18 bronze badges

Collectives™ on Stack Overflow

How to call the SQL LIKE function in Yii2?

6 Answers 6

1 Comment

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related