SQLSTATE[42000] Error

Need Help
1

I am using CakePHP to create an online education system. I am getting this error: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: ‘Users’ when I try to get a list of users from my database. I am using the code created by bake, with no modifications. I am not sure what is happening. The query that is created is: SELECT Users.users_id AS Users__users_id, Users.users_username AS Users__users_username, Users.users_password AS Users__users_password, Users.users_hint AS Users__users_hint, Users.users_answer AS Users__users_answer, Users.users_grades AS Users__users_grades FROM users Users INNER JOIN users Users ON Users.users_id = (Users.users_id) LIMIT 20 OFFSET 0.

Any help would be greatly appreciated.
Thank you, Troy L. Marker

2

It would help showing your table and fields. Ther error is from INNER JOIN users Users ON Users.users_id = (Users.users_id) it seems. You need to provide different aliases.

3

Thanks for the response. I’m sorry about the lack of information. I will put something together, but my next question is how can I change the query? As I mentioned, I am using the code that bake created for me.

4

Here is what my users table looks like:
Users Table:
users_id - int(11) - auto-increment primary key
users_username varchar(45)
users_password varchar(255)
users_hint varchar(100)
users_answer varchar(45)

5

And this is the table model.
public function initialize(array $config)
{
parent::initialize($config);

    $this->setTable('Users');
    $this->setDisplayField('users_id');
    $this->setPrimaryKey('users_id');

    $this->belongsTo('Users', [
        //'foreignKey' => 'users_id',
        //'joinType' => ''
    ]);
    //$this->belongsToMany('Paths', [
    //    'foreignKey' => 'user_id',
    //    'targetForeignKey' => 'path_id',
    //    'joinTable' => 'users_paths'
    //]);
}

I have tried it with the commented lines both ways, with the same effect.

6

The problem is that you defined users belongs to users. you should use another alias and add a classname

$this->belongsTo('AdminUsers', [
    'className' => 'Users',        
    'foreignKey' => 'users_id',
    'joinType' => ''
]);
7

rrd,

Thanks for the reply. I made the change, as you suggested and listed here:

    $this->belongsTo('AllUsers', [
        'className' => 'Users',
        'foreignKey' => 'users_id',
        'joinType' => ''
    ]);

However when I run it I get the following error: Users is not associated with users.

I’m note sure why I am getting this error, any suggestions?

Thanks again, Troy

8

You should remove $this->belongsTo('Users', [.... Than one cause the error.

If you already removed it than show me your find() call

9

After working with it a little more, I got it to work. My mistake was assuming that the code created by Bake was ready to go, I did not think I would have to tweak it, but after doing just that, it is working. I do appreciate your help, it got me going in the right direction.

Now on to bigger and better things.

Thanks again,
Troy L. Marker

10

And you were correct, the problem was with my find() call.

1 Like