We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

How to insert data in multiple mysql tables

Hi @all,

i develop a register form with several tables, i took the "invo" project as my base.

  1. Table "account" includes accout_id, password, email and username -> here i can insert data with no problem.
  2. Table "profile" includes profile_id, account_id(foreign key), gender, birthday -> can't insert

I created 1 Controller RegisterController.php and 2 Model Account.php and Profile.php.

How can i insert data into the second table and set the account_Id as foreign_Key into its.

RegisterController:

class RegisterController extends ControllerBase{

        $form = new RegisterForm;

        if ($this->request->isPost()) {

            $username = $this->request->getPost('username', 'alphanum');
            $email = $this->request->getPost('email', 'email');
            $password = $this->request->getPost('password');
            $repeatPassword = $this->request->getPost('confirmPassword');
            $gender = $this -> request -> getPost('gender');

            if ($password != $repeatPassword) {
                $this->flash->error('Passwords are different');
                return false;
            }

            $user = new Account();
            $user->username = $username;
            $user->password = sha1($password);
            $user->email = $email;
            $user->date = new Phalcon\Db\RawValue('now()');
            $user->active = 'Y';
           if ($user->save() == false) {
                foreach ($user->getMessages() as $message) {
                    $this->flash->error((string) $message);
                }
            } else {
                //$this->tag->setDefault('email', '');
                //$this->tag->setDefault('password', '');
                $this->flash->success('Thanks for sign-up, please log-in to start generating invoices');
                return $this->forward('session/index');
            }
        }

        $profile = new Profile();
        $profile -> gender = $gender;
        $profile -> save();

        $this->view->form = $form;

    }

}

Account.php:

use Phalcon\Mvc\Model; use Phalcon\Mvc\Model\Validator\Email as EmailValidator; use Phalcon\Mvc\Model\Validator\Uniqueness as UniquenessValidator;

class Account extends Model { public function validation() { $this->validate(new EmailValidator(array( 'field' => 'email' ))); $this->validate(new UniquenessValidator(array( 'field' => 'email', 'message' => 'Sorry, The email was registered by another user' ))); $this->validate(new UniquenessValidator(array( 'field' => 'username', 'message' => 'Sorry, That username is already taken' ))); if ($this->validationHasFailed() == true) { return false; } }

}

Profile.php:

use Phalcon\Mvc\Model;

class Profile extends Model { public $gender;

public $birthday;

}

edited Aug '15

Hi there, check the docs! ;]

Additional info in this thread



59.9k

Hi Lajos,

thx for your very fast reply, i will try it today :-)

Rgds

Stefan



59.9k

Sorry i can't get it run.

I also tried "belongsTo" and "hasMany". The only "insert into" is the user account, but nothing in the "profile".

Profile.php:

   $this->belongsTo('fid', 'Account', 'id', array(
        'alias' => 'account',
        'reusable' => true
    ));

Account.php:

    $this->hasMany('id', 'Profile', 'fid', array(
        'alias' => 'profile',
        'foreignKey' => array(
            'message' => 'Profile cannot be deleted because it\'s used on Users'
        )
    ));

ProfileController.php:

        $profile         = new Profile();
        $profile->assign(array(
            'fid' => $this->request->getPost('id', 'int'),
            'name' => "rfrfr",
            'birthday' => "eee",
            'country' =>  'dxde',

        ));

        $profile->save();

AccountController.php:

                $user        = new User();
                $user->assign(array(
                'username' => $username,
                'email' => $email,
                'password' => $password,
                'date' => new Phalcon\Db\RawValue('now()'),
                'active' => 'Y'

            ));

            $user->save();

Thx for your help :-)

Rgds

Stefan



77.7k
Accepted
answer
edited Aug '15

As far as I understand your code, you should do something like this:

// Profile.php (Model)
$this->belongsTo('fid', 'Account', 'id', array(
    'alias' => 'account',
    'reusable' => true
));
// Account.php (Model)
$this->hasMany('id', 'Profile', 'fid', array(
     'alias' => 'profile',
     'foreignKey' => array(
         'message' => 'Account cannot be deleted because it\'s used on Profile'
     )
));
// RegisterController.php (Controller)
public function registerAction() {
   $form = new RegisterForm;
   if ($this->request->isPost()) {
      $username = $this->request->getPost('username', 'alphanum');
      $email = $this->request->getPost('email', 'email');
      $password = $this->request->getPost('password');
      $repeatPassword = $this->request->getPost('confirmPassword');
      $gender = $this -> request -> getPost('gender');
      if ($password != $repeatPassword) {
         $this->flash->error('Passwords are different');
         return false;
      }
      $user = new Account();
      $user->username = $username;
      //$user->password = sha1($password);
      $user->password = $this->security->hash($password); // see: https://docs.phalcon.io/en/latest/reference/security.html
      $user->email = $email;
      $user->date = new Phalcon\Db\RawValue('now()');
      $user->active = 'Y';
      $profile = new Profile();
      $profile ->gender = $gender;
      $user->profile = array($profile); // hasMany relationship, define as array
      if ($user->save() == false) {
         foreach ($user->getMessages() as $message) {
            $this->flash->error((string) $message);
         }
      } else {
           $this->flash->success('Thanks for sign-up, please log-in to start generating invoices');
           return $this->forward('session/index');
        }
    } 
    $this->view->form = $form;
}

Hope it helps!



59.9k

YES YES YES!!! Lajos you are my best friend .... for now :-))))))

Thank you soooo much, it was only the array (= $user->profile = array($profile); // hasMany relationship, define as array)

Kind Rgds

Stefan



13.9k

Hello! Thanks for adding this useful information.

That I'm trying to do is very similar to this, but I got no records on my second tables, in this example would be the "Profile" table, also, I'm not getting any errors, why this happens? How can I debugg this? Thank you!

As far as I understand your code, you should do something like this:

// Profile.php (Model)
$this->belongsTo('fid', 'Account', 'id', array(
   'alias' => 'account',
   'reusable' => true
));
// Account.php (Model)
$this->hasMany('id', 'Profile', 'fid', array(
    'alias' => 'profile',
    'foreignKey' => array(
        'message' => 'Account cannot be deleted because it\'s used on Profile'
    )
));
// RegisterController.php (Controller)
public function registerAction() {
  $form = new RegisterForm;
  if ($this->request->isPost()) {
     $username = $this->request->getPost('username', 'alphanum');
    $email = $this->request->getPost('email', 'email');
    $password = $this->request->getPost('password');
    $repeatPassword = $this->request->getPost('confirmPassword');
    $gender = $this -> request -> getPost('gender');
    if ($password != $repeatPassword) {
       $this->flash->error('Passwords are different');
        return false;
     }
    $user = new Account();
    $user->username = $username;
    //$user->password = sha1($password);
    $user->password = $this->security->hash($password); // see: https://docs.phalcon.io/en/latest/reference/security.html
    $user->email = $email;
    $user->date = new Phalcon\Db\RawValue('now()');
    $user->active = 'Y';
    $profile = new Profile();
    $profile ->gender = $gender;
    $user->profile = array($profile); // hasMany relationship, define as array
    if ($user->save() == false) {
       foreach ($user->getMessages() as $message) {
          $this->flash->error((string) $message);
       }
    } else {
         $this->flash->success('Thanks for sign-up, please log-in to start generating invoices');
         return $this->forward('session/index');
      }
  } 
  $this->view->form = $form;
}

Hope it helps!