Accounts table has foreign key user_id which references Users table id.
I've been trying to finish my add function of AccountsController. Currently, it is able to add a record into my Accounts table and Users table (associated) if it is a non-existent user. The problem comes when I'm trying to add an account onto an existing user (error i get "Record not found in table "accounts" with primary key [NULL]"; more info on this following). The reason why I have a users table is because it will contain public users (subscribe by providing email; no account) and registered users (registered users will have an account). I think that the reason this problem occurs is because the email is not unique in the UsersTable (rule).
AccountsController
public function add()
{
$account = $this->Accounts->newEntity();
if ($this->request->is('post')) {
$account = $this->Accounts->patchEntity($account, $this->request->data);
if ($this->Accounts->save($account)) {
$this->Flash->success('The account has been saved.');
return $this->redirect(['action' => 'index']);
}else {
$this->Flash->error('The account could not be saved. Please, try again.');
}
}
$users = $this->Accounts->Users->find('list', ['limit' => 200]);
$this->set(compact('account', 'users'));
$this->set('_serialize', ['account']);
}
add.ctp (accounts)
<?= $this->Form->create($account); ?>
<fieldset>
<legend><?= __('Add Account') ?></legend>
<?php
echo $this->Form->input('user.name');
echo $this->Form->input('user.email');
echo $this->Form->input('password');
echo $this->Form->input('phonenumber');
echo $this->Form->input('address');
echo $this->Form->input('user.postcode');
?>
</fieldset>
<?= $this->Form->button(__('Submit')) ?>
<?= $this->Form->end() ?>
</div>
Note: Information about what fields are in which tables are in the form so I won't go into detail about them. But accounts table will also have an id and user_id; user table will have id associated with user_id and a usertype (admin, client or public).
I've been trying to tackle this by trying to retrieve the row from the Users table using user.email but I can't access it. If I could access it, I should be able to retrieve the associated row from the Users table, get the id from the Users table and then somehow set the user_id for my Accounts record as that id. I have tried requesting the data from the form but it only works on inputs related to account model (at least I think that's how it works).
Much help would be appreciated. Please tell me if my thinking is wrong. I would greatly appreciate (not requirement) if answers to my problem were explained in layman's terms.
Edit: The users table will contain an admin, users that have a registered account to purchase services and users that just want to subscribe only. The latter will only have to provide lesser information than the registered users and hence why there is both a users table and an accounts table. The only way to log in is by using the users table (contains email) and the accounts table (contains password).
Edit 2: I basically want to do this.
AccountsController report address in case of error (primary key constraint)
public function add()
{
$account = $this->Accounts->newEntity();
if ($this->request->is('post')) {
$account = $this->Accounts->patchEntity($account, $this->request->data);
if ($this->Accounts->save($account)) {
$this->Flash->success('The account has been saved.');
return $this->redirect(['action' => 'index']);
}else {
$this->Flash->error($this->request->data['address']);
}
}
$users = $this->Accounts->Users->find('list', ['limit' => 200]);
$this->set(compact('account', 'users'));
$this->set('_serialize', ['account']);
}
The above works and reports the address that was typed in.
AccountsController report email in case of error (primary key constraint)
public function add()
{
$account = $this->Accounts->newEntity();
if ($this->request->is('post')) {
$account = $this->Accounts->patchEntity($account, $this->request->data);
if ($this->Accounts->save($account)) {
$this->Flash->success('The account has been saved.');
return $this->redirect(['action' => 'index']);
}else {
$this->Flash->error($this->request->data['user.email']);
}
}
$users = $this->Accounts->Users->find('list', ['limit' => 200]);
$this->set(compact('account', 'users'));
$this->set('_serialize', ['account']);
}
This doesn't work and gives me the error: Notice (8): Undefined index: user.email [APP/Controller\AccountsController.php, line 68]
I want to get user.email so then I can search for that row in my Users table and retrieve it's primary key (to plug into the user_id of the Account I am trying to make).
sql of the tables
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255),
postcode VARCHAR(255),
usertype VARCHAR(20) DEFAULT 'Public',
created DATETIME,
updated DATETIME
);
CREATE TABLE accounts (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
password VARCHAR(255),
phonenumber VARCHAR(10),
address VARCHAR(255),
created DATETIME,
updated DATETIME,
FOREIGN KEY user_key (user_id) REFERENCES users(id)
);
Edit 3: I found out the way to retrieve associated data from the form (or more accurately, the patched entity $account) is by $account->user->email. I'm getting somewhere but it's a pretty dirty way to register the account for an existing user (public).
AccountsController updated
public function add()
{
$account = $this->Accounts->newEntity();
if ($this->request->is(['post', 'patch', 'put'])) {
$account = $this->Accounts->patchEntity($account, $this->request->data);
if ($this->Accounts->save($account)) {
$users = $this->Accounts->Users;
$query = $users->query();
$query->update()
->set([
'usertype'=>'Client'
])
->where(['email' => $account->user->email])
->execute();
$this->Flash->success('The account has been saved.');
return $this->redirect(['action' => 'index']);
}else {
$users = $this->Accounts->Users;
$query = $users->query();
$usertype = $query->select('usertype')
->where(['email' => $account->user->email]);
if(true){
$query->update()
->set(['name'=>$account->user->name,
'postcode'=>$account->user->postcode,
'usertype'=>'Client'
])
->where(['email' => $account->user->email])
->execute();
//everything up to this point works
$userid = $query->select('id')
->where(['email' => $account->user->email]);
$accounts = $this->Accounts;
$query = $accounts->query();
$query->insert(['user_id', 'password', 'phonenumber', 'address'])
->values([
'user_id' => $userid,
'password' => $this->request->data['password'],
'phonenumber' => $this->request->data['phonenumber'],
'address' => $this->request->data['address']
])
->execute();
$this->Flash->success('The account has been saved.');
return $this->redirect(['action' => 'index']);
}
else{
$this->Flash->error('The account could not be saved.');
}
}
}
$users = $this->Accounts->Users->find('list', ['limit' => 200]);
$this->set(compact('account', 'users'));
$this->set('_serialize', ['account']);
}
The current problem now is inputting user_id into my account row through query builders. Currently, it successfully changes the usertype for my User but afterwards I get this when I try to insert a row into my Accounts table. Error: SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s).
Additionally the sql error is this -> INSERT INTO accounts (user_id, password, phonenumber, address) VALUES ((SELECT Users.usertype AS Users__usertype, Users.id AS Users__id FROM users Users WHERE (email = :c0 AND email = :c1 AND email = :c2)), :c3, :c4, :c5)
I have no idea why it's trying to put two columns into one field. Much help appreciated. Upon writing this I realized there are 3 queries executed up to that point where there is an email condition. Seems like queries are joined.
