Asked  7 Months ago    Answers:  5   Viewed   36 times

UPDATE

I was making a petty mistake when listing the VALUES. I should have put ":username" and not ":alias". I suppose the answer credit to this question is free reign for anyone who wants it? Or do I delete the question?

ORIGINAL

I've been using Yii's active record pattern for a while. Now, my project needs to access a different database for one small transaction. I thought the Yii's DAO would be good for this. However, I'm getting a cryptic error.

CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Here is my code:

public function actionConfirmation
{
    $model_person = new TempPerson();

    $model = $model_person->find('alias=:alias',array(':alias'=>$_GET['alias']));
    $connection=Yii::app()->db2;
            $sql = "INSERT INTO users (username, password, ssn, surname
                    , firstname, email, city, country) 
                    VALUES(:alias, :password, :ssn, :surname
                    , :firstname, :email, :city, :country)";
            $command=$connection->createCommand($sql);
            $command->bindValue(":username", $model->alias);
            $command->bindValue(":password", substr($model->ssn, -4,4));
            $command->bindValue(":ssn", $model->ssn);
            $command->bindValue(":surname", $model->lastName);
            $command->bindValue(":firstname", $model->firstName);
            $command->bindValue(":email", $model->email);
            $command->bindValue(":city", $model->placeOfBirth);
            $command->bindValue(":country", $model->placeOfBirth);
            $command->execute();
            $this->render('confirmation',array('model'=>$model));
}

This constructs the following query (as seen on the application log):

INSERT INTO users (username, password, ssn, surname, firstname, email
                   , city, country) 
VALUES(:alias, :password, :ssn, :surname, :firstname, :email, :city, :country);

FYI $model->placeOfBirth is supposed to be in both city and county values. That's not a typo (just a silly thing I have to do).

 Answers

68

Just to provide an answer - because this error is pretty common - here are a few causes:

1) The :parameter name does not match the bind by mistake (typo?). This is what happened here. He has :alias in the SQL statement, but bound :username. So when the param binding was attempted, Yii/PDO could not find :username in the sql statement, meaning it was "one parameter short" and threw an error.

2) Completely forgetting to add the bindValue() for a parameter. This is easier to do in Yii other constructs like $critera, where you have an array or params ($criteria->params = array(':bind1'=>'test', ':bind2'=>'test)).

3) Weird conflicts with CDataProvider Pagination and/or Sorting when using together and joins. There is no specific, easy way to characterize this, but when using complex queries in CDataProviders I have had weird issues with parameters getting dropped and this error occurring.

One very helpful way to troubleshoot these issues in Yii is to enable parameter logging in your config file. Add this to your db array in your config file:

'enableParamLogging'=>true,

And make sure the CWebLogRoute route is set up in your log section. This will print out the query that gave and error, and all of the parameters it was attempting to bind. Super helpful!

Wednesday, March 31, 2021
 
Xavio
answered 7 Months ago
69

If you use positional parameters, the array of parameters you pass to execute() must be an ordinal array. Likewise, if you use named parameters, the array must be an associative array.

Here's a test to confirm the behavior:

$stmt = $db->prepare("SELECT ?, ? ,?");

$params = array( 'a', 'b', 'c' );
// OK
if ($stmt->execute($params)) {
  print_r($stmt->fetchAll());
}

$params = array( 'A'=>'abc', 'B'=>'def', 'C'=>'ghi' );
// ERROR!
if ($stmt->execute($params)) {
  print_r($stmt->fetchAll());
}

$stmt = $db->prepare("SELECT :A, :B, :C");

$params = array( 'a', 'b', 'c' );
// ERROR!
if ($stmt->execute($params)) {
  print_r($stmt->fetchAll());
}

$params = array( 'A'=>'abc', 'B'=>'def', 'C'=>'ghi' );
// OK
if ($stmt->execute($params)) {
  print_r($stmt->fetchAll());
}

Note that in current versions of PHP, the associative array keys don't have to be prefixed with : as @prodigitalson comments. The : prefix used to be required in array keys in older versions of PHP.

It's also worth mentioning that I've encountered bugs and unpredictable behavior when I tried to mix positional parameters and named parameters in a single query. You can use either style in different queries in your app, but chose one style or another for a given query.

Wednesday, March 31, 2021
 
Packy
answered 7 Months ago
13

The problem - and you will kick yourself - is with :color.

The array key for the value you are passing for that marker when calling execute() is named :color:. Remove the trailing : (I'm guessing this was just a typo anyway).

$stmt3->execute(array(
    ':room' => $Clean['room'],
    ':name' => $Clean['name'],
    ':message' => $Clean['message'],
    ':time' => $time,
    ':color' => $Clean['color'],
    ));
Wednesday, March 31, 2021
 
coolguy
answered 7 Months ago
76

Try this:

 Yii::$app->db->createCommand("UPDATE room_types SET total_booked=total_booked+1 WHERE room_type = '$model->room_type' ")->execute();

OR

public function actionCreate()
    {
        $model = new CreateBookings();
        if ($model->load(Yii::$app->request->post())) {

    $RoomType = new room_types(); // room type replace with model name
    $RoomType->updateCounters(['total_booked' => 1]);

      $model->save();
            return $this->redirect(['view', 'id' => $model->id]);
        } else {
            return $this->render('create', [
                'model' => $model,
            ]);
        }
    }

Official link

Wednesday, March 31, 2021
 
jerrygarciuh
answered 7 Months ago
40

Yii2 advanced app comes by default with a working example of the login part from the DB (I see the basic ones uses a static username and password). You do not have to install anything extra, just look at the code. Install the advanced app and take a look at the frontend.

In short SiteController uses LoginModel for validation then uses the login() of the LoginModel to login the User model to the User component.

If you do not want to use the User model, just create your own model and use that one. You do not want to use the default User component, just create your own. It is quite easy to do.

Edit: mate, remove the public declarations of variables bellow.

class User extends ActiveRecord implements yiiwebIdentityInterface
{
public $id;
public $username;
public $password;
public $authKey;
public $accessToken;

You are telling Yii to ignore what is in the database.

Saturday, May 29, 2021
 
tedders
answered 5 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share